Dim dbConn
As OleDbConnection =
New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="""
& ExcelFile & """;Extended Properties=""Excel 8.0;HDR=Yes;""")
Dim SheetDataTable
As DataTable
Dim SheetName
As
String
Dim SheetNameRow
As Int32
Dim SQLComm
As
String
Dim GUID
As Guid
Dim TextData
As
String
Dim i
As
Integer
Dim oBO
As
New UploadBO
Dim RetVal
As Int32 = 0
Dim LineNumber
As Int32
Try
'Open connection to Excel Spreadsheet
dbConn.Open()
'Get GUID
GUID = System.Guid.NewGuid
'Fill Datatable with Schema data
SheetDataTable = dbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
Nothing)
'Loop through each worksheet
For SheetNameRow = 0
To SheetDataTable.Rows.Count - 1
'Get Worksheet name
SheetName = SheetDataTable.Rows(SheetNameRow)("TABLE_NAME").ToString
'Select Worksheet from Excel Spreadsheet
SQLComm = String.Format("SELECT * FROM [{0}]",
SheetName)
Dim DBCommand =
New OleDbCommand(SQLComm, dbConn)
Dim DBReader
As OleDbDataReader = DBCommand.ExecuteReader()
'Remove $ and single quotes from WorkSheet name
SheetName = Replace(Replace(SheetName, "'",
""),
"$",
"")
' Loop through worksheet rows
While DBReader.Read
'Start at beginning of worksheet
LineNumber = 1
For i = 0
To DBReader.FieldCount - 1
If i < (DBReader.FieldCount - 1)
Then
TextData = TextData & DBReader.GetName(i) &
"|" ' delimit by pipe |
Else
TextData = TextData & DBReader.GetName(i)
End
If
Next
'Insert row 1 (excel field names) into db
Member
11 Points
48 Posts
How to read from excell comma delimited file and save it as a record into sql database table???
Sep 30, 2008 03:15 PM|seed|LINK
Dim SheetDataTable As DataTable
Dim SheetName As String
Dim SheetNameRow As Int32
Dim SQLComm As String
Dim GUID As Guid
Dim TextData As String
Dim i As Integer
Dim oBO As New UploadBO
Dim RetVal As Int32 = 0 Dim LineNumber As Int32 Try 'Open connection to Excel Spreadsheet
dbConn.Open()
'Get GUIDGUID = System.Guid.NewGuid
'Fill Datatable with Schema data SheetDataTable = dbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing) 'Loop through each worksheet For SheetNameRow = 0 To SheetDataTable.Rows.Count - 1 'Get Worksheet name SheetName = SheetDataTable.Rows(SheetNameRow)("TABLE_NAME").ToString 'Select Worksheet from Excel Spreadsheet SQLComm = String.Format("SELECT * FROM [{0}]", SheetName) Dim DBCommand = New OleDbCommand(SQLComm, dbConn) Dim DBReader As OleDbDataReader = DBCommand.ExecuteReader() 'Remove $ and single quotes from WorkSheet name SheetName = Replace(Replace(SheetName, "'", ""), "$", "") ' Loop through worksheet rows While DBReader.Read 'Start at beginning of worksheetLineNumber = 1
For i = 0 To DBReader.FieldCount - 1 If i < (DBReader.FieldCount - 1) ThenTextData = TextData & DBReader.GetName(i) &
"|" ' delimit by pipe | ElseTextData = TextData & DBReader.GetName(i)
End If Next 'Insert row 1 (excel field names) into dbRetVal = oBO.DoInsert(GUID, SheetName, LineNumber, TextData)
While DBReader.Read() 'Clear TextData string and increment LineNumber TextData = String.EmptyLineNumber = LineNumber + 1
For i = 0 To DBReader.FieldCount - 1 If i < (DBReader.FieldCount - 1) ThenTextData = TextData & DBReader.GetValue(i).ToString &
"|" ElseTextData = TextData & DBReader.GetValue(i).ToString
End If Next 'Insert the each remaining rows (excel values) into dbRetVal = oBO.DoInsert(GUID, SheetName, LineNumber, TextData)
End While End While Next 'Close connectiondbConn.Close()
End If Finally 'Close connectiondbConn.Close()
End TryContributor
3221 Points
814 Posts
Re: How to read from excell comma delimited file and save it as a record into sql database table?...
Oct 10, 2008 05:32 AM|ameenkpn|LINK
Hi,
Pls read this article to import a csv file into database.
http://www.aspdotnetcodes.com/Importing_CSV_Database_Schema.ini.aspx
Information is quite useful.
save data into database read an comma delimited file