Extracting data from a text file (CFHTTP)

[mike2277]

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:

<CFHTTP
DELIMITER=&quot;,&quot;
TEXTQUALIFIER=&quot;&quot;&quot;&quot;
METHOD=&quot;Get&quot;
URL=&quot;

http://#THISProviderURL#/providers/#Session.Auth.ProviderID#/batch/#BATCHFILENAME#&quot;

NAME=&quot;GetUsersViaHTTP&quot;>

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 &quot;Incorrect Number of Columns&quot;...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=&quot;,&quot;...textqualifier=&quot; &quot;.... 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

 

[diggy8]

Could you insert the double quotes, even for a NULL value, so make Steve be &quot;Steve&quot;,&quot;&quot;,&quot;Smith&quot;?

 

[SidianConsulting]

You might find this link helpful:

http://forums.belution.com/en/access/000/000/77.shtml

Also, if the above &quot;just used to work fine in ColdFusion 5.0&quot;, then it had nothing to do with that version. If you are exporting via Access' export feature, this is a problem within Access. I tried to export a table from one of my databases and noticed the preview shows NULLs double-quoted, but the text file that is generated does not have them.

The above link will only be helpful if you are not exporting using Access' export feature; only if you are writing the SQL yourself and writing the result set to a file. This seems like the only workaround.

-Tek