Reading, Modifying, Writing CSV files

[Guest]

Hi,

I'll be reading .csv files. A small file could be 20 columns and around 1000 rows. A medium sized file would be 20 columns and around 5000 rows. Basically I need to read the column headings, and possibly change the value. Then on a row by row basis, I need to examine the value, and make sure it meets specific criteria. Once the row field has been manipulated, I need to write it back to the file. Ultimately, I'm going to upload the file to a database.

I'm seeing and reading about lots of different tags and possible ways to do this. <cfhttp> <cffile read/write>. I'd appreciate any thoughts on how to do this effectively. I'm using CF8.

Here is some sample data:

UPC, Brand, Prod_Description
55555-10111, Kraft, Kraft Mac&Cheese 6oz
55555-10117, Kraft, Kraft Spirals with Cheese 7oz

I'd like to change Prod_Description to Description
I'd like to remove the hypens in the upc numbers to that they look like 5555510111.

Any thoughts on a "stable" approach would be appreciated. If the size of the file went up to 50,000 rows, would this still work? If not, is there a different approach that could handle it?

Thanks!!

cfwild

[Guest]

A quick and dirty approach would be to read the file using CFFILE, then use the Replace( ) function to change Prod_Description to Description. If the only place you have hyphens is in the UPC column, then use the Replace( ) function to remove the hyphens. When your finished, use CFFILE to write the file back to disk. This approach should also work for 50,000 rows.

[Newsgroup_User]

There are a few options you have here.

1) Read the file with <cffile...> or <cfhttp...>. Modify the data then
rewrite the data. The main issue with this is that all the data must be
read into memory at once and manipulated there then written back out to
the file system.

2) Tap the underlining Java IO objects. This allows you to parse the
files in smaller bites. But I don't know about writing back to the
file. You may need to create a new file as you are parsing the old one.
But I do not know that much about the full capabilities of the Java IO
API.

3) Create an 'Excel' DSN. At least some ColdFusion versions have a
'database' driver that can connect to an Excel/CSV file and use it as a
data source for SQL operations. I have never actually done this so
again I do not know the full capabilities of the driver.

4) Import the data into a RDMS first, as you plan to do later, and then
modify the data. Thus allowing you the full capabilities of a database
management system. Which is almost always going to out preform anything
file based such as the first three options.

[Stressed_Simon]

Why don't you import it into the database as a temporary table and make the changes there? This is what a database is for and will absolutely batter any solution using CF on performance and scalability.