Need help with csv read.

[siriiven]

I have some csv files that are in this format:

"Source","Requester ID","ID","Start Date","End Date","location","Value","TimeStamp"

"DCA","tedkrasav","9913457","12/4/2016 12:00:00 AM","12/18/2016 12:00:00 AM","Room_121",,"12/3/2016 2:50:03 PM"

What I am trying to do is read the file and insert all of the individual columns into a DB table.

I am trying to do this method just to see the individual columns but I keep getting an error that states "Invalid list index 2.In function ListGetAt(list, index [, delimiters]), the value of index, 2, is not a valid as the first argument (this list has 1 elements). Valid indexes are in the range 1 through the number of elements in the list" :

<cffile action="read" file="csvdemo.csv" variable="csvfile">

<cfloop index="index" list="#csvfile#" delimiters="#chr(10)##chr(13)#">

<cfoutput>

#listgetAt('#index#',1, ',')# #listgetAt('#index#',2, ',')# #listgetAt('#index#',3, ',')# #listgetAt('#index#',4, ',')#

</cfoutput>
</cfloop>

Does anyone know what I am doing wrong?

[WolfShade]

My assessment could be wrong, but I believe that the delimiter should be #chr(13)##chr(10)#, not the other way around.

HTH,

^_^

[siriiven]

I changed that around but I still received the same error.

[WolfShade]

Odd.  Okay, try this.  Set the delimiter to #chr(13) & chr(10)# and see if that fixes it.

HTH,

^_^

[siriiven]

Still the same error. It keeps stating the list only has 1 element when I try to access the  additional columns.

[WolfShade]

Just out of curiosity, is either the server or your workstation _not_ a Windows system?

chr(10) is a linefeed (new line) character.  chr(13) is the carriage return.  chr(13) & chr(10) is a Windows new line.  If you're using a Mac or Linux, that may be why it's not working.  Just a guess.

HTH,

^_^

[siriiven]

No it's windows.

[WolfShade]

I'm at a loss.  The issue is probably not the new line delimiters.  For some reason, your code isn't seeing the first comma, so it thinks the whole line is the only index.

Are you checking the .csv file prior to processing?  Are you sure that these files are in exactly the format you stated?

V/r,

^_^

[siriiven]

Ok I think I have made some progress. The issue was that I was not fully qualifying where the name of the csv was coming from. When I manually typed it in it started working. So I have fixed that and I am now getting the correct columns.

So the only other thing I would like to do is to not read the first row of each file since those are the headers. How do I disregard the first row of the read?

[WolfShade]

Mmmm.. what I would do (not what most people would do) is to use ListDeleteAt() to remove the first index, then loop through the list.

HTH,

^_^

[Steve Sommers]

There is a java csv reader that may make this easier for you. I'm on the road and I don't have access to example code but you could google "coldfusion java csv" and you'll probably find examples.