Extracting data from a text file (CFHTTP)

mike2277

unread,

May 12, 2003, 12:58:03 PM5/12/03

to

I am using ColdFusion MX and am having problems with my batch upload tool which used to work just fine in ColdFusion 5.0.

My text file (as it gets exported from MS Access) looks like this:

"FirstName","MiddleInitial","LastName"
"Matt","J","Jones"
"Steve",,"Smith"

Notice that the second record has no pair of double quotes because the MiddleInitial field was a null value for Steve Smith (he never entered his middle initial into the database and isn't required to).

My problem is that I'm trying to create a query object out of this text file using the following code and it won't work as it's coded:

My problem is that since I don't have double quotes as the text qualifier in Steve's middle initial field the page bombs out saying "Incorrect Number of Columns"...meaning it's not able to read the file correctly I assume.

If I do a find and replace to make sure that all fields have double quotes no matter if they are null or not then the page processes just fine. I can't ask my users to do this procedure though.

I've tried everything...textqualifier=","...textqualifier=" ".... stripping out all double quotes from the text file. The only way I can get it to work is if all fields have double quotes.

Please helpppp!

Mike

mike2277

unread,

May 12, 2003, 1:25:34 PM5/12/03

to

Hi Dan,
Thanks for the suggestion. The three fields I used were just a shortened version of my records - I've actually got about a dozen fields to process for each row. I would try this anyway but I don't think that I can use CFHTTP.fileContent to output my rows because I can't get CFHTTP to read the file in the first place. Is there some other way that you know of that I might be able to avoid using the CFHTTP code:

If so, I'm willing to try anything at this point. Also, I can follow your suggestion of not using quotes for the text qualifier. That shouldn't be a problem.

Mike

Dan-CFTagStore.com

unread,

May 12, 2003, 1:33:15 PM5/12/03

to

Hi Mike,

Why cant you use CFHTTP? If its because of the delimeter problem, you can still use it to just get the file contents (Which is what it does at its basic level). Take out any reference making it parse the file into a query, so you have:

Just retrive the file as text and work on parsing it that way using the method I suggested.

If on the other hand, the file is actually located on your server, just use CFFILE to read it into a variable and do the parsing then.

Dan.

Check Out : www.cftagstore.com for the latest and greatest Coldfusion custom tags and components!

Built by developers, for developers!

Claude

unread,

Aug 6, 2003, 7:58:52 PM8/6/03

to

This situation could easily be handled using http://www.contentbox.com/claude/tagstore.cfm?p=f
The following code should return a query with one third of a complete name in each row.
You could easily reconstitute names with 2 loops on the query.
<CF_REextract
INPUT="Your page address"
INPUTMODE="HTTP"
OUTPUTMODE="query"
RE1='"|,"?'
RE2='"|,'
>

Claude Schnéegans, CS. Internétique
------------------------
See some cool custom tags

______________________________________