Reading Tab Delimited File

[rokit1313]

I have an excel file that i export to a tabbed text file and import it into a database using Coldfusion. The page reads the tabbed text file then loops through the tabbed list.

My problem is if the excel file has blank cells the coldfusion list will not import into the database. If i fill the empty cells with "Unknown" (or anythign else as long as it isn't empty) it will work just fine. I want to be able to do this without having to fill all empty cells with "Unknown". Is there anything i can do?

Any help would be apprecaited.

[Newsgroup_User]

Sure, how are you reading the files currently?

[rokit1313]

Thanks for replying.

Here is what i have (i have removed personal details):


<!--- get and read the TXT file --->
<CFFILE ACTION="read" FILE="E:\myserver\file.txt" VARIABLE="tabfile">

<!--- loop through the TXT file on line breaks and insert into database --->
<CFLOOP INDEX="index" LIST="#tabfile#" DELIMITERS="#chr(10)##chr(13)#">

<CFQUERY NAME="importcsv" DATASOURCE="mydb">
INSERT INTO databasename (column1,column2,column3,column4)
VALUES

(
<CFQUERYPARAM VALUE="#listgetat(index,1,chr(9))#">,
<CFQUERYPARAM VALUE="#listgetat(index,2,chr(9))#">,
<CFQUERYPARAM VALUE="#listgetat(index,3,chr(9))#">,
<CFQUERYPARAM VALUE="#listgetat(index,4,chr(9))#">
)

</CFQUERY>

</CFLOOP>

[Newsgroup_User]

<CFQUERYPARAM VALUE="#listgetat(index,1,chr(9))#">,
<CFQUERYPARAM VALUE="#listgetat(index,2,chr(9))#">,
<CFQUERYPARAM VALUE="#listgetat(index,3,chr(9))#">,
<CFQUERYPARAM VALUE="#listgetat(index,4,chr(9))#">

I presume this section is throwing errors when a row does contain data
in all the "cells". There are many ways to handle this, www.udflib.org
has UDF built to handle this.

The first one that pooped in my head was an automatic version of your
manual solution. After you assign each line to the index var,
programmaticly add something between empty tab characters.

<cfset index = rereplace(index,chr(9)& chr(9),chr(9) & "|" & chr(9),"ALL")>

You will then probably want to do something so that you don't insert
pipe | characters into your database.

[rokit1313]

you are correct, that is the section that throws the error. I will give your idea try. thanks for the help