Question:
I’m trying to fetch the data from SFTP server through velo code. The data I want to fetch is in two separate files pricelist-1.csv and pricelist-2.csv. Both files have more than 20000+ rows. Now I need to fetch the data and store them into the database. I don’t want to download the files. When I tried it I got only 400+ rows of data and then I got error. Can anyone please help me?
Product:
I’m trying to get this done in Wix Editor using backend fucntion.
What are you trying to achieve:
I need to run this function twice a day without getting errors and I need all the data to be stored into Wix CMS. Once I get all the data I can achieve my goal easily.
What have you already tried:
Here is the code I have tried:
const Client = require('ssh2-sftp-client');
const csv = require('csv-parser');
class SFTPClient {
constructor() {
this.client = new Client();
this.partialData = [];
this.startTime = null;
this.remoteFile = '';
this.chunkSize = 2500;
this.offset = 0;
this.timeoutDuration = 50000;
this.wholeContent = '';
}
async connect(options) {
console.log(`Connecting to ${options.host}:${options.port}`);
try {
await this.client.connect(options);
console.log('Connected successfully');
} catch (err) {
console.log('Failed to connect:', err);
}
}
async disconnect() {
await this.client.end();
console.log('Disconnected');
}
async readFile(remoteFile, offset = 0, chunkSize = 2500) {
try {
const options = {
readStreamOptions: {
start: offset,
end: offset + chunkSize - 1
}
};
const contentBuffer = await this.client.get(remoteFile, undefined, options);
const content = contentBuffer.toString();
return content;
} catch (err) {
console.error(`Reading failed at offset ${offset}:`, err);
return null;
}
}
async fetchFileInChunks(remoteFile, chunkSize = 2500) {
if (!this.startTime) this.startTime = Date.now();
this.remoteFile = remoteFile;
this.chunkSize = chunkSize;
let content = await this.readFile(remoteFile, this.offset, chunkSize);
if (content) {
this.wholeContent += content;
this.offset += chunkSize;
let elapsed = Date.now() - this.startTime;
if (elapsed < this.timeoutDuration) {
return this.fetchFileInChunks(remoteFile, chunkSize);
} else {
console.log(`Elapsed time: ${elapsed} ms, pausing...`);
return this.wholeContent;
}
} else {
console.log(`Completed fetching data from ${remoteFile}`);
return this.wholeContent;
}
}
}
export async function sftpConnection() {
const client = new SFTPClient();
const options = {
host: 'myhost.com',
port: 22,
username: 'username',
password: 'password'
};
try {
await client.connect(options);
console.log('Fetching pricelist-1.csv ...');
let pricelist1Content = await client.fetchFileInChunks('./pricelist-1.csv');
console.log(`Pricelist-1.csv content length: ${pricelist1Content.length}`);
let pricelist1Data = parseCSV(pricelist1Content);
console.log("Pricelist-1.csv Data:", pricelist1Data);
console.log('Fetching pricelist-2.csv ...');
client.partialData = [];
client.startTime = null;
client.offset = 0;
client.wholeContent = '';
let pricelist2Content = await client.fetchFileInChunks('./pricelist-2.csv');
console.log(`Pricelist-2.csv content length: ${pricelist2Content.length}`);
let pricelist2Data = parseCSV(pricelist2Content);
console.log("Pricelist-2.csv Data:", pricelist2Data);
} catch (err) {
console.error("Error in completing function:", err);
} finally {
await client.disconnect();
}
}
function parseCSV(data) {
let rows = data.split('\n');
let headers = rows[0].split(',');
let parsedData = [];
for (let i = 1; i < rows.length; i++) {
if (rows[i].trim() !== '') {
let values = rows[i].split(',');
let obj = {};
headers.forEach((header, index) => {
obj[header] = values[index];
});
parsedData.push(obj);
}
}
return parsedData;
}