Import dynamic file

[Kalin]

Hi,

I want to import a CSV-file into SQL-Server. The column headers for the columns are on the first line. The file contains approx. 500000 records.
So far no problem....

But the columns can vary both in amount and composition.

So I need to dynamicly create a table based on the file and the import the records.

Can anyone help me as how to do this ??

Grtz,

Kalin

 

[dbomrrsm]

when you say the columns can vary in amount and composition do you mean from one file to the next it can have more/less columns ? and the composition bit do you mean that the data in the columns is unknown in type and length ?

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[Kalin]

Well,

the files are imported once a month. Each month each file consists of approx. 10 set columns followed by a number (which can vary for month to month) of variable columns.

Example:

In January we get a total of 10 columns:
Col A; Col B; Col C; Col D; Col E; Col F; Col G;etc

In February we get a total of 15 columns:
Col A; Col C; Col E; Col D; Col T; Col U; Col V;etc

In January Column T, U and V aren't in the file...

The datatype of the columns stays the same.

Hope this clarifies some questions..

Grtz,

Kalin

 

[Catadmin]

If this has to be a regularly scheduled DTS package, then you might try the "Drop and Recreate table" option. I haven't tried this myself, so I'm not sure if it'll work.

If it doesn't have to be regularly scheduled, then there really isn't a problem. Import the CSV file manually each time with a slightly different table name.

Otherwise, dropping the table manually and having the DTS job Create Destination Table is a third option.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[Kalin]

Where can I find the drop and recreate table option is it part of a sql-statement or task.



Grtz,

Kalin

 

[Catadmin]

The drop & Recreate table option is available when using the DTS Export/Import Wizard. When you get to the part when it says what tables are going where, click on the ellipses (...) box to the right of the destination table. It gives you five options up above where you determine datatype conversions. Create Destination table has to be checked to get to the drop & recreate table. Then there is Append, Delete, and Insert Identity values.

I'm new to the DTS Designer, so I'm not quite sure where you can find it there, but I would assume it is part of the workflow properties of the arrow between your connections.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!