This content has been marked as final. Show 3 replies
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.
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
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.
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.