UPDATE: Parsing CSV Data Files In ColdFusion With csvToArray()

Posted October 22, 2010 at 5:37 PM by Ben Nadel

Tags: ColdFusion

Reader Comments

Thanks @ben! You rock!

@Stacey,

No problem - happy to finally get this fixed... after several years :)

Cool,
up until now I have always been using the cfx tag text2Query (http://www.emerle.net/old/programming/display.cfm/t/cfx_text2query/)

The only problem with using this it seems pretty slow when outputting the result through cfdump, but no problem looping over the results

@Richard,

CFDump can be a beast on its own since it outputs so much HTML.

Great post!

On a side note, I'm still really disappointed that the cfspreadsheet tag doesn't accept CSV files.

@Brian,

Thank my man. I really need to play around with CFSpreadsheet some more (ie. at all). Since I use CF8 in production still, it's been hard for me to really get motivated to try all the new features of CF9. My blog, at the very least, will be moving to CF9 soon; so, hopefully, then I'll put more effort into CF9 studies.

I am trying to parse RETS realty listings for a site I'm building. This is exactly what I needed. Works great until I found a record with a field that contained a room dimension as 7'10" x 10'8". Most of the records didn't use single and double quotes like this. Your code stopped on this field.
I have no quoted fields. I attempted to adjust the reg expressions with no success. Could you help me?
I can send you a sample file and my test code file.

@Gene,

Without looking at your code I'd suggest the PreserveSingleQuotes() function.

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=functions_m-r_14.html

Thanks Brian. I tried it with no success. The output array has no more content (or cells) when it hits this field and the input record has several more fields. I'm thinking the regEx that's trying to evaluate it is failing.

Ok, finally got around to actually using this new version and not my hacked version, but the following line has CF barking at me:

<cfset local.csvData = [ [] ] />

Invalid CFML construct found on line 401 at column 32.

ColdFusion was looking at the following text:
[

The CFML compiler was processing:

A cfset tag beginning on line 401, column 10.
A cfset tag beginning on line 401, column 10.

It's line 401 in my case sense I added this to an existing CFC I have.

Stacey,
I'm using CF8 and got the same error initially. I commented the line out with no apparent issues. There were a couple other spots I needed to modify for CF8 also, but I didn't document them. My version is working for me, except for the change I am working on for the regEx noted in my previous post.

@Gene,

How are your fields delimited? Tabs? Commas? I'll do some testing to see what I can come up with.

@Stacey,

That [[]] notation is an implicit array. I think you might need to be running 8.0.1 or higher for that to work. The nested arrays, I think, was an updater.

@Gene,

I've just went back to the previous version. I had already hacked it to work, and am hacking it some more for some other cases I need to handle. Some of my data looks like:

"Sam Smith" <sam@smith.com>,
Jim Jones <jim@abc.com>,
"Jones, Bob" <bob@big.com>,
Sally Jo <sally@xyz.com>

And I need it to handle that kind of quoted case as well. I managed to do that by creating a new regex for that type of field value.

@Ben,

Yep, running 8 here. :-( I did change how it creating the array, and got past that, but there were some other things too, so I just went back to my previous hacked version. Lie I told @gene, I had to make some other hacks too, so no big deal. I'll tell you though, this has been invaluable! Many kudos for your work on this!

@Stacey,

No problem. Sorry that this one threw a curve-ball at you :)

@Gene,

What are they using to produce the CSV data? If I remember correctly, this UDF was built on the CSV standard, which suggests that all fields with embedded special characters are themselves wrapped in double-quotes. Are they manually creating the data?

I can probably help you with the parsing, but I may be hesitant to put that kind of logic in the UDF as it appears to be non-standard.

Ben,
My CSV file is tabbed and no, it doesn't double quote anything. I suspect I am working with an unusual circumstance and wouldn't suggest including it in the UDF.
I planned on dropping the test for double quoted fields to simplify it for my situation and then adjust the regEx to catch the example I listed in my previous post. If you have time to look at it that would be great! I haven't done much with regEx.
Also, I am putting the data in a SQL database. I have the data types and lengths of the originating data. When I try to insert a field that is integer into the table I get a data type mismatch until I change the table column's type to varchar. I suspect I need to pull the values that are integer out of the array and assign them to a variable param'd as integer type to make the insertion work. I'm looking at around 75 fields with maybe 10 that are integer. Does this sound correct to you?

@Gene,

If your fields are tab-delimited, you might make your life easier by using something like:

listToArray( data, "#chr( 13 )##chr( 10 )#" )

... to break out rows and then:

listToArray( rowData, chr( 9 ) )

... to break out cells for each row. Assuming that none of the fields have embedded line breaks or tabs, treating the CSV data as "nested lists" is actually gonna be way faster than parsing with RegEx (I would guess).

Is that possible, do you think?

Ben,
I had decided to cfloop over the file so I am dealing with just one row at a time. I do have many empty cell though. Wouldn't I loose the empty cells with your suggestion?

I just finished a discussion with a friend that suggested I look at CFHTTP and converting to a query instead. His thought was to put "[NULL]" (or something) in the empty fields before running CFHTTP. I will look into that option.

Of course putting something in the empty cells first would allow me to do listToArray( rowData, chr( 9 ) ) as well.

@Gene,

Ah, good point. For something like that, it might be better to dip into the Java layer to do some sort of split-type approach, which works a bit better with empty values... I think.

Let me play around with some ideas.

@Gene,

First off, I totally forgot that listToArray() *can* handle empty values - there's an optional third argument as of ColdFusion 8.

That said, I put this together this morning - reSplit(). It's a splitting function that works off of regular expression patterns that can be used to define list delimiters:

http://www.bennadel.com/blog/2058-RESplit-Splitting-Strings-With-Regular-Expressions-In-ColdFusion.htm

That might help.

Ben,
Your comment on listToArray()'s ability to handle empty values is right on. I have to confess I was still running CF7 on my workstation when I tried this some time ago with no luck. I upgraded now and tried it. I think it will solve my problem without using any regEx.
Thanks for your advice!

@Gene,

Sounds groovy.

I might be going around this in a really strange way, just trying to connect the dots between the few things i actually know how to do....

I want to use google spreadsheets as an easy to use/share database. I can take google's CSV output and convert it to an array with your instructions (awesome!). The next step is figuring out how to run a query of that array and output the values.

I was planning to take the array and convert to a query object (in the fashion of your XML to Query post.) But am hung up on 2 things:
1) I have this nagging feeling that I'm rigging up a way overly-complicated system;
2) I can't figure out how to identify the proper array values in this structure anyway.

Wisdom?

@Jared,

I guess sometimes asking is all I need to do to clarify to myself what I need to be doing. I've got it, I've got it.

Ben, your posts are amazing. All the more amazing is the fact that people like me with really limited education/experience are able to pull off really cool stuff thanks to you!

Hi Ben, and everyone,

I released a new CFML tag for Railo today, based on the code you published here! It's called <cfcsv>.
Read more at http://www.railodeveloper.com/post.cfm/railo-custom-tag-cfcsv

I added some features to your original code, like returning the parsed data as a query, and determining your own text qualifier. You can see the new code here: http://www.railodeveloper.com/svn.cfm?repositorypath=CFCSV/cfcsv/cfcsv.cfc%3AHEAD

Thanks for writing this code, much appreciated :-)

Paul

I had to perform a similar task on a project couple yrs back wherein users could upload a csv file (fields in any order) and map it to DB fields. I used Microsoft text driver to read the text file. Basically I created a datasource in CF Admin pointing to the folder containing the csv files.

and then used cfquery to read the csv files. There are some issues with how the MS text driver reads the columns - it reads the first few rows and makes assumptions about datatypes, length, etc.

You can specify a data-type/length using a schema.ini file.

Ben, have you measured the impact on heap space? The speed is impressive, but I'm running into heap issues. You had mentioned some large datasets, but when I tried going over 10K records, I run out of heap when using CF8 Standard with a heap size of 768MB. When I monitor the heap as the test program runs, it just keeps climbing. Forcing a GC doesn't help. Subsetting the # of records sometimes helps. Any ideas?

Thanks for this, Ben. Needed to import a CSV of browsers/user agents mappings. :-)

@Jared,

Ha ha, glad you got it sorted out yourself :) Sometimes, you just gotta say the question "out load" and it makes a lot more sense.

@Paul,

Very cool stuff!

@Dharm,

I've used the database driver approach a couple of years ago. I remember it being pretty awesome. We just kept overwriting a file and running the same query that would extract it. We had some problems with it, but I can't even remember what they were (it was like 5 years ago).

@Tom,

I can't say I've actually looked at any memory consumption, only at perceived speed. I suppose you could build a SAX-style parser for CSV files where, rather than XML events, the parser could raise "field" and "record" events. Actually, that could be kind of a fun little project to tinker with. That way, you could, theoretically, read a file of infinite size since there would never be more than N number of characters in memory at any given time.

I'll see if I can play around with that idea, for funzies. I want to get more into anything that is event-driven.

@Michael,

Awesome :)

@Ben,
I'll think about the event driven approach as well. I've found his problem has more to do with when CF releases memory which I think you've raised before. I've even tried manually forcing garbage collection (a practice I don't like or recommend). Some has suggest running sections of code in threads which is supposed to cause CF to recover memory used by the thread. If I take the same amount of data and retrieve it using a query from the database, there is no problem. But try to read the data into a query from a CSV file, and you can watch memory getting consumed quickly.

@Thomas,

This morning, I tried to create an evented parser for a CSV file using a Buffered Input Stream.

http://www.bennadel.com/blog/2241-Parsing-CSV-Data-With-An-Input-Stream-And-A-Finite-State-Machine.htm

I know this is from a while ago, but I thought you might find it interesting. I think state machines are pretty powerful. I'm trying to learn more about them.

Hey Ben, I've been using this custom tag for a little while with great success, but now I'm moving to a shared server environment that does not allow use of Java objects... I know, right?

Any suggestions on how I can modify this to get rid of the Java object calls and functions? I'm not really sure how to translate the regex calls.

Any help would be greatly appreciated!

Dear Ben,

your CSV2Array UDF ist very nice. The only problem I have is that it stops parsing after 9999 rows. Is there any way to make it parsing the whole CSV? Mine has around 14000 rows.

Thanks in advance and thx for this nice UDF.
Greetings
Bernhard

Sorry, found the problem. cfdump stops at 9999. Your UDF is absolutely working. Thx again.

Cool stuff. Very good. Thanks

This is so useful! But I've encountered an odd issue. The import bombs when there are quotes within the delimited data. This is okay:

|"Test data"|

but this bombs:

|This "is test data"|

Any suggestions on how to deal with it? I don't want to do a global find/replace for quotes or items with the delimiter within the quotes will get screwy.

G'day mate:
This is the first CSV parser that I've encountered that will handle any CSV-curveball that I can throw at it. Good stuff. Most people's implementations - including Adobe's and Microsoft Excel's don't appear to be based on the CSV "spec", and fail with things like embedded CRLFs and the like.

There are a coupla UDFs on CFLib that fall into this category (or worse), and I'd like to add an updated function which uses your approach, but also adds a couple of different return-format options. Would it be OK if I lift your technique as the engine room for my UDF? I'll credit you accordingly, obviously.

I just think having the UDF in the central place CF people look for UDFs is better than relying on people finding it in a blog post (although you've probably done it for the traffic, so I get why you've done what you have ;-)

Anyway, lemme know what you think, mate.

Cheers for all your hard work!

--
Adam

Hello together,

I really can't tell how I enjoy this amazing function.
Now received a CSV file from a customer. The file is Windows ANSI not UTF-8 but ISO-8859-1. Notepad++ shows me a CR and LF at the end of each row. Delimiters are Tabs (chr(9)).
When I try to parse the file I get an empty array.

I tried to fix it and got it running with

<cfset arguments.csv = Replace(arguments.csv, chr(9), ";", "ALL") />
<cfset arguments.csv = ListToArray(arguments.csv, "#chr(13)##chr(10)#") />
<cfset arguments.delimiter = ";" />
<cfset var i = "" />
<cfloop index="i" from="1" to="#ArrayLen(arguments.csv)#">
<cfset arguments.csv[i] = ListToArray(arguments.csv[i], arguments.delimiter) />
</cfloop>
<cfset local.csvData = arguments.csv />

but wonder why Bens function delivers an empty array. I think I missed something.

Thanks again for this still awesome function.

Yours
Bernhard

Your code was the closest thing I've found to obtaining some direction for converting ISO fields to values that CF can translate properly. Thank you for posting!

Post A Comment

Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Supported HTML tags for formatting: <strong>bold</strong>   <em>italic</em>   <code>code</code>
	Remember my information
	Subscribe to comments
	Send me a copy of this comment