Scriptable import problem

Classic Servoy
1

Hi,

We are developing a solution where data has to be imported from another system in a regular basis.

The data exported from another system is in CSV format.

"7888785","Hong Kong",02/03/2005,"description goes here with comma,","keyword"

I wrote the following script to import the data from the csv file.

//**************************************************************//
var servoyFormName       = "contactList"  
var sourceFileName       = sysadmin.auto_import_folder + "\\" + "sourceContact.CSV"

var dbColumnArray        = new Array (
                           "code", "country", "enter_date", "keyword", "description"
                           )
var columnSeperator      = "," 
var recordSeperator      = "\n" 

//**************************************************************//

var fileContents = application.readTXTFile(sourceFileName) 
var frmObj       = forms[servoyFormName];

var rowData      = fileContents.split(recordSeperator); //put each line into an array item 

for ( i = 0 ; i < rowData.length ; i++ ) 
{ 
   frmObj.controller.newRecord(); 
   var rowDataStr   = rowData[i];
   var cellsArray   = rowDataStr.split(columnSeperator); 
   
   for (var k = 0 ; k < dbColumnArray.length ; k++) 
   { 
      var fieldValue = cellsArray[k];
   
     //************ REMOVE THE DOUBLE QUOTES AT THE START ************//
      if ( fieldValue.substr(0, 1) == "\"")
      { 
       fieldValue = fieldValue.substr(1, fieldValue.length)
      }
   
     //************ REMOVE THE DOUBLE QUOTES AT THE END ************//
      if ( fieldValue.substr(fieldValue.length-1, 1) == "\"")
      { 
       fieldValue = fieldValue.substr(0, fieldValue.length -1)
      }
     //**************************************************************//
   
      var currentField       = dbColumnArray[k];
      frmObj[currentField]   = fieldValue;
   
   }
   //**************************************************************//
}

The problem is I don’t know how to split the columns in a row into an array.

If I split it using the seperatot comma (,) then if there is a comma in a field value then the record is splitted in a wrong way.

Any suggestions please..

Thanks

2

split on: ",

than it get’s split only there.

than remove just the last "

Hope this helps

3 
"7888785","Hong Kong",02/03/2005,"description goes here with comma,","keyword"

Yes..

I tried that too.. But you might have noticed that some of the fields like date are not surrounded with quotes.

Also some of the source files does not have quotes at all

4

hmm, yes indeed.

You have a problem than!

5

Thanks for your help.. hope some one will come up with an intelligent way for doing this.. since I don’t think that I’m the first one to do such import

Cheers

6

I think you’re input format is sligthly off.

The way to go about storing data in CSV format is the following:
1- you have you delimiter, in this case a comma
2- when the data in your field has the delimiter in it, surround the entire field with double quotes
3- if there are double quotes in the data in your field, surround that quote by double quotes as well.

Example:
data to export: She said: “yes, that’s true”
result in file: ,“She said: “”“yes, that’s true””“”,

data to export: She said: “hello”
result in file: ,She said: “hello”,

data to export: She said something, didn’t she?
result in file: ,“She said something, didn’t she?”,

Now, if you get this to import, you can parse the string properly, if you write the right code to do so. Using split to create an array won’t do it, you will have to do this by searching of indexes of delimiters and double quotes and thus analyse each line, to get to the correct result.

Would give you the code if I had, but haven’t got it…

Hope this will help you,

Paul

7

Hi Ahmad,

I always use tab delimited format – CSV is a hazzle to parse…
otherwise you’d need to do something like:

for(each record) {
while(more text) {
if(the text starts with a " ) {
the text upto ", or to the " at the end is a column
} else {
the text upto , is a column
}
}
}

The other option is to import straight into your backend database – this will be a lot faster.

8

Hi Pbakkar & Swingman,

Thanks a lot for your suggestions..

I made it as a rule so that the fields in the source file must be surrounded by double quotes and delimited by comma. Also as per Pbakkar I have surrounded all the quotes within the field value with double quotes.

For eg:

"," this' ;'is' "," field1 value, this is field 2

becomes

""","" this' ;'is' "","" field1 value","this is field 2"

Now I have to find a way to parse this so that I seperate the fields correctly. Please see that there is a comma (surrounded by quotes) within the field value so I could not use the split function using the delimiter (“,”)

Not yet figured out how to do this.. I will try my best.. Any help will be appreciated

Thanks