Need Utility to insert a CSV to ACCESS Table

[murpg]

Hi Guys, first of all thanks for your help. Can someone please help me find a
ColdFusion component or custom tag which will allow me to import a CSV file
into one of my tables. Does anyone have a script like this? If you don't have
a script then perhaps a premade utility which might work. I have looked at a
couple of things none of which seem to work. I bought the MX Collection
utility but I could never get it running properly.

[mxstu]

Is this a one time or recurring import? Is the file format fixed or do you need to import files with many different formats? Have you tried using CFQUERY with a schema file?

[murpg]

This import will happen quite a bit. The field names will always stay the same. I am not familiar with the using of the CFQUERY with a schema file. Can you please show me what you had in mind?

[PaulH]

have you seen [url]http://www.cflib.org/udf.cfm?ID=1236?[/url] are the csv files big? that could be a problem.

[mxstu]

You create a schema.ini file that the Access driver will use to interpret the
format of CSV file. Place the schema file in the same directory as the CSV
file. You can then use a regular cfquery to insert the data from the table.
Something like this

<cfquery name="insertData" datasource="myAccessDSN">
INSERT INTO myDestinationTable(FirstName,LastName,Address)
SELECT FirstName,LastName,Address
FROM [TEXT;DATABASE=c:\myCSVDirectory;].myAddresses.csv;
</cfquery>

[mxstu]

I haven't used schema files in a while, but they should be able to handle a
moderate amount of data pretty well. I've used it with 20K rows in the past.

Here are a few links on schema files:

[url]http://www.devx.com/tips/Tip/12566[/url]

[PaulH]

while i'm no big fan of access, that's a slick trick. thanks for teh tip.

[mxstu]

No problem. I'm not a fan either, but I had to use it one project and the schema file was a real life saver.