CF10 - CFLoop with a File - Only read in first row

[SGroty]

Hi,

I'm trying to read the header row from a very large(83k) tab delmited file.  I want to automate an upload to a temp table to append data, but I need to know the column headers to set up the table for Bulk Insert.

How can I read in the only the first row?  CFSpreadsheet doesn't work with XSLX files, especially 83k row ones, it would just crap out.  So I'm going the tab delmited route here.

Thanks

[Reed_Powell-ttnmOb]

Use CFLOOP and exit after the first row.

[SGroty]

I'm trying not to read the entire file into memory.

<cfloop index="line" file"file.txt" from="0" to="1">

                    #i#<br>

          </cfloop>

This doesn't seem to work.

[Reed_Powell-ttnmOb]

wrong CFLOOP syntax - you want FILE= attribute.  it will loop through the file one row one each pass, so if you exit the loop after the first row, all it will read is the first row.  It definitely does not read the entire file into memory.

[BKBK]

SGroty wrote:

I'm trying not to read the entire file into memory.

<cfloop index="line" file"file.txt" from="0" to="1">

                    #i#<br>

          </cfloop>

This doesn't seem to work.

You need the full path to the text file. You should also break out of the loop immediately after, as you only need the first line.

<cfloop index="line" file="#expandPath('.')#\file.txt">

<cfoutput>#line#</cfoutput>

<cfbreak>

</cfloop>

[Carl Von Stetten]

Is the file in a web-accessible location?  If so, a quick way is to use CFHTTP to read the CSV directly into a ColdFusion query object. Then you can use that query object to populate your temp table.  See Dave Fergusons's blog entry on this: http://blog.dkferguson.com/index.cfm/2011/9/28/CSV-File-Reading-using-cfhttp.

Otherwise, open the file using CFFILE and loop throught the contents (exiting after the first row if necessary).

HTH,

-Carl V.

[SGroty]

83,000 rows is too much to loop over.

That's why I want to grab the header row, dynamically build a SQL Statement to build a table(temporary one), then pass a BULK INSERT query to the SQL server to import the tab deliminited file.  Append the necessary data to the table, export it to the user, then DROP the table.