Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 858
  • Last Modified:

Coldfusion Blank fields in tabbed txt file

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

0
bkort1
Asked:
bkort1
  • 8
  • 5
  • 3
  • +1
1 Solution
 
duncancummingCommented:
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.
0
 
_agx_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

0
 
bkort1Author 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!
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Jones911Commented:
Can you attache a sample of the .txt file?
0
 
_agx_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.


0
 
bkort1Author 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
0
 
_agx_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
0
 
_agx_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>
0
 
Jones911Commented:
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

0
 
Jones911Commented:
Well the cahrset screwed up replace the Box thing with pipe |
0
 
_agx_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.


0
 
Jones911Commented:
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

0
 
bkort1Author 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

0
 
_agx_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>



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

You should award points to agx.
0
 
_agx_Commented:
My last comment was about Jones911's code suggestion.

@bkort1 - Good to hear you solved it.  
0
 
duncancummingCommented:
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.
0
 
_agx_Commented:
@duncancumming & @bkort1 - Thanks guys  
0
 
Jones911Commented:
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
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 8
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now