load multiple .txt files to sql server

[bmpsu]

I am attempting to write a vb.net class file that will look at a file directory that contains multiple text files that are ready to load into sql server 2005 tables.

I want this to be as automated as possible. I would like to be able to "somehow" compare the columns in the text file with the table columns in my database to determine which table the text file should be loaded to. At least I think this would be a good way to accomplish this. But then again, I really have no idea how I am going to do this.

Has anyone every done anything like this before or have an idea of how I could do this.

Pretty much the class file will grab a text file from a folder and determine that the data in this text file belongs to this database table.

Thanks.

 

[jebenson]

Well, you can open text files in a DataTable with a DataAdapter, just as if they were a database table:

If your text file is delimited:

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Path\to\text\files\folder\;Extended Properties=""text;HDR=Yes;FMT=Delimited"";"

If your text file is fixed width:

ConnectionString = Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Path\to\text\files\folder\;Extended Properties=""text;HDR=Yes;FMT=Fixed"";"

(Note: you can find these and many other connection strings at

http://www.connectionstrings.com/)

Once you have a connection you open the text files like any other data table:

'Assume an OleDbConnection named 'conn'
Dim da As OleDbDataAdapter
Dim dt As DataTable

da = New OleDbDataAdapter("Select * from SomeTextFile.txt", conn)

dt = New DataTable

da.Fill(dt)

From here, you can compare the columns, etc.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[bmpsu]

Thank you for your response. I should have stated that I am able to read the text file into a DataTable. The comparing columns part is where I am a little 'iffy'. That was the only way I could think of to determine which text file belongs to which table. I was wondering if there would be another way to do this. It doesn't appear to be.

What I have done to this point is pulled all the columns from my database and stored them into a .net DataTable, grouped by table_name. I also have the columns from the text file stored in a .net DataTable. This is where I am not sure of the best way to find a match.

Any suggestions?