Losing Columns when Replacing NULL values in Tab Delimited File - Coldfusion

Question

I have a Tab Delimited Text file that contains the dreaded "NULL" value. And after some searching I found how to replace the Empty string... with "something". But when I do this I seem to be losing Half of my values.

<cfset thisRow = replace(thisRow, "#chr(9)##chr(9)#", "#chr(9)#ScoobySnack#chr(9)#", "all")>

For example: One row of data contains 120 "Empty Tabs" - but when I do this workaround - I only return 60. Snippet is here:

    <cffile action="read" file="c:\websites\the website\UsageData\#MyFile.NAME#" variable="myDataFile">

  <cfset rowCount = listLen(myDataFile,chr(13))>


  -Other stuff happens-


  <cfloop from="1" to="#rowCount#" index="i">
  <table cellpadding="2" cellspacing="0" border="1">
  <tr>
  <th>ID</th>
  <th>Date</th>
  <th>Time</th>
  <th>DeviceMac</th>
  <th>DeviceAddress</th>
  <th>DeviceName</th>
  <th>UsageBytes</th>
  </tr>
  <!--- set current row to simple variable --->
  <cfset thisRow = listGetAt(myDataFile,i,chr(13))>

  <cfset Field1 = listGetAt(thisRow,1,chr(9))>
  <cfset Field2 = listGetAt(thisRow,2,chr(9))> 
  <cfset Field3 = listGetAt(thisRow,3,chr(9))>


  <cfset thisRow = replace(thisRow, "#chr(9)##chr(9)#", "#chr(9)#ScoobySnack#chr(9)#", "all")>

  <cfset variables.CheckColumnCount = listLen(thisRow,chr(9))>

  <cfif variables.CheckColumnCount NEQ variables.ColumnCount>
  <cfset variables.AdjustedColumnCount = (variables.CheckColumnCount - 3)>
  <cfelse>
  <cfset variables.AdjustedColumnCount = (variables.ColumnCount - 3)>
  </cfif>

  <!---Row #i# Data as a string (#thisRow#)<br/>--->
  <cfset ColNum = 4>
  <!--- now loop the row --->
  <cfloop from="1" to="#variables.AdjustedColumnCount#" index="r">
  <cfif r EQ 1>
  <cfset variables.MyLoopStartTime = variables.MyStartTime>
  <cfelse>

  </cfif>

  <tr>
  <td>#ColNum#</td>
  <td>#variables.thisDate#</td>
  <td>#TimeFormat(variables.MyLoopStartTime,"hh:mm:ss")#</td>
  <td>#Field1#</td>
  <td>#Field2#</td>
  <td>#Field3#</td>
  <td>#listGetAt(thisRow,ColNum,chr(9))#</td>
  </tr>
  <cfset variables.MyLoopStartTime = DateAdd('n', 1, variables.MyLoopStartTime)>
  <cfset ColNum = ColNum +1>
  </cfloop>
  </table>
  <hr>
  <cfset count = count + 1>
  </cfloop>

Thoughts?

(Edit) If you must roll-your-own, do not use `replace()`. It is simpler to use arrays, specifically [`listToArray(..)` with `includeEmptyFields=true`](http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7f0f.html). That said, there are [better tools for this job](http://stackoverflow.com/a/18026854/104223). Ones specifically designed for parsing delimited files. — Leigh, Sep 16 '13 at 18:58
Thanks Leigh - so then the answer to my question would be: `` — Big Fat Designs, Sep 16 '13 at 21:19
Not quite. If you are going to switch to arrays, get rid of all the list functions. You only need to convert it to an array *once*. Then everywhere you are using lists, use the array instead. Instead of `listGetAt( yourList, x, delim )`, use `yourArray[ x ]`, instead of `listLen( yourList, delim )`, use `arrayLen( yourArray) `, etcetera. — Leigh, Sep 16 '13 at 21:31
Well - Truth be told - Arrays are not my strong suit. When I convert my file to an array - it doesn't separate the rows... It all becomes one big column. (With 37000 rows...) But my code above did convert a row to an array AND kept the empty fields... (Which was great) But I get that I was mixing.. just wasn't sure how to keep the rows in the original file. — Big Fat Designs, Sep 17 '13 at 03:19

score 1 · Accepted Answer · edited May 23 '17 at 11:50

(From the comments ..)

There are better tools for the job: ones specifically designed for parsing delimited files. However, to answer your question, if you must roll-your-own, do not use replace(). It is simpler to use arrays, specifically listToArray() with includeEmptyFields=true.

Read in the file, and split the content on CR to create an array of rows:

<!--- split content into an array of rows --->
<cfset rowArray  = listToArray(myDataFile, chr(13), true)>
<cfset rowCount  = arrayLen(rowArray)>

Then loop through the array, and create a separate array of columns from each row:

<cfloop from="1" to="#rowCount#" index="rowIndex">
    <!--- split current row into an array of columns --->
    <cfset thisRow  = rowArray[ rowIndex ]>
    <cfset colArray = listToArray(thisRow , chr(9), true)>

    <!--- get first 3 columns (for illustration) --->   
    <cfset Field1 = colArray[ 1 ]>
    <cfset Field2 = colArray[ 2 ]> 
    <cfset Field3 = colArray[ 3 ])>
    ...

</cfloop>

If you do not need the loop index for other things, you could also use an array loop instead:

<!--- split content into an array of rows --->
<cfset rowArray  = listToArray(myDataFile, chr(13), true)>
<cfloop array="#rowArray#" index="thisRow">
    <!--- split current row into an array of columns --->
    <cfset columns = listToArray(thisRow, chr(9), true)>

    <!--- get first N columns for illustration ---> 
    <cfset Field1 = columns [ 1 ]>
    <cfset Field2 = columns [ 2 ]> 
    <cfset Field3 = columns [ 3 ])>
    ...
</cfloop>

Losing Columns when Replacing NULL values in Tab Delimited File - Coldfusion

1 Answers1