-1

I'm trying to parse a CSV using the CFCSV custom tag. Here's my CFML code:

<cfset data = fileRead( "inputtest.csv", "utf-8" ) />
<cfcsv action="parse" data="#data#" variable="csv" hascolumnnames="true" delimiter="," />
<cfloop query="csv">
    <cfoutput>#csv.name#</cfoutput>
</cfloop>

.. and my test CSV:

"Email","Name","Address1","Address2","City","State","ZIP","Country","Phone"
"somedude@foobar.com","Some Dude","3129 golden bridge ave",,"somecity","somestate","somezip","usa","1234567890"

The error I get is:

column [NAME] not found in query, columns are [Email,Name,Address1,Address2,City,State,ZIP,Country,Phone]

If I change the loop to output anything other than name, it works. But I can't get the name. Even changing the columname from Name to TheName results in the same error:

column [THENAME] not found in query, columns are [Email,TheName,Address1,Address2,City,State,ZIP,Country,Phone]

But again, I have no problem outputting the email address or any other column. Changing the location of the column in the CSV and/or changing from cfloop query plus cfoutput to just cfoutput query has no effect.

If I cfdump the query, the column is there.

Leigh
  • 28,765
  • 10
  • 55
  • 103
Kevin B
  • 94,570
  • 16
  • 163
  • 180
  • Consecutive delimiters are treated as a single delimiter by default. What happens if you put a pair of empty quotes `""` in the column for "Address2"? – ale Sep 05 '12 at 19:28
  • Fwiw, the sample code runs perfectly for me with 3.3.1. Is that really the full code/file content? – Leigh Sep 05 '12 at 19:47
  • @KevinB - Yeah it does not make sense, especially as that exact same code works fine for me. So something must be different on your end ... (Edit: The previous comment went away, but I am leaving this response for context) – Leigh Sep 05 '12 at 19:58
  • 1
    It must be an extra or hidden character or an encoding problem with the CSV, if I create a test csv from scratch using the content above it works. Doesn't really help me solve the problem though, i can't re-create 3k records so easily. – Kevin B Sep 05 '12 at 20:02
  • can you rename the column to something other than name? Is name a reserved word? – Matt Busche Sep 05 '12 at 20:30
  • @mrbusche That was one of the first things I tried. – Kevin B Sep 05 '12 at 20:35
  • @KevinB I figured, but wanted to make sure – Matt Busche Sep 05 '12 at 20:36
  • @KevinB - That seems likely, but it is the type of thing where we would need to see the actual problem file to figure out what is wrong. – Leigh Sep 05 '12 at 21:02
  • @Leigh And I definitely can't share that file, it has private information. – Kevin B Sep 05 '12 at 21:06
  • @KevinB - Understood. (I figured that was case, or you would have included it in the original post). Glad you found some work around though. – Leigh Sep 05 '12 at 21:10

2 Answers2

5

I was able to reproduce the problem by adding a newline at the start of the CSV file, but you could see this same error anytime there is a problem parsing the column header line.

Russ
  • 1,931
  • 1
  • 14
  • 15
  • 1
    Good detective work! So a simple `trim()` might help, IF it is just an extra new line. – Leigh Sep 05 '12 at 21:18
  • 1
    An extra new-line does not generate the same error, however i did notice that there was an extra character at the beginning when i dump the contents before encoding it as UTF-8. Trimming that extra character away may have helped – Kevin B Sep 06 '12 at 14:13
1

There was something wrong with the CSV's format, I couldn't figure out the exact issue. To solve the problem, I had the CSV re-exported as a tab delimited text file, opened it in Calc, then saved it in tab delimited format. Now it works. I wish I knew what was wrong with the original file.

Leigh
  • 28,765
  • 10
  • 55
  • 103
Kevin B
  • 94,570
  • 16
  • 163
  • 180