loop through a csv from the second line

[critical5]

Hi,

I am trying to loop through a csv file wich comes with column headers but the trciky part is some of the headers(1 or 2) are missing and I have no control over how it comes back. Is there anyway that I can skip or replace that header with a decent title?

The end product needs to be inserted into a MySql database using coldfusion 7. The file will be like this:

Name, surname,,(missing header)DOB
Richard,Patey,Mr,01/01/1975

When I loop like this

<cfloop index="i" list="#fileContent#" delimiters="#chr(10)##chr(13)#">
<cfquery name="importcsv" datasource="#request.dsn#">
INSERT INTO results(REFERENCE, FIRST_NAME, LAST_NAME, TITLE ,DOB)
VALUES
('#listgetAt('#i#',1)#',
'#listgetAt('#i#',2)#',
'#listgetAt('#i#',3)#',
'#listgetAt('#i#',4)#',
)
</cfquery>
</cfloop>


The above code errors: "Invalid list index 2" but works okay if I populate the blank with something or just remove the columns headers.

I am reading this csv file from the server using <cffile> - is there anyway I can know how many lines there are in a csv file? Then I can loop from line 2?
Hoping that I make sense - could someone help please.

Thanks

 

[r937]

can i ask why you're doing this with coldfusion?

why not just load the csv with mysql's LOAD DATA INFILE command? you can even use the IGNORE 1 LINES option to skip over the header

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[critical5]

Hi r937,

thanks for the reply. The reason is the users will be performing this task and I do not think they can use LOAD DATA INFILE command - we are talking about basic users. so if I could get coldfusion to purify the data and then store it it would save us from manual repetitive job which could be done repetitively through the week.