Datasource CSV File

[varnix]

My company is fairly new to Coldfusion, but we are quite intrigued by the functionality it will offer us.

One of our planned implementations is to link it to a file that is generated from one of our AIX systems. The file is generated in a CSV format, unfortunately that is the only format the system can generate to.

We *could* import the file into access and convert it, but we want to automate this as much as possible by having the AIX box automatically FTP the updated files on a weekly basis to our coldfusion server box.

The question is this - how do we reference the data contained within the CSV file from CF?

For example, the file that we are looking at contains the following header:
enum,lastname,midinitial,firstname,deptcode

Where enum is the employee number, etc, etc.

If I wish to pull data from the CSV based on the deptcode, how do I specify to the CF code which column the data is? With an access DB, we could use the "where" statement of course, but we're just not sure how to proceed using a CSV file.

Any help will be greatly appreciated!

Thanks!

 

[tlhawkins]

hey varnix,

working with CSV files can be a little tricky but certainly not impossible. There is an ODBC driver for .txt and *.csv files but I haven't used it.

What I would do is load the file with:
<cffile Action=&quot;read&quot;
File=&quot;C:\temp\test.csv&quot;
Variable=&quot;TestCSV&quot;
>

then loop through it as a list. In this case it will be accessible as #File.TestCSV#

play with that and see if it works for you.

Let me know if you need help with the details of loading and looping through a CSV file. Sometimes there are problems with the text contained especially if it includes carriage returns.

Hope it works out for you.