Importing Files To DB Using VB.NET and ADO.NET

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

Guest

I did this before in VB 6.0/5.0. But how do you programmatcally write code using VB.NET and ADO.NET to import Excel/Text files into a database? Eg., the Excel file/Text file has columns of Account Number and Amount and need to be imported to a Table with fields Account Number and Amount. I think I need FileStream and StreamWriter objects for this

Are there a site that teaches you how to import Excel/Text/etc files into data programmatically

Thanks for info.
 
Hi
You can use the Microsoft.Jet.OLEDB.4.0 or the Excel ODBC driver to connect to the Excel fil
Dim strConn as String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="path of the file";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

NOw you can use this data set to load your table in the database

Thank
Shij
MC
 
Thanks a bundle, Shiju. That really helps

Is there a site that provides documentation on this Excel object

- Chong
 
Hi Shiju
I have slightly different problem my TextVersion.xls file has many table and garbage data on in and i want to read the all excel rows and want to put in access
Table with only two column on it 1st is auto number and 2nd column is memo field after that I can manipulate my self this table
It is ok if i can have the all file as CSV format
Like

Item Units Amount
Two-Box Volume Bonus Counted Units 170 0
Two-Box Volume Bonus Payment 170 5100
Allowance Plan Bonus 1559 44425
Service Options 4 51
SIM Payment 623 3438.96
Handset Subsidy / Handset Bonus 719 76756.91
Post-Pay 31 Day Disconn Clawback 1 -150

REGISTRATIONS WITH INACCURATE/INCOMPLETE INFORMATION (PLEASE SUPPLY DETAILS)
SE Code Registered Agreement Phone No. IMEI Model Despatched Type
FB54 17/03/2004 779261833 7956580737 3.51263E+14 Sony Ericsson T610 Upgrade
REGISTRATIONS WITH INACCURATE/INCOMPLETE INFORMATION (PLEASE SUPPLY DETAILS)
SE Code Registered Agreement Phone No. IMEI Model Despatched Type
FB54 29/03/2004 781446364 7903777870 3.51296E+14 Sony Ericsson T630 New
FB54 29/03/2004 781405717 7903223148 3.52943E+14 Nokia 6100 New

Can you help me out for this
Thanks in advance
BABAMuntaq



name ----- ShijuFrancis wrote: ----

Hi
You can use the Microsoft.Jet.OLEDB.4.0 or the Excel ODBC driver to connect to the Excel fil
Dim strConn as String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="path of the file";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

NOw you can use this data set to load your table in the database

Thank
Shij
MCP
 
The extensions library has methods to manipulate binary data fields,
including importing of any file to a database field and dumping the contents
of a binary field to a file.

E-mail me for you free copy: (e-mail address removed)
Your acceptance of the license agreement will be required.


--

Ori Millo

BabaMuntaqi said:
Hi Shiju,
I have slightly different problem my TextVersion.xls file
has many table and garbage data on in and i want to read the all excel rows
and want to put in access
Table with only two column on it 1st is auto number and 2nd column is memo
field after that I can manipulate my self this table
It is ok if i can have the all file as CSV format
Like

Item Units Amount
Two-Box Volume Bonus Counted Units 170 0
Two-Box Volume Bonus Payment 170 5100
Allowance Plan Bonus 1559 44425
Service Options 4 51
SIM Payment 623 3438.96
Handset Subsidy / Handset Bonus 719 76756.91
Post-Pay 31 Day Disconn Clawback 1 -150

REGISTRATIONS WITH INACCURATE/INCOMPLETE INFORMATION (PLEASE SUPPLY DETAILS)
SE Code Registered Agreement Phone No. IMEI Model Despatched Type
FB54 17/03/2004 779261833 7956580737 3.51263E+14 Sony Ericsson T610 Upgrade
REGISTRATIONS WITH INACCURATE/INCOMPLETE INFORMATION (PLEASE SUPPLY DETAILS)
SE Code Registered Agreement Phone No. IMEI Model Despatched Type
FB54 29/03/2004 781446364 7903777870 3.51296E+14 Sony Ericsson T630 New
FB54 29/03/2004 781405717 7903223148 3.52943E+14 Nokia 6100 New

Can you help me out for this
Thanks in advance
BABAMuntaqi





name ----- ShijuFrancis wrote: -----

Hi,
You can use the Microsoft.Jet.OLEDB.4.0 or the Excel ODBC driver to connect to the Excel file
Dim strConn as String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source="path of the file";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)

NOw you can use this data set to load your table in the database.

Thanks
Shiju
MCP
 
Back
Top