CSV file - How to import

Java Script
#1

Hello,
I think you would appreciate some advice on how to import data from CSV file, when it is accessible via HTTP GET request.
(For example getting data from the e-shop platform Shoptet)

As an example I attach a script how to upload a CSV file that can be downloaded using HTTP GET
request method:

//doo.toast.info('Info','Downloading Shoptet order');

const parseCsv = function (csvContent, columnsSeparator = ',') {
const linesSeparator = '\n';
const fncGetValue = function (value) {
 if (value === 'null')
     return null;
   const valueTrim = value?.trim();
   if (valueTrim?.startsWith('"') && valueTrim?.endsWith('"'))
     value = valueTrim.substring(1, valueTrim.length - 1);
   return value;
 };

 let ret = null;
 let lines = csvContent?.trim().replace(/\r/g, '').split(linesSeparator);
 if (lines?.length) {
   let headers = lines[0].trim();
   if (headers.endsWith(columnsSeparator))
     headers = headers.substring(0, headers.length - 1);
     headers = headers.split(columnsSeparator).filter(header => !!header).map(header => fncGetValue(header));
   lines = lines.splice(1);
   ret = lines.filter(line => line?.indexOf(columnsSeparator) > -1).map(line => {
     const values = line.split(columnsSeparator);
     const obj = {};
     headers.forEach((prop, index) => obj[prop] = fncGetValue(values[index]));
     return obj
   });
 }
 return ret;
};

//let numberOfOrdSys = await doo.table.getCount('orders_by_systems',{filter: 'currency(eq)CZK'});

// call from server only
await doo.table.deleteRecordsBulk('orders_by_systems',10000, 'currency(eq)CZK');

 const csvLink = "Insert your CSV link here"
 const csv = await doo.functions.request.get(csvLink);
 const parsedCsv = parseCsv(csv, ';');
 
await doo.table.createRecordsBulk('orders_by_systems', parsedCsv);

I hope this is useful advice for you :slight_smile:

With kind regards,
Soňa

2 Likes
#2

Hi Soňa. Script is super but it does not work for me. Can you send me please an example of csv file? Because everything seems to be ok. When I debugging i see the structure of parsedCsv and it loks good.

Thank you. Josef

#3

Hi Josef,
The structure of csv must fit the table structure. If that does not work for you, please contact us at support@tabidoo.cloud. Then we can check your implementation.
Best regards
Michal

#4

Hi Josef, thanks for sending more information. The problem is that your table also contains number fields.
createRecordsBulk then does not create any records because it crashes on validation (the number is expected) - we recommend checking the return value of bulk operations for more information:
const result = await doo.table.createRecordsBulk('orders_by_systems', parsedCsv);
Sonia’s parser script only counts with text fields. A slight modification of the script can also handle number fields:

const fncGetValue = function (value) {
 if (value === 'null')
     return null;
   const valueTrim = value?.trim();
   if (valueTrim?.startsWith('"') && valueTrim?.endsWith('"'))
     value = valueTrim.substring(1, valueTrim.length - 1);
   return isNaN(value) ? value : Number(value);
 };

Best regards
Jan