CSV to array
Jeff Thies
data I have has 20 records per row, some are quoted and some not.
Seems like there's a lot of potential gotchas!
Jeff
Paul Blay
Is this well formed CSV ?
If I remember correctly non-quoted fields should not contain commas
and if quote " marks inside fields should be double quoted "".
Oh, and I think dates are supposed to be inside # marks, but I'm
not sure.
Paul
Jeff Thies
Seems to be.
>
> If I remember correctly non-quoted fields should not contain commas
> and if quote " marks inside fields should be double quoted "".
My first run at CSV data. It took me a while before I realize the rows
were seperated by newlines. I've decided to parse this server side, as I
know of no way for javascript to deal with newlines in variables!
Cheers,
Jeff
Guido
for a general "text read" routine you may look at Computer - Programming -
VBA at http://www.geocities.com/holonet2001/ to get some idea how to do it
in JS.
Guido
--
HoloGuides
We are proud to be of service.
http://www.geocities.com/holonet2001/
Jeff Thies wrote in message <39CAD280...@sprintmail.com>...
GalaSoft Laurent Bugnion
WebLoadFile will return you a string containing your file. If rows are
separated with new lines, and fields separated with commas, something
like this should work:
strCSVFile = strCSVFile.split( '\n' );
for ( index = 0; index < strCSVFile.length; index++ )
{
strCSVFile[ index ] = strCSVFile[ index ].split( ',' );
}
This should turn your file into a 2 dimensions array, each field being
then access with
strCSVFile[ rowNumber ][ fieldNumber ];
If you send me one of your CSV files, I can test it with WebLoadFile and
tell you if it works fine.
WebLoadFile page:
http://www.galasoft-LB.ch/myjava/WebLoadFile/Demo/Demo.html
Laurent
--
GalaSoft Laurent Bugnion
Webdesign, Java, JavaScript: http://www.galasoft-LB.ch
Darjeeling teas: http://www.galasoft-LB.ch/indiantea
Support the children of Calcutta: http://www.calcutta-espoir.ch
Fox
.js file), so the I developed a little tool that might assist you in
converting csv into a two-dimensional JavaScript Array.
Instructions:
Open the csv file in a text processor. Select All and copy.
Run the following converter "app" in a browser and paste the csv data
into the textarea. Click on the convert button.
After a second or so, the original file will be converted into
JavaScript code which "hard-codes" the data into an array. Just copy
this output into your HTML file and your good to go.
Features:
Pre-scans data for quoted material and commas that might be within the
quotes (and subsequently add extra unintential fields) -- converts those
commas into a caret (^), then splits the data by record, then further
splits each record into the fields. Carets are restored to commas for output.
All array members generated are strings. Keep this in mind when dealing
with number values -- use parseInt or parseFloat. All strings are
bounded by single quotes to preserve any original double-quotes.
The fields of Record #0 are the names of the fields.
The number of records can be gotten by (csvData.length - 1) [remember
record 0 is just field names].
This program makes no assumptions about empty fields. The number of
fields will be represented by csvData[recordNum].length.
I wrote this on a Mac. I THINK I took care of the PC newline issue. If
this program only ever returns one long record (a single dimensional
array), let me know.
Hope this helps (code follows),
Fox
***************
<xmp><!-- remove this line to run -->
<title>CSV to JS Converter</title>
<script language = javascript>
function
csvhandler(f)
{
var output = "var csvData = new Array();\n";
var forequote =0;
var backquote = 0;
var data = f.data.value;
forequote = data.indexOf('"', backquote + 1);
backquote = data.indexOf('"', forequote + 1) + 1;
while(forequote != -1)
{
tdata = data.substring(forequote, backquote);
tsplit = tdata.split(",").join("^");
data = data.substring(0, forequote) + tsplit + data.substring(backquote);
forequote = data.indexOf('"', backquote + 1);
backquote = data.indexOf('"', forequote + 1) + 1;
}
var tempArray = data.split("\r");
if(tempArray.length == 1) tempArray = data.split("\n");
var fields;
for (var r = 0; r < tempArray.length; r++)
{
fields = tempArray[r].split(",");
output += "csvData[" + r + "] = new Array();\n";
for (var c = 0; c < fields.length; c++)
{
fields[c] = fields[c].split("^").join(",");
output += "csvData[" + r + "][" + c + "] = '" + fields[c] + "';\n";
}
}
f.data.value = output;
return false;
}
</script>
<body>
<center>
<h1>CSV to JS Converter</h1>
<form name=exchanger onsubmit = "return csvhandler(this)">
<textarea name=data rows = 24 cols = 80></textarea>
<br><br>
<input type=submit value = Convert onclick = "return csvhandler(this.form)">
</form>
</center>
</body>
</xmp><!-- remove this line to run -->
*************************************************************
Paul Blay
> Hi Jeff,
>
> Jeff Thies wrote:
> >
> separated with new lines, and fields separated with commas, something
> like this should work:
>
> strCSVFile = strCSVFile.split( '\n' );
>
> for ( index = 0; index < strCSVFile.length; index++ )
> {
> strCSVFile[ index ] = strCSVFile[ index ].split( ',' );
> }
This bit would be tripped up by any text fields that include a comma.
A problem that bit me the first time I tried to do this in Visual Basic.
"This is one field",1,"So, is this a single field?","This field includes ""quotes"""
<snip>
Paul
GalaSoft Laurent Bugnion
Paul Blay wrote:
>
> "GalaSoft Laurent Bugnion" wrote ...
> > Hi Jeff,
> >
> > Jeff Thies wrote:
> > >
> > WebLoadFile will return you a string containing your file. If rows are
> > separated with new lines, and fields separated with commas, something
> > like this should work:
> >
> > strCSVFile = strCSVFile.split( '\n' );
> >
> > for ( index = 0; index < strCSVFile.length; index++ )
> > {
> > strCSVFile[ index ] = strCSVFile[ index ].split( ',' );
> > }
>
> This bit would be tripped up by any text fields that include a comma.
> A problem that bit me the first time I tried to do this in Visual Basic.
>
> "This is one field",1,"So, is this a single field?","This field includes ""quotes"""
>
> <snip>
>
> Paul
I would say that this is a common problem when you handle CSV files,
isn't it ;-) Try to open this in Excel, I wonder what it does with those
extra commas...
Laurent
--
GalaSoft Laurent Bugnion, +41 (79) 3108701
Webdesign, Java, JavaScript: http://www.galasoft-LB.ch
Darjeeling Tea: http://www.galasoft-LB.ch/indiantea
Support Calcutta's children: http://www.calcutta-espoir.ch
Paul Blay
>
> Paul Blay wrote:
<snip>
> > This bit would be tripped up by any text fields that include a comma.
> > A problem that bit me the first time I tried to do this in Visual Basic.
> >
> > "This is one field",1,"So, is this a single field?","This field includes ""quotes"""
> >
> > <snip>
> >
> > Paul
>
> I would say that this is a common problem when you handle CSV files,
> isn't it ;-) Try to open this in Excel, I wonder what it does with those
> extra commas...
Excel does just fine, I used to use a program called Framework that completely
stuffed this up. For a long time I had the habit of doing global search and
replace for all " and , inside files before export.
It looks like the solution provided by Fox should handle this properly.
Paul