Reading Text Files, and inserting its contents into a table 1

[powahusr]

I would like to read data from a text file and insert the text into a database.

I created a text file from an Excel spreadsheet and saved it as a comma delimited text file (.csv).

I tried using the cffile tag along with a cfloop, but the cffile tag just does not seem to work with this kind of procedure. It does not appear as if the tag can distinguish if there are columns and rows, or assign variables to each individual column. I just want to be able to have the construct, loop through the text file 1 row at a time and insert each column value into it’s respective column in a SQL database table.

Does anyone know how to do this?

Thanks in Advance!!!

 

[Sarky78]

in v4 of CF i used a cfquery to do this. you can setup a ODBC connection to the text file from the administrator and then just query it like you would with a database.

If you are using MX then you will have to create a DSN in the windows ODBC creator and then use ODBC Socket from the CF admin to point to the DSN connector.

The way that it works is that the ODBC connection points to a directory on your server, then when you query the connection you say which file it is you want to look at.

So if you had a file called data.csv in your c:\ you would point the odbc connection at your c:\ and give it a name - say csvdata

then in your CF code you would ahve the following:

<CFQUERY DATASOURCE=&quot;csvdata&quot; NAME=&quot;getData&quot;>
SELECT *
FROM data.csv
</CFQUERY>

and then output in the usual manner !

hope this helps !

 

[csteinhilber]

I'm guessing that the reason your original solution (CFFILE and CFLOOP) isn't working is because you haven't gotten the delimiters quite right.

I've used CFFILE and CFLOOP for this type of functionality quite often, and it works quite nicely.

Usually, when you save an Excel spreadsheet as a .CSV, the individual rows are separated by a carriage-return/linefeed combination (CRLF). So this is what you need to specify as the &quot;delimiter&quot; for your main CFLOOP.

  <!--- set up the CRLF character, since it's
        actually two characters masquerading as one --->
  <CFSET myCRLF = Chr(13) & Chr(10)>

  <!--- read the CSV file and stuff it into a variable --->
  <cffile action=&quot;READ&quot; fileBook1.csv&quot; variable=&quot;mybook&quot;>

  <!--- loop through each &quot;line&quot; of the variable by
        specifying our CRLF character as the delimiter --->
  <cfloop index=&quot;whichLine&quot; list=&quot;#mybook#&quot; delimiters=&quot;#myCRLF#&quot;>

     <!--- now stuff the individual columns of each
           row into your database --->
     <CFSET firstColumn = ListGetAt(&quot;#whichLine#&quot;,1)>
     <CFSET secondColumn = ListGetAt(&quot;#whichLine#&quot;,2)>
     <CFSET thirdColumn = ListGetAt(&quot;#whichLine#&quot;,3)>

     <CFQUERY name=&quot;insertData&quot; ...>
        INSERT INTO tbl_name (col1, col2,col3) VALUES (#firstColumn#,#secondColumn#,#thirdColumn#)
     </CFQUERY>
   </cfloop>

An ODBC connection, as arperry suggests, is really the better solution, though, if you have admin access to be able to set up DSNs.

A complete tutorial is available here:

http://www.cfcomet.com/Excel/index.cfm?ArticleID=0239B2E5-5FED-11D3-B3E9004033E03EF9

or there are several others that you'd be able to find with a web search. Hope it helps,
-Carl

 

[powahusr]

Thanks for you replies, however 2 errors have occurred, 1 for each solution.

I apologize for not letting you know what I am running here.

* I am using ColdFusion Studio 5 w/Server (5)

* I am using SQL Server 2000 as my database as the destination for the data in my text file.

* My OS is Windows 2000 Professional

========================================
Query Solution:

I setup the ODBC connection as specified, but when I am inside of CF Studio and I click on the table under my newly created data source name while in the Database Resource Tab, the following error message appears:

The Table Fields could not be retrieved do to an error:
Error –1 occurred: ODBC Error Code = S1C00 (Driver Not Capable)
[Microsoft] [ODBC Text Driver] Optional Feature Not Implemented

I also setup the ODBC connection from both Windows (Admin) and ColdFusion (Server) to see if that had anything to do with the problem, but the error resulted for each method. I removed the existing connection before I attempted the alternative method of setting up the connection.

========================================
##########################################
========================================
CFLOOP & CFFILE Solution:

The solution worked just fine, however there are 4 columns of text in my text file. I tried tweaking your snippet to suit the 4 columns where as your snippet was setup for 3 and got the following error message:

Error Diagnostic Information

An error occurred while evaluating the expression:


fourthColumn = ListGetAt(&quot;#whichLine#&quot;,4)


Error near line 27, column 9.
--------------------------------------------------------------------------------

In function ListGetAt(list, index [, delimiters]) the value of index, which is 4, is not a valid index for the list given as a the first argument (this list has 3 elements). Valid indexes are in the range 1 through the number of elements in the list


My Script looks like this:

<CFSET myCRLF = Chr(13) & Chr(10)>

<cffile action=&quot;READ&quot; file=&quot;C:\Inetpub\

http://wwwroot\Test\mybook\foo.csv&quot;

variable=&quot;mybook&quot;>

<cfloop index=&quot;whichLine&quot; list=&quot;#mybook#&quot; delimiters=&quot;#myCRLF#&quot;>
<CFSET firstColumn = ListGetAt(&quot;#whichLine#&quot;,1)>
<CFSET secondColumn = ListGetAt(&quot;#whichLine#&quot;,2)>
<CFSET thirdColumn = ListGetAt(&quot;#whichLine#&quot;,3)>
<CFSET fourthColumn = ListGetAt(&quot;#whichLine#&quot;,4)>

<CFQUERY NAME=&quot;insertData&quot; DATASOURCE=&quot;MyDsn&quot;>
INSERT INTO tblTestFoo (col1, col2, col3, col4)
VALUES ('#firstColumn#', '#secondColumn#', '#thirdColumn#', '#fourthColumn#')
</CFQUERY>
</cfloop>

========================================

Any Suggestions?

Thanks Again

 

[powahusr]

I figured out the Query Solution issue, so please disregard my response. thanks arperry!

As for the &quot;CFLOOP & CFFILE Solution&quot;, if you can me help me out, I will be all set.

Thanks for your help, Tek-Tips are the best!!!

 

[csteinhilber]

Yes, this is a common problem... because ColdFusion lists ignore null elements. In other words, if you had a row in your Excel spreadsheet that was:

  Column 1  |  Column 2  |  Column 3  |  Column 4  
  ===============================================
    Hello   |  Goodbye   |            |   Aloha

(column 3 is empty)

This would save out to a .CSV file as:

Hello,Goodbye,,Aloha

which, because of the null value (the two ,, next to each other), ColdFusion would see as only 3 elements.

Personally, I think this is a poor choice they made with regards to functionality in ColdFusion lists... as it never seems to be how the real world wants to view that same list (at least in my experience).

The only way I've been able to work around the issue is to implicitly stuff something into that empty element. It's not difficult to do, but it's stupid that I have to (again, IMHO).

Try

  <!--- set up the CRLF character, since it's
        actually two characters masquerading as one --->
  <CFSET myCRLF = Chr(13) & Chr(10)>

  <!--- read the CSV file and stuff it into a variable --->
  <cffile action=&quot;READ&quot; fileBook1.csv&quot; variable=&quot;mybook&quot;>

  <!--- replace all the empty elements with &quot;n/a&quot; --->
  <cfset mybook = Replace(mybook,&quot;,,&quot;,&quot;,n/a,&quot;,&quot;ALL&quot;)>
  <cfset mybook = Replace(mybook,&quot;,#myCRLF#&quot;,&quot;,n/a#myCRLF#&quot;,&quot;ALL&quot;)>

  <!--- loop through each &quot;line&quot; of the variable by
        specifying our CRLF character as the delimiter --->
  <cfloop index=&quot;whichLine&quot; list=&quot;#mybook#&quot; delimiters=&quot;#myCRLF#&quot;>

     <!--- now stuff the individual columns of each
           row into your database --->
     <CFSET firstColumn = ListGetAt(&quot;#whichLine#&quot;,1)>
     <CFSET secondColumn = ListGetAt(&quot;#whichLine#&quot;,2)>
     <CFSET thirdColumn = ListGetAt(&quot;#whichLine#&quot;,3)>
     <CFSET forthColumn = ListGetAt(&quot;#whichLine#&quot;,4)>


     <CFQUERY name=&quot;insertData&quot; ...>
        INSERT INTO tbl_name (col1, col2,col3,col4) VALUES (#firstColumn#,#secondColumn#,#thirdColumn#,#forthColumn#)
     </CFQUERY>
   </cfloop>

Should do it. You may also want to add a check of the ListLen before you do the ListGetAt's... just in case something got screwy somewhere else:

  <cfloop index=&quot;whichLine&quot; list=&quot;#mybook#&quot; delimiters=&quot;#myCRLF#&quot;>

     <cfif ListLen(&quot;#whichLine#&quot;) GT 3>

        <!--- now stuff the individual columns of each
              row into your database --->
        <CFSET firstColumn = ListGetAt(&quot;#whichLine#&quot;,1)>
        <CFSET secondColumn = ListGetAt(&quot;#whichLine#&quot;,2)>
        <CFSET thirdColumn = ListGetAt(&quot;#whichLine#&quot;,3)>
        <CFSET forthColumn = ListGetAt(&quot;#whichLine#&quot;,4)>


        <CFQUERY name=&quot;insertData&quot; ...>
           INSERT INTO tbl_name (col1, col2,col3,col4) VALUES (#firstColumn#,#secondColumn#,#thirdColumn#,#forthColumn#)
        </CFQUERY>

</cfif>

   </cfloop>

Hope it helps,
-Carl

 

[powahusr]

Thanks csteinhilber!