Import CSV File of unknown format

[joepeacock]

Hello all,

Here's my problem: I administer a website where users upload csv files and they are incorporated into a database. Of course, it's impossible to get all of the users to arrange their files correctly, so the files coming in may have any number of columns, with or without headers, with columns in any order. I currently analyze the files using Cold Fusion, identify relevant columns and do a line-by-line insert into a table on the SQL Server database, then compare that table to the main data tables to update the information appropriately. The problem with that is that some of the files uploaded have 100,000+ records, meaning the line-by-line insert can be slow.

What I'd like to do is just import the file into a new table, identify the relevant columns once it is imported, do a single query to copy the information into an existing table, and drop the new table. Of course, this is all automated, so I'd like to create a DTS package to do all of that.

The difficulty I'm having is that I can't seem to find a way to just import a file into a new table and have the file determine the table format. I either have to import to an existing table, or create a properly formatted table and then import. That won't work, since each file may have a unique format.

Is there a solution to this?

Thanks,
Joe

 

[nigelrivett]

Again why use dts?

Create a table with a single column. Import the file into it using bulk insert then use and SP to parse the table to get the structure. You might want to use a function to get the fields in the row but that might be a bit slow.

If you want to use dts you will probably have to write an activex script to open the file to get the structure and change the properties of the import task to get the correct structure.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[joepeacock]

Can you give an example of what that stored procedure would look like?

 

[MDXer]

If your doing it in cold fusion you could have a form with a template where they would input column names and delimeters. This form you could have create a format file which you would then use during the bcp of your data.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[joepeacock]

That would be nice, but they wouldn't do it. They just FTP their files every few days or so and expect it to be taken care of from there.