Uploading Text Files To DB Using ADO.NET

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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

Anonymous said:
Is there a way of uploading text files with columns of data (eg., column
Click to expand...
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
Click to expand...
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.
Click to expand...
 
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

Anonymous said:
Is there a way of uploading text files with columns of data (eg., column
Click to expand...
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
Click to expand...
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.
Click to expand...
 
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
 
The select statement didn't work.

Do you know of a site that shows documentation on how to do this? Thanks.
 
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
 
¤ 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)
 
Back
Top