4 Replies Latest reply on Oct 4, 2006 1:04 PM by rokit1313

    Reading Tab Delimited File

      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.
        • 1. Re: Reading Tab Delimited File
          Level 7
          Sure, how are you reading the files currently?
          • 2. Re: Reading Tab Delimited File
            rokit1313 Level 1
            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)

            <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))#">


            • 3. Re: Reading Tab Delimited File
              Level 7
              <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.
              • 4. Re: Reading Tab Delimited File
                rokit1313 Level 1
                you are correct, that is the section that throws the error. I will give your idea try. thanks for the help