Tip Csv Importer

Classic Servoy
1

Hi,

I was looking for a javascript csv importer and found the module that Benoit Savignac of LOGIsoft placed in the Servoy Magazine.

This works great, the only problem I had is that my delimiter is a TAB and there are fields (enclosed in " ") that contain Tabs which
should not be handled as fieldseparator.
I looked for this on the internet and found a tip from Ben Nadel Of Kinky Solutions.

So I now use the following code

calling the readMethod :

//first argument: boolean, first row contains fieldnames
//second argument: string, ColumnNames seperated by: ":"
//third argument: string, the file to be imported.
readFile(false,"a1:a2:a3:a4:a5:a6:a7:a8:a9:a10","C:/fmpfiles/V10881-9717.tab");

The readmethod :

//Based on an article in the Servoy Magazine 
//Thanks to 
//David Workman of Data-Mosaic
//Rich Coulombre of The Support Group
//Harjo Kompagnie of Direct-ICT
//Marcel Trapman of IT2Be
//Paul of DNA Diagnostics Center
//Benoit Savignac of LogiSoft
 

var fieldnames = arguments[0]    // boolean, first row contains fieldnames
var columnnames = arguments[1]   // string, ColumnNames seperated by: ";"
var filename = arguments[2]      // string, the file to be imported.

// split the columnames into an Array
var columnArray = columnnames.split(':')
// determine how many columns are used
var columnCount = columnArray.length


//read csv file
var fileContents = application.readTXTFile(filename)
//parse the csv file into array (lines) of arrays (columns)
var fileLinesColumns=csvParser(fileContents,'\t');
 
//determ if the first line has fieldnames, which should not be imported
if(fieldnames == true) var begin = 1
else var begin = 0

//fileLinesColumns is now an array (lines) of arrays (columns)
//now loop through the array to get to the cells
for ( i = begin ; i < fileLinesColumns.length ; i++ )
{
	foundset.newRecord();
	for (var x = 0 ; x < columnCount ; x++)
	{
		var index = foundset.getSelectedIndex()
		var record = foundset.getRecord(index)
		if(fileLinesColumns[i][x]) //check if cellArray is not empty or null
		{
			var column = fileLinesColumns[i][x]
			var columnlength = column.length
            var a = column.substring(0,columnlength)
            //replace vertical tabs by newlines and groups by newlines
            var a = (a.replace(/\v/g,'\n')).replace(/\x1D/g,'\n')    
            record[columnArray[x]] = a
		}
	}
}

the csvParser method :

//Based on information on the internet
// thanks to Ben Nadel @ Kinky Solutions
//
// This will parse a delimited string into an array of
// arrays. The default delimiter is the comma, but this
// can be overriden in the second argument.
// Check to see if the delimiter is defined. If not,
// then default to comma.
//
strDelimiter = arguments[1]
strData = arguments[0]
strDelimiter = (strDelimiter || ",");
 

// Create a regular expression to parse the CSV values.
var objPattern = new RegExp(
(
// Delimiters + 
"(\\" + strDelimiter + "|\\r?\\n|\\r|^)" + 

// Quoted fields.
"(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +

// Standard fields.
"([^\"\\" + strDelimiter + "\\r\\n]*))"
),
"gi"
);
 
// Create an array to hold our data. Give the array
// a default empty first row.

var arrData = [[]];
 

// Create an array to hold our individual pattern
// matching groups.


var arrMatches = null;
 
// Keep looping over the regular expression matches
// until we can no longer find a match.

while (arrMatches = objPattern.exec( strData )){
 

	// Get the delimiter that was found.
	var strMatchedDelimiter = arrMatches[ 1 ];
 

	// Check to see if the given delimiter has a length
	// (is not the start of string) and if it matches
	// field delimiter. If id does not, then we know
	// that this delimiter is a row delimiter.

	if (strMatchedDelimiter.length && (strMatchedDelimiter != strDelimiter))
	{
		// Since we have reached a new row of data,
		// add an empty row to our data array.
		arrData.push( [] );
	}
 
	// Now that we have our delimiter out of the way,
	// let's check to see which kind of value we
	// captured (quoted or unquoted).
	if (arrMatches[ 2 ])
	{
		// We found a quoted value. When we capture
		// this value, unescape any double quotes.
		var strMatchedValue = arrMatches[ 2 ].replace(	new RegExp( "\"\"", "g" ),	"\"");
	} 
	else 
	{
		// We found a non-quoted value.
		var strMatchedValue = arrMatches[ 3 ];
	}
 
	// Now that we have our value string, let's add
	// it to the data array.
	//application.output(strMatchedValue)
	arrData[ arrData.length - 1 ].push( strMatchedValue );
}
 

// Return the parsed data.

return( arrData );

I use this for a day now so it is not tested in extent, so any feedback is welcome !!

Thanks to all the guys mentioned in the methods above !!!

Servoy is great and the forum makes it even greater !!!

Regards,

Hans