Processing a CSV file

[Dave]

We often get asked about reading data in from EXCEL CSV files. Used to be the case that you had to use a Custom Resource to do this sort of thing but, now that we have server side JavaScript and a library of File Services, there's really no need.

Here's a simple example that reads from a CSV file, chops the data into rows and columns, and then writes it to an Ebase table:

Code: Select all

importPackage(com.ebasetech.xi.api);
importPackage(com.ebasetech.xi.services);

// read the csv
var m = FileServices.readFile("C:/tms_certificates/spreadsheet/matrix.csv");

// put each line in an array cell
var lines = m.split(/\r\n|\n/);

// process each line in the array into an Ebase table, skipping the first one (the header)
for &#40;var i=1; i<lines.length; i++&#41; &#123;
   // put each comma seperated field into an array cell
   var data = lines&#91;i&#93;.split&#40;','&#41;;

   // insert table row
   tables.CERTIFICATES.insertRow&#40;&#41;;
   tables.CERTIFICATES.COMPANY.value=data&#91;0&#93;;
   tables.CERTIFICATES.TRAINING_DATE.value=data&#91;1&#93;;
   tables.CERTIFICATES.DELEGATE_NAME.value=data&#91;2&#93;;
   tables.CERTIFICATES.CERT_NO.value=data&#91;3&#93;;
   tables.CERTIFICATES.COURSE_TITLE.value=data&#91;4&#93;;
   tables.CERTIFICATES.INSTRUCTOR.value=data&#91;5&#93;;
   tables.CERTIFICATES.SITE.value=data&#91;6&#93;;
   tables.CERTIFICATES.EXT_CERT_CARD_NO.value=data&#91;7&#93;;
   tables.CERTIFICATES.EXPIRY_DATE.value=data&#91;8&#93;;
&#125;

// scroll to the first line in the table
controls.C.scrollToTop&#40;&#41;;&#91;code&#93;&#91;/code&#93;