How to escape commas in CSV file

Board index » YUI 2.x: » DataTable and DataSource

Page 1 of 1

[ 10 posts ]

New Topic | Post Reply | Print view

Previous topic | Next topic

Roman Hoyenko

Username: hoyenko
Joined: Wed Dec 02, 2009 1:09 pm
Posts: 30
GitHub: Romario
Gists: Romario

Tags:

How to escape commas in CSV file

Posted: Fri Jan 14, 2011 9:19 am

+0-

I have a csv file that I read like this:

Code:

                    var myDataSource = new YAHOO.util.DataSource("myfilename.csv");
                    myDataSource.responseType = YAHOO.util.DataSource.TYPE_TEXT;
                    myDataSource.responseSchema = {
                        recordDelim: "\n",
                        fieldDelim: ",",
                        fields: myFields,
                        metaFields: {
                            totalRecords: myTotalRecords
                        }
                    };

It's a regular CSV file and in CSV you can put fields in quotes if they contain commas that are not separators, something like this

Code:

field1, field2, field3
"this is field1", "this is field2, it has comma inside", "this is field 3"

But putting the quotes doesn't seem to help. Is there some way to make this work?

Alberto Santini

YUI Contributor

Username: icebox
Joined: Wed Sep 16, 2009 3:16 am
Posts: 1066
Location: Milan, Italy
Twitter: santinialberto
GitHub: albertosantini
Gists: albertosantini
IRC: icebox

Tags:

Re: How to escape commas in CSV file

Posted: Sat Jan 15, 2011 12:04 am

+0-

Hello Roman.

I think you should pass to the datasource an url (not a file) providing csv data.

In my code I added

myDataSource.dataType = YAHOO.util.DataSource.TYPE_TEXT;

Hope that helps,
Alberto

Roman Hoyenko

Username: hoyenko
Joined: Wed Dec 02, 2009 1:09 pm
Posts: 30
GitHub: Romario
Gists: Romario

Re: How to escape commas in CSV file

Posted: Mon Jan 31, 2011 7:53 am

+0-

I provide the url to the file and it is being read, my problem is that the fields get misaligned if I have a comma in one of the fields.
The comma is the separator, so it doesn't work even if I put the field in the quotes.

Alberto Santini

YUI Contributor

Username: icebox
Joined: Wed Sep 16, 2009 3:16 am
Posts: 1066
Location: Milan, Italy
Twitter: santinialberto
GitHub: albertosantini
Gists: albertosantini
IRC: icebox

Re: How to escape commas in CSV file

Posted: Mon Jan 31, 2011 8:53 am

+0-

Hello Roman.

You may change the field delimeter, for instance, to '#'.

Regards,
IceBox

Todd Smith

YUI Contributor

Username: stlsmiths
Joined: Thu Nov 05, 2009 10:03 am
Posts: 675
GitHub: stlsmiths
Gists: stlsmiths
IRC: t_smith

Re: How to escape commas in CSV file

Posted: Mon Jan 31, 2011 10:21 am

+0-

Seems like this may be a bug in DataSource TYPE_TEXT. I looked at the source in DataSourceBase.js and the parseTextData method uses a simple JS ".split" string method which won't know about string delimeters. Not sure if others have encountered this.

I'm not real current on my Regex skills, but I think a "greedy-aware" Regex parsing may be written to overcome using the .split technique that could resolve this.

I don't think a user should have to reformat a comma delimiter, especially if they don't control the data feed.

Todd

Alberto Santini

YUI Contributor

Username: icebox
Joined: Wed Sep 16, 2009 3:16 am
Posts: 1066
Location: Milan, Italy
Twitter: santinialberto
GitHub: albertosantini
Gists: albertosantini
IRC: icebox

Re: How to escape commas in CSV file

Posted: Mon Jan 31, 2011 10:36 am

+0-

Hello Todd.

I agree, but it was, of course, a workaround.

Another workaround, it would be tweaking the data in doBeforeCallback.

For instance,

myDatasource.doBeforeCallback = function (oRequest, oFullResp, oParsedResp) {
// Remove the first result (i.e., the headers);
oParsedResp.results.shift();
return oParsedResp;
};

There it could be added a different parsing.

Regards,
Alberto

Edit1:

For instance, (I tested the split with the regex, but not the oParsedResp)

Code:

myDatasource.doBeforeCallback = function (oRequest, oFullResp, oParsedResp) {
    // '"a", "b,c,d", "e"'.split(/,(?!(?:[^",]|[^"],[^"])+")/);
    oParsedResp = oFullResp.split(/,(?!(?:[^",]|[^"],[^"])+")/);
    return oParsedResp;
};

Edit2: This one is better: '"a","b,c, d","e"'.split(/,(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)/)

Last edited by icebox on Mon Jan 31, 2011 1:46 pm, edited 1 time in total.

Todd Smith

YUI Contributor

Username: stlsmiths
Joined: Thu Nov 05, 2009 10:03 am
Posts: 675
GitHub: stlsmiths
Gists: stlsmiths
IRC: t_smith

Re: How to escape commas in CSV file

Posted: Mon Jan 31, 2011 11:34 am

+0-

Alberto, I haven't tested your Regex yet, but was thinking maybe it could be proposed to Roman for his application to place into his code as an overloaded replacement method for the parseTextData method of DataSource. This would be a workable "hack" potentially for him to get around his problem, assuming the Regex works properly.

Todd

Roman Hoyenko

Username: hoyenko
Joined: Wed Dec 02, 2009 1:09 pm
Posts: 30
GitHub: Romario
Gists: Romario

Re: How to escape commas in CSV file

Posted: Mon Jan 31, 2011 11:45 am

+0-

thank for the replies, I will try the workaround.

I don't want to change the separator for this application since the comma separated values file could be read by Excel without any change and I re-use this file for this reason.

Roman Hoyenko

Username: hoyenko
Joined: Wed Dec 02, 2009 1:09 pm
Posts: 30
GitHub: Romario
Gists: Romario

Tags:

Re: How to escape commas in CSV file

Posted: Mon Jan 31, 2011 2:35 pm

+0-

Tried it and it takes a very long time to execute (I get stuck script warning in Firefox). I use pagination, so the return result is not that big (but it has plenty of comma separators, I think that is causing performance degradation)

Alberto Santini

YUI Contributor

Username: icebox
Joined: Wed Sep 16, 2009 3:16 am
Posts: 1066
Location: Milan, Italy
Twitter: santinialberto
GitHub: albertosantini
Gists: albertosantini
IRC: icebox

Tags:

Re: How to escape commas in CSV file

Posted: Tue Feb 01, 2011 2:54 am

+0-

Hello Roman.

I worked out a tiny example, using another approach.

I parsed the response, replacing commas outside the quotes with another delimeter and removing then the quotes. Of course, it is a bit primitive, but you may arrange the parsing for your needs.

http://pastebin.com/3p3Jub7p

Hope that helps,
IceBox

Page 1 of 1

[ 10 posts ]

New Topic | Post Reply | Print view

Previous topic | Next topic

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum