Manipulating data in a text file

[mkyzar]

I'm back again with another question about this text file I've been working with (see: Illegal Operation in ColdFusion Extension). The problem I have now, of course, is the occasional occurrance of commas inside the data fields. The file that I will be using when this is put into use will not have quotes as text qualifiers like I had at first believed. I have no power over what format the file is sent in except that it will be in either comma-delimited format, separated by tabs or fixed-length. The problem with fixed-length is that I don't know how to specify the lengths of the fields in my code (can this be done?). The problem with comma-delimited is the occasional comma within a field, and, though I haven't tried it, the problem with separated by tabs will be the same as fixed-length. What I have been looking for is a way for ColdFusion to count the number of comma's in a line of text and if there are too many, delete a certain comma from the line. Is there a way to do this?

Thank you for your help.

mkyzar

 

[faust13]

What is the size of your text file. If it is less than a meg, you can asign it to a variable inside your CF page and manipulate it as any other string.

Yes, you can use fixed width files in CF, np. However, you must step through the file character by character. So it is very important that you have a mapped definition of your file (of course, you can always manually count).

If your file is too big, you still can use CF, but it is doggedly slow. I just finished writing a Java component to handle my large files. Time to process in CF 25.3 minutes. Time to process in Java 35 seconds.

A few options. ----------------------------------------
Is George Lucas Kidding...

 

[mkyzar]

It's a fairly small file. I've never done anything like this before so please bear with me. I have the file definition so if I use a fixed-length format how would I go about reading this file in and writing it to the Access database? Currently, I use cffile to read in the file and append the first record to the bottom so I won't lose it and then use cfhttp to get the data and specify the columns with "," as the delimiter. Could you point me in the right direction? Thanks for your help.

mkyzar

 

[faust13]

This fixed width example is close to what you need to do with delimited files, except you use listLen() instead of len().

Hope this helps!

Here's an Example of fixed width files:

<!--- first your need to read in your file into a variable called &quot;dump&quot;--->
<cffile action=&quot;READ&quot; file=&quot;c:\inetpub\ftproot\youTextFile.txt&quot; variable=&quot;dump&quot;>

<!--- find out how long dump is --->
<cfset length=len(dump)>

<!--- set your first position to 1 to start at the beginning of the file --->
<cfset columnPos=1>

<!--- divide the whole thing by your record length (this comes from your file definition map. This must be a whole number, if not, something is wrong. This gives you number of records in your file --->
<cfset loopMax = length/262>

<!--- loop through the file with your record max as the limiter --->
<cfloop from=&quot;1&quot; to=&quot;#loopMax#&quot; index=&quot;i&quot;>

<!--- set your temp var to what mid() returns --->
<cfset prodCD= trim(mid(Dump,columnPos,9))>

<!--- index your &quot;cursor&quot; in dump --->
<cfset ColumnPos=ColumnPos+9>

<!--- set your temp var to what mid() returns --->
<cfset brand1= trim(mid(Dump,columnPos,8))>

<!--- and again --->
<cfset ColumnPos = ColumnPos + 8>
<cfset slsGrp= trim(mid(Dump,columnPos,9))>

<!--- and again --->
<cfset ColumnPos = ColumnPos + 9>
<cfset prodGrp= trim(mid(Dump,columnPos,10))>

<!--- and again --->
<cfset ColumnPos = ColumnPos + 10>
<cfset dist= trim(mid(Dump,columnPos,7))>

<!--- you get the point? --->
<cfset ColumnPos = ColumnPos + 7>
<cfset acctYr= trim(mid(Dump,columnPos,10))>

<!--- do what you want with your data. In this example, I'm updating a db --->
<cftry>
<CFQUERY DATASOURCE=&quot;blah&quot;>
INSERT INTO yourTbl
VALUES (#yourValues#, ...);
</CFQUERY>

<!--- error handling --->
<cfcatch type=&quot;Any&quot;>
DB Update failed
</cfcatch>
</cftry>

<!--- index to the next record --->
<cfset ColumnPos = ColumnPos + 74>

<!--- do it again until you reach the end of the record --->
</cfloop> ----------------------------------------
Is George Lucas Kidding...

 

[mkyzar]

Thanks so much for your help. I figured out how to do it with the fixed-length file and it was so much easier than I thought. I always make things harder on myself than they have to be.

mkyzar