Skip to main content
S
SGroty
Known Participant
July 30, 2013
Question

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

  • July 30, 2013
  • 2 replies
  • 1892 views

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

This topic has been closed for replies.

2 replies

Carl Von Stetten
Carl Von Stetten
Brainiac
July 30, 2013

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.

S
SGrotyAuthor
Known Participant
July 30, 2013

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.

R
Reed_Powell-ttnmOb
Inspiring
July 30, 2013

Use CFLOOP and exit after the first row.

S
SGrotyAuthor
Known Participant
July 30, 2013

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.

R
Reed_Powell-ttnmOb
Inspiring
July 30, 2013

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.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices