Uploading Text Files To DB Using ADO.NET

[Guest]

Is there a way of uploading text files with columns of data (eg., column one is Account Number, column two is Amount) to the Database using ADO.NET? Eg. I want to do something similar to below code that I do with Excel

'Code to open the file here
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\CoopMetrics\test.xls';Extended Properties=Excel 8.0;

'Now using the OledbDataAdapter you can query the excel sheet
Dim myDataset As New DataSe
Dim myData As New OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn
myData.TableMappings.Add("Table", "ExcelTest"
myData.Fill(myDataset

Thanks for info.

 

[Miha Markic [MVP C#]]

Hi,

Yes, set myData.AcceptChangesDuringFill = false, so all rows will be marked
as Added.
Create an adapter for your database, configure its insert command and do
Update.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Is there a way of uploading text files with columns of data (eg., column

one is Account Number, column two is Amount) to the Database using ADO.NET?
Eg. I want to do something similar to below code that I do with Excel:

'Code to open the file here.
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data

Source='c:\CoopMetrics\test.xls';Extended Properties=Excel 8.0;"

'Now using the OledbDataAdapter you can query the excel sheet.
Dim myDataset As New DataSet
Dim myData As New OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn)
myData.TableMappings.Add("Table", "ExcelTest")
myData.Fill(myDataset)

Thanks for info.

 

[Jan]

Anonymous,

I'm not sure this is what you want, but if you want to fill a datatable
starting from a text-file instead of an excell file, then have a look at
www.connectionstrings.com , and look under Text, OledDb, for the correct
connectionstring. You also have to adapt the SQL statement a bit.... but
the rest stays exactly the same.

Regards,
Jan

Is there a way of uploading text files with columns of data (eg., column

one is Account Number, column two is Amount) to the Database using ADO.NET?
Eg. I want to do something similar to below code that I do with Excel:

'Code to open the file here.
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data

Source='c:\CoopMetrics\test.xls';Extended Properties=Excel 8.0;"

'Now using the OledbDataAdapter you can query the excel sheet.
Dim myDataset As New DataSet
Dim myData As New OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn)
myData.TableMappings.Add("Table", "ExcelTest")
myData.Fill(myDataset)

Thanks for info.

 

[Guest]

Thanks, Miha and Markic.

 

[Guest]

Hi, Jan. What do you write for the Select statement

Thanks.

 

[Jan]

Anonymous,

Try this (Haven't tested it, but it might work) :

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\CoopMetrics\;Extended Properties=""text;HDR=Yes;FMT=Delimited;"""
'set HDR=No if the first row in the textfile is data instead of
colunmnames.
Dim ds As New DataSet
Dim da As New OleDbDataAdapter("SELECT * FROM MyTextFile.txt", strConn)
da.Fill(ds, "TextFileDataTable")

Regards,
Jan

 

[Guest]

The select statement didn't work.

Do you know of a site that shows documentation on how to do this? Thanks.

 

[Jan]

Anonymous,

I just tested the code I gave you and it turns out ok on my system.

I'm sure you adapted <MyTextFile.txt> inside the SQL-statement to your
filename as well as the directorypath <c:\CoopMetrics\> inside the
connectionstring.

Regards,
Jan

 

[Paul Clement]

¤ The select statement didn't work.
¤
¤ Do you know of a site that shows documentation on how to do this? Thanks.

You need to identify what isn't working. Also, it may help to post a few lines from your text file.
The requirements and results will vary depending upon the field delimiter being used in the text
file.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)