Convert text file to recordset

Forum|Forum|16 years ago
December 17, 2009
2 replies
1298 views

We've got a web server where text files are uploaded by the outside world, and I need to get the data in those files into my SQL tables. Can I code ColdFusion to convert the text files (which are on the same server as the web pages) to recordsets so I can have a SQL stored procedure import the data to my SQL tables?

Database access

This topic has been closed for replies.

Correct answer ilssac

YES....

I hope you are not asking here for somebody to do it for you, but I will point out that the two tags you probably want to look at are the <cffile....> tag that would allow you to read it and parse it any way you care to. And the <cfhttp....> tag, that may be able to parse the file into a recordset automatically, if it is well structured.

D

Dan_Bracuk

Inspiring

Using coldfusion to upload text files to databases is generally a last resort. Many dbs have other utilities that do this better.

If you must use coldfusion, reading the file with cfhttp instead of cffile gives you a cold fusion query object. If you read it with cffile, you have a list delimited by chr(10) & chr(13) that you can loop through. I don't think either would be accessible by a stored procedure in your db.

A

Anonymous

Thanks Dan. We actually had considered using an ftp server to move the files inside the firewall and then use code there to import the data, however our IT department is giving us fits regarding security and opening ports. We currently use stored procedures to move data from form fields on the web pages to the SQL tables, and we thought it would be much easier to move the data from the text files that way as well, if we could just get the data from a text file to something we could pass via stored procedure.

ilssac

Inspiring

We just won this very argument with our operations folks. We developed a proff of concept applicaiton where a text file is uploaded to our exteranl, ColdFusion web server. The CFML page that handles this then uses the <cfhttp...> tag to re-post the file to an internal ColdFusion webserver through the filewall. Then the internal servers' CFML page can process the data to any inside location we care to, databases, file servers, etc.

Thus the firewall rule is restricted to only accept communication between the external web server IP and the internal web server IP through a non-standard port number. Thus it is a very small hole. And the internal web server is configured to only respond to requests from the external web server IP address.

But it took a couple of years to get this worked through the powers that be in our organization.

ilssacCorrect answer

Inspiring

YES....

I hope you are not asking here for somebody to do it for you, but I will point out that the two tags you probably want to look at are the <cffile....> tag that would allow you to read it and parse it any way you care to. And the <cfhttp....> tag, that may be able to parse the file into a recordset automatically, if it is well structured.

A

Anonymous

HaHa... Thanks Ian - no I don't expect anyone to do it for me, I just needed some guidance. Upon investigation, it looks like the <cffile> tag is the one I need, specifically with action="Read". It looks like this will store the contents in a variable that I can then pass to my SQL server via a stored procedure. I actually use the <cffile> tag to upload the files, I just didn't think to use it to parse them as well.

Thanks again.

ilssac

Inspiring

It can write, copy, move and append to files as well. A pretty good tag all around.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded