We help IT Professionals succeed at work.

Coldfusion Blank fields in tabbed txt file

Medium Priority
911 Views
Last Modified: 2013-12-20
I'm having issue with blank fields when trying to run an import script.  The application im building pulls data from a text filed that has about 20 columns and then attempts to insert the data in to the database.  I have a line of code to replace empty columns with the word NULL, but it doesn't seem to fix every blank field for some reason.  

I'm trying to figure out why all of the empty fields are not being replaced with the word NULL because my values are getting put in the wrong order.

You can see an example of the output here: http://209.164.170.8/admin/inventory/import_products3.cfm
<!--- read the txt file --->
	<cffile action="read" file="C:\inetpub\wwwroot\jayparrino\admin\inventory\products2.txt" variable="fileData">
	
	<table style="font:9pt arial;">
	<!--- start looping over the text file and assign columns to vars --->
	<cfloop index="record" list="#fileData#" delimiters="#chr(10)##chr(13)#">
		<cfif record NEQ 1 AND (record NEQ " " OR record NEQ "")>
			<!--- set empty fields to NULL --->
			<cfset record = ReplaceNoCase(record,"#chr(9)##chr(9)#", "#chr(9)#NULL#chr(9)#", "ALL")>
 
			<cfif isNumeric(left(getToken(record,1), 1))>
				<cfset tmpSKU = #GetToken(record,1,chr(9))#>
				<cfset tmpItemTitle = #GetToken(record,2,chr(9))#>	
				<cfset tmpScottText = #GetToken(record,3,chr(9))#>
				<cfset tmpHolder = #GetToken(record,4,chr(9))#>
				<cfset tmpMasterCat = #GetToken(record,5,chr(9))#>				
				<cfset tmpCategory = #GetToken(record,6,chr(9))#>				
				<cfset tmpDate = #GetToken(record,7,chr(9))#>				
				<cfset tmpDenomination = #GetToken(record,8,chr(9))#>		
				<cfset tmpColor = #GetToken(record,9,chr(9))#>		
				<cfset tmpGrade = #GetToken(record,10,chr(9))#>		
				<cfset tmpJumbo = #GetToken(record,11,chr(9))#>	
				<cfset tmpGum = #GetToken(record,12,chr(9))#>	
				<cfset tmpDescription = #Replace(GetToken(record,13,chr(9)), """", "", "ALL")#>
				<cfset tmpOnsite = #GetToken(record,14,chr(9))#>
				<cfset tmpStatus = #GetToken(record,15,chr(9))#>					
				<cfset tmpPrice = #Replace(Replace(Replace(GetToken(record,16,chr(9)), """", "", "ALL"), ",", "", "ALL"), "$", "", "ALL")#>
				<cfset tmpBundle = #GetToken(record,17,chr(9))#>
				<cfset tmpBundleParent = #GetToken(record,18,chr(9))#>								
				<cfset tmpRarestFinest = #GetToken(record,19,chr(9))#>
				<cfset tmpLegendaryRarities = #GetToken(record,20,chr(9))#>	
				<cfset tmpInvertedCenter = #GetToken(record,21,chr(9))#>
				<cfset tmpHighestGradedMint = #GetToken(record,22,chr(9))#>																
				<cfset tmpHighestGradedUsed = #GetToken(record,23,chr(9))#>		
					
				<tr>
					<td>|#tmpSKU#|</td>
					<td>|#tmpItemTitle#|</td>
					<td>|#tmpScottText#|</td>
					<td>|#tmpHolder#|</td>
					<td>|#tmpMasterCat#|</td>
					<td>|#tmpCategory#|</td>
					<td>|#tmpDate#|</td>
					<td>|#tmpDenomination#|</td>
					<td>|#tmpColor#|</td>
					<td>|#tmpGrade#|</td>
					<td>|#tmpJumbo#|</td>
					<td>|#tmpGum#|</td>
					<td>|#tmpOnsite#|</td>
					<td>|#tmpStatus#|</td>
					<td>|#tmpPrice#|</td>
					<td>|#tmpBundle#|</td>
					<td>|#tmpBundleParent#|</td>
					<td>|#tmpRarestFinest#|</td>
					<td>|#tmpLegendaryRarities#|</td>
					<td>|#tmpInvertedCenter#|</td>
					<td>|#tmpHighestGradedMint#|</td>
					<td>|#tmpHighestGradedUsed#|</td>			
				</tr>
			</cfif>
		</cfif>
		<cfflush>
	</cfloop>
	</table>
</cfoutput>

Open in new window

Comment
Watch Question

Instead of using GetToken, I'd treat each line from the file as a new list, with the tab as the delimiter.  Check if each item has length; if not it's an empty list item, set it to NULL.
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
> I'm having issue with blank fields when trying to run an import script.  The application
> im building pulls data from a text filed that has about 20 columns and then attempts to
> insert the data in to the database.

1) List functions are problematic when it comes to empty elements.  If you are using CF8, it is much better to use the listToArray function, which can preserve empty elements.  Then simply loop through the array to display the elements.  See attached example

2) Most databases have tools for importing from text files. The tools are generally faster than looping from CF.  For example, MS SQL has bulk insert, MySQL has the Load Data option, etc... So you might consider using those tools instead.

http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html


> but it doesn't seem to fix every blank field for some reason.  

IIRC, replace functions don't always catch consecutive instances of a string.  So you may have to run it multiple times.   But I would only recommend that if you are using MX7 or earlier.


<!--- 
This just simulates a tab delimited file, so you can test the code "as is"
--->
<cfsavecontent variable="fileData">
apples	oranges	pears	whatever
pears	oranges		whatever
apples	oranges	oranges	whatever
whatever		pears	whatever
apples	oranges	pears	apples
</cfsavecontent>
 
<cfoutput>
<!--- start looping over the text file and assign columns to vars --->
<cfloop index="record" list="#fileData#" delimiters="#chr(10)##chr(13)#">
	<cfset columns = listToArray(record, chr(9), true)>
	<!--- If this row contains the correct number of columns .. ---> 
	<!--- NOTE ::: Substitute "4" with however many columns there should be in each row of your file ---> 
    <cfif arrayLen(columns) gte 4>
		<!--- display the value for each column --->
		<cfloop array="#columns#" index="col">
			#col#||
		</cfloop>
		<!--- line break at the end of each row --->
		<br>
	</cfif>
</cfloop>
</cfoutput>

Open in new window

Author

Commented:
agx,

I am using Coldfusion 7.  Thanks for putting the code together, but I don't think it is what I'm looking for. The method your provided seems to just look past those rows and only displays the ones that are complete.   I need to import rows that have empty values in them.  

I'm still looking for a fix here if any one can help.

Thanks!

Commented:
Can you attache a sample of the .txt file?
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
> The method your provided seems to just look past those rows and only displays
> the ones that are complete.   I need to import rows that have empty values in them.  

No.  CF8's listToArray function preserves the empty values.  So say you have file with 4 columns, but some of them are empty items (like below).  CF would still detect 4 columns.

John[tab](empty)[tab](empty)[tab]123 Something

BUT, since you are using MX 7 you cannot use the code above. MX7 doesn't have the new listToArray  functionality.  So unfortunately, it won't work for you.


Author

Commented:
Here is a sample of the txt file.  You should be able to clearly see which lines have empty records in them.
products.txt
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
bkort1,

You might try this UDF.  Though I have not tried it, the UDF should support tab delimited files as well.  I will test it out with the sample you posted.

http://www.bennadel.com/blog/991-CSVToArray-ColdFusion-UDF-For-Parsing-CSV-Data-Files.htm
CERTIFIED EXPERT
Most Valuable Expert 2015
Commented:
bkort1:

I tried it and it works fine with CF8.  Here is the sample code used to test your file:

Example:
<cfset pathToFile = "C:\inetpub\wwwroot\jayparrino\admin\inventory\products2.txt">
<cfset fileArray = CSVToArray(File= pathToFile , Delimiter="#chr(9)#")>
<cfoutput>
<!--- display the number of columns detected and values in each row --->
<cfloop from="1" to="#arrayLen(fileArray)#" index="row">
        <cfset cols = fileArray[row]>
            <b>column count</b> #arrayLen(cols)#||
            <cfloop from="1" to="#arrayLen(cols)#" index="c">
                  #cols[c]#|
            </cfloop>
            <br>
</cfloop>
</cfoutput>

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
This will also work.
<cffile action="read" file="C:\inetpub\wwwroot\products.txt" variable="fileData">
<table border="1">
<cfloop index="record" list="#fileData#" delimiters="#chr(10)##chr(13)#">
	<cfif record NEQ 1 >
    <!--- set empty fields to NULL --->
    <cfset myArray = listToarray(record,chr(9),true) />
    <cfoutput>#arrayLen(myArray)#<br /></cfoutput>
  	<cfset record = replace(record,chr(9), "¿", "ALL")>
     <cfoutput>#listLen(record,"¿")#<br /></cfoutput>
     <tr>
     <cfloop list="#record#" delimiters="¿" index="i">
     	<td>
			<cfoutput>
				<cfif replace(i,"","") eq "">
                 	NULL
				<cfelse>
                   	#replace(i,"","")#
                </cfif>
			</cfoutput>
         </td>
      </cfloop>
      </tr>
	</cfif>
</cfloop>
</table>

Open in new window

Commented:
Well the cahrset screwed up replace the Box thing with pipe |
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Jones911:
>  <cfset myArray = listToarray(record,chr(9),true) />

I already posted a similar suggestion above.  But the OP is using MX7, so listToArray will not work for them.  The "includeEmptyElements" paramter was not introduced until CF8.


Commented:
Sorry that line is not needed it was for my own debugging I was simply using it to compare the results Plese see the below code.
<cffile action="read" file="C:\inetpub\wwwroot\products.txt" variable="fileData">
<table border="1">
<cfloop index="record" list="#fileData#" delimiters="#chr(10)##chr(13)#">
	<cfif record NEQ 1 >
    <!--- set empty fields to NULL --->
    <cfset record = replace(record,chr(9), "¿|", "ALL")>
     <tr>
     <cfloop list="#record#" delimiters="¿" index="i">
     	<td>
			<cfoutput>
				<cfif replace(i,"|","") eq "">
                 	NULL
				<cfelse>
                   	#replace(i,"|","")#
                </cfif>
			</cfoutput>
         </td>
      </cfloop>
      </tr>
	</cfif>
</cfloop>
</table>

Open in new window

Author

Commented:
Thanks everyone!  I finally have it working now.  I used what agx requested, but I had to modify it a bit.  I added my code below if anyone runs in to the same issue in the future.
<cfoutput>
<table style="font:9pt arial;">
	<tr>
		<td><b>SKU</b></td>
		<td><b>Title</b></td>
		<td><b>Scott</b></td>
		<td><b>Holder</b></td>
		<td><b>Master Cat.</b></td>
		<td><b>Category</b></td>
		<td><b>Date</b></td>
		<td><b>Denomination</b></td>
		<td><b>Color</b></td>
		<td><b>Grade</b></td>
		<td><b>Jumbo</b></td>
		<td><b>Gum</b></td>
		<td><b>Onsite</b></td>
		<td><b>Status</b></td>
		<td><b>Price</b></td>
		<td><b>Bundle</b></td>
		<td><b>Bundle Parent</b></td>
		<td><b>RarestFinest</b></td>
		<td><b>Legendary</b></td>
		<td><b>Inverted</b></td>
		<td><b>HGMint</b></td>
		<td><b>HGUsed</b></td>			
	</tr>		
	
	<cfset pathToFile = "C:\inetpub\wwwroot\jayparrino\admin\inventory\products2.txt">
	<cfset index = CSVToArray(File= pathToFile , Delimiter="#chr(9)#")>
	<!--- display the number of columns detected and values in each row --->
		<cfloop from="1" to="#arrayLen(index)#" index="c">
			<cfset tmpSKU = index[c][1]>
			<cfset tmpItemTitle = index[c][2]>	
			<cfset tmpScottText = index[c][3]>
			<cfset tmpHolder = index[c][4]>
			<cfset tmpMasterCat = index[c][5]>				
			<cfset tmpCategory = index[c][6]>				
			<cfset tmpDate = index[c][7]>				
			<cfset tmpDenomination = index[c][8]>		
			<cfset tmpColor = index[c][9]>		
			<cfset tmpGrade = index[c][10]>		
			<cfset tmpJumbo = index[c][11]>	
			<cfset tmpGum = index[c][12]>	
			<cfset tmpDescription = index[c][13]>
			<cfset tmpOnsite = index[c][14]>
			<cfset tmpStatus = index[c][15]>					
			<cfset tmpPrice = index[c][16]>
			<cfset tmpBundle = index[c][17]>
			<cfset tmpBundleParent = index[c][18]>								
			<cfset tmpRarestFinest = index[c][19]>
			<cfset tmpLegendaryRarities = index[c][20]>	
			<cfset tmpInvertedCenter = index[c][21]>
			<cfset tmpHighestGradedMint = index[c][22]>																
			<cfset tmpHighestGradedUsed = index[c][23]>		            	
	        	
			<tr>
				<td>|#tmpSKU#|</td>
				<td>|#tmpItemTitle#|</td>
				<td>|#tmpScottText#|</td>
				<td>|#tmpHolder#|</td>
				<td>|#tmpMasterCat#|</td>
				<td>|#tmpCategory#|</td>
				<td>|#tmpDate#|</td>
				<td>|#tmpDenomination#|</td>
				<td>|#tmpColor#|</td>
				<td>|#tmpGrade#|</td>
				<td>|#tmpJumbo#|</td>
				<td>|#tmpGum#|</td>
				<td>|#tmpOnsite#|</td>
				<td>|#tmpStatus#|</td>
				<td>|#tmpPrice#|</td>
				<td>|#tmpBundle#|</td>
				<td>|#tmpBundleParent#|</td>
				<td>|#tmpRarestFinest#|</td>
				<td>|#tmpLegendaryRarities#|</td>
				<td>|#tmpInvertedCenter#|</td>
				<td>|#tmpHighestGradedMint#|</td>
				<td>|#tmpHighestGradedUsed#|</td>			
			</tr>
		<cfflush>
	</cfloop>
</table>
</cfoutput>

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Ah, okay.  

The only thing I would add is handling for when the first or last element is empty.  Just so the script doesn't break.  Maybe something like:

<cfloop ...>
....
   <cfset record = replace(record, chr(9), "¿|", "ALL")>
   <cfif left(record, 1) EQ "¿">
       <cfset record = "|" & record>
  </cfif>
  <cfif right(record, 1) EQ "|">
      <cfset record = record & "¿|" >
  </cfif>      
   ....
</cfloop>



"I used what agx requested, but I had to modify it a bit."

You should award points to agx.
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
My last comment was about Jones911's code suggestion.

@bkort1 - Good to hear you solved it.  
bkort, sorry, I thought you weren't going to award points.  However it seems like you were going to split the points to agx anyway.  I think a moderator will review my objection, and I've asked that it be ignored, as only after objecting did I realise you were awarding agx the points.
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
@duncancumming & @bkort1 - Thanks guys  

Commented:
Ben's solution is nice they always are.  I guess if you didn't have access to java or wanted the same result in less code my solution with agx addition of skipping the first line would suit perfectly.  

Dan and agx will know that I always tend to skip the first line when we answer questions like this ha ha
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.