1 Reply Latest reply on Aug 30, 2006 8:48 AM by @shortfused

    Handling empty values in tab delimited file.

    Lumpia Level 1
      Developers,

      In regards to the same question asked by rowbeast here:
      http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_21742253.html?qid=21742253 , the accepted answer states to use the ListFix function from: http://www.cflib.org/library.cfm?ID=1 .

      My question is, where do you append this ListFix function within your code? I have downloaded the function and am calling it, but do not know how to insert it on top of a #listgetat# function.

      Here is my code:

      <cfinclude template="ListFix.cfm">

      <!--- get and read the CSV-TXT file --->
      <cffile action="read" file="csvdemo.txt" variable="csvfile">

      <!--- loop through the CSV-TXT file on line breaks and insert into database --->
      <cfloop index="index" list="#csvfile#" delimiters="#chr(10)##chr(13)#">
      <cfquery name="importcsv" datasource="#database#" username="#dbid#" password="#dbpass#">
      INSERT INTO csvdemo
      (COLOR,
      SIZE,
      INSTOCK,
      STYLE)
      VALUES
      ('#listgetAt('#index#',1, chr(9))#',
      '#listgetAt('#index#',2, chr(9))#',
      '#listgetAt('#index#',3, chr(9))#',
      '#listgetAt('#index#',4, chr(9))#'
      )
      </cfquery>
      </cfloop>

      <!--- use a simple database query to check the results of the import - dumping query to screen --->
      <cfquery name="rscsvdemo" datasource="#database#" username="#dbid#" password="#dbpass#">
      SELECT * FROM csvdemo
      </cfquery>
      <cfdump var="#rscsvdemo#">


      I have tried multiple tests such as throwing the #listfix# function in front of the #listgetat# like this:

      '#listfix(listgetAt('#index#',4, chr(9)))#'

      but it doesn't work. Please advise.

      Also, kinda unrelated to the listfix function, I heard you could use ISNULL function as seen here:
      http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_20546746.html

      but I cannot get it to work either with my code. I've used it like so:

      'ISNULL(#listgetAt('#index#',4, chr(9))#,'')'

      Any and all help would be greatly appreciated.