Reading text file names and importing from Them

[TonyKhela]

I had a SQL coding question and wondered if anyone can help.

We have hundreds of csv files in a folder with names like “CP1.txt”, “CP2.txt”, “CP3.txt” etc.

The files represent code tables and each contains just two “|” separated columns like:

1|Person
2|Marked for deletion
3|Use instead

I wanted to import these into a SQL table with the following structure:

Code Table, (this would contain values like CP1, CP2, CP3 representing the csv filenames the codes came from)
Code (this would contain values from the first column of the csv file)
Description (this would contain values from the second column of the csv file)

Would anyone know how to do this using a stored Procedure?
Any help would be appreciated. Thanks.

Regards
TK

 

[mkal]

The easiest way to do this is to use Data Transformation Services (DTS). It's a fairly straight forward process where you create a source (your csv files) and a destination (your Sql Server) and then create a Datapump task between them.

If you've never used DTS then you could also use the bcp (Bulk Copy Program). All our documented in Books Online as to how to use them.

 

[Crowley16]

it's ssis if you're on sql 2005

--------------------
Procrastinate Now!