Handling In-Field Commas in CSV import?
hofar...@houseoffusion.com
I'm trying to import a comma delimited CSV file using <cfloop
file="filename.csv">, but some records contain commas and they are throwing
everything off. I can't seem to figure out how to replace the in-field
commas without messing up the delimiter. Any ideas? Thanks in advance.
-Paul
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334270
hofar...@houseoffusion.com
There is absolutely no reason to parse a CSV document manually; there
are myriad tools for exactly this purpose. A quick Googling will turn
up a pile of options. You may need to tweak them if your file is
enormous and must be processed line-by-line, but even then you should
be able to use an off-the-shelf parser and just treat each individual
line as a separate CSV document (which they are), and then do your
aggregation between them however you see fit.
At the very least, build yourself a UDF that strictly does CSV parsing
so you don't have that logic mixed in with your business logic,
because it's more complicated than you think. And if you go this
route, ensure you consider the differences between "standard" and
Excel CSV format.
cheers,
barneyb
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334272
hofar...@houseoffusion.com
Any delimited file that has delimiters contained within a field is
supposed to use a text qualifier. For CSV, the most common qualifier
is double quotes, so your file would look like:
"1234","field 1","my big field, that has a delimiter or two, but is qualified"
If there are not text qualifiers and a field includes a qualifier then
it isn't a valid csv file. If it is qualified, you should break the
line up using the compound string "," as what you are splitting on,
then remove the first and last qualifiers.
Hope that helps,
Judah
On Thu, Jun 3, 2010 at 10:32 AM, <pa...@smashedvision.com> wrote:
>
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334273
hofar...@houseoffusion.com
Thanks for the replies, it seems like I'm taking the wrong approach.
Currently I am using a cfloop to loop over the file, then
#listgetat(FileLine,1)# to reference each field.
<cfloop file="C:\CSVs\2010-05-21.csv" index="FileLine">
<cfset record_type = '#listgetat(FileLine,1)#'>
</cfloop>
It seemed like a pretty simple solution and was working fine until I came
across fields with commas. As far as I can tell I can't use "'" as the
delimiter with listgetat. Does anyone have a better approach? Thanks again.
On Thu, 3 Jun 2010 10:37:34 -0700, Judah McAuley <ju...@wiredotter.com>
wrote:
>
> qualified"
>
>
> Hope that helps,
> Judah
>
>>
>>
>> throwing
>>
>>
>>
>>
>>
>> -Paul
>>
>>
>
>
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334278
hofar...@houseoffusion.com
This solution worked for me:
http://www.bennadel.com/blog/1903-Parsing-CSV-Data-With-ColdFusion-s-CFHTTP-Tag.htm
On Thu, 03 Jun 2010 14:27:08 -0400, Paul Henderson
<pa...@smashedvision.com> wrote:
>
>
>
>
>
>
> <cfset record_type = '#listgetat(FileLine,1)#'>
>
> </cfloop>
>
>
>
came
>
>
again.
>
>
>
>
>
>
> wrote:
>
>
>
>
>>
>
>
>> qualified"
>
>>
>
>
>
>
>
>>
>
>> Hope that helps,
>
>> Judah
>
>>
>
>
>>>
>
>
>>>
>
>
>>> throwing
>
>>>
>
>
>>>
>
>
>>>
>
>>>
>
>>>
>
>>> -Paul
>
>>>
>
>>>
>
>>
>
>>
>
>
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334290
hofar...@houseoffusion.com
On Thu, Jun 3, 2010 at 2:35 PM, Paul Henderson wrote:
>
> This solution worked for me:
>
> http://www.bennadel.com/blog/1903-Parsing-CSV-Data-With-ColdFusion-s-CFHTTP-Tag.htm
>
I've done that in the past with some success. It all depends on how
good the data is. GIGO, ja know?
There's a long, horrible story behind why I've had to parse bad CSV
data for years, but long story short, I settled on a java CSV parsing
library, and a bit of regular expression magic, and that's freaking
tackled everything.
I wrote a custom tag, but it's still Railo-only format, so right now
it's a Railo only deal. Eventually I'll probably make it portable.
Just for giggles.
:Den
--
If I am capable of grasping God objectively, I do not believe, but
precisely because I cannot do this I must believe.
Soren Kierkegaard
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334317