import excel sheet into a sql server table

T

TG

hi!

I am trying to create a sql server table from an excel sheet.

Here is the code I have:



'This procedure the xlsx file and dumps it to a table in SQL Server

Private Sub Button5_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button5.Click

'CreateTable()
'GenerateQueryFromDataGridView(DataGridView1, "Spambank",
"temp_spaminfo")

Dim excelConnection As System.Data.OleDb.OleDbConnection = New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.
4.0;Data Source= C:\exporteddata.xlsx;Extended Properties=Excel
8.0;HDR=YES;")

'Dim sExcelConnectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & (sExcelFileName) &
";Extended Properties=""Excel 8.0;HDR=YES;"""

excelConnection.Open()


Dim excelCommand As New System.Data.OleDb.OleDbCommand("SELECT
* INTO [ODBC; Driver={SQL Server};Server=" & lstServers.SelectedValue
& ";Database=SpamBank;Trusted_Connection=yes].[temp_spaminfo] FROM
[Sheet1$];", excelConnection)

Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim da As SqlDataAdapter
Dim ds As New DataSet

conn = New SqlConnection("Data Source=" &
lstServers.SelectedValue & ";Initial Catalog=Spambank;Integrated
Security=SSPI")
cmd = New SqlCommand("SELECT * FROM sys.objects WHERE
object_id = OBJECT_ID(N'temp_spaminfo')", conn)

da = New SqlDataAdapter(cmd)
da.Fill(ds)

If (ds.Tables(0).Rows.Count > 0) Then
MessageBox.Show("Table succesfully created in [" &
lstServers.SelectedValue & "].dbo.Spambank")
Else
MessageBox.Show("The table doesn't exist in Spambank
database")
End If



End Sub





Unfortunately I am getting an error message "Could not find
installable ISAM" at this point


excelConnection.Open()


Your help will be greatly appreciate it.

Thanks

Tammy
 
R

Rich P

Hi Tammy,

Here is a method which works for me for reading data from Excel and
writing it to Sql Server. Note: More times than not, you have to prep
the data in the Excel sheet - meaning - eliminating unused rows at then
end of the dataset in Excel, eliminating unused columns also at the end
of the dataset in Excel, making sure that the datatypes in each column
in the Excel sheet are valid (General format usually works OK but
sometimes have to format date fields as dates explicitly, text fields as
text, explicityly, numeric fields as numbers excplicitly). The note
doesn't really have anything to do with "Missing ISAM", but just
something to be aware of.

-----------------------------------
Private Sub WriteDataToSqlServerFromExcel()
Dim da As OleDbDataAdapter, conn As OleDbConnection
conn = New OleDbConnection
conn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data
source=C:\someDir\test1.xls;Extended Properties=Excel 8.0"

da = New OleDbDataAdapter
da.SelectCommand = New OleDbCommand
da.SelectCommand.Connection = conn
da.SelectCommand.CommandText = "Select * From [" Sheet1$]"

da.Fill(ds1, "Sheet1OLE")

Dim daS As New SqlDataAdapter, connS As New SqlConnection

connS.ConnectionString = "Data Source=MyServer;Initial
Catalog=MyDB;Integrated Security=True"

daS.SelectCommand = New SqlCommand
daS.SelectCommand.Connection = connS
daS.InsertCommand = New SqlCommand
daS.InsertCommand.Connection = connS

daS.SelectCommand.CommandText = "Select * From Sheet1DBTbl"
daS.Fill(ds1, "sht1DBtbl")

daS.InsertCommand.Parameters.Add("@fdl1", SqlDbType.VarChar, 50, "fld1")
daS.InsertCommand.Parameters.Add("@fdl2", SqlDbType.VarChar, 50, "fld2")
daS.InsertCommand.Parameters.Add("@fdl3", SqlDbType.VarChar, 50, "fld3")
...
daS.InsertCommand.CommandText = "Insert Into Sheet1DBTbl Select @fld1,
@fld2, @fld3, ..."

Dim reader As DataTableReader = ds1.Tables("Sheet1OLE").CreateDataReader
ds1.Tables("sht1DBtbl").Load(reader, LoadOption.Upsert)
daS.Update(ds1, "sht1DBtbl")

End Sub

-------------------------------------------

There are 2 caveats here - 1) Sheet1DBTbl is an existing table on the
server DB (if there isn't one there - you need to create it either at
the server or from code). 2) However many fields there are in the Excel
sheet (and in the table on the sql server - which must match the same
number of columns there are in the Excel sheet and the Server DB table)
-- you have to add the same number of parameters to the DataAdapter
InsertCommand. So the first half of the procedure above reads the data
from Excel into a memory table in the dataset (ds1) and the 2nd half of
the procedure writes the data from the OLE table to the Sql Server table
using a dataReader object.

One more note:

ds1.Tables("sht1DBtbl").Load(reader, LoadOption.Upsert)

(Except for the tableName -- use your own table name) this is the
correct syntax -- VB2005.

Rich
 
T

TG

Hi Tammy,

Here is a method which works for me for reading data from Excel and
writing it to Sql Server.  Note:  More times than not, you have to prep
the data in the Excel sheet - meaning - eliminating unused rows at then
end of the dataset in Excel, eliminating unused columns also at the end
of the dataset in Excel, making sure that the datatypes in each column
in the Excel sheet are valid (General format usually works OK but
sometimes have to format date fields as dates explicitly, text fields as
text, explicityly, numeric fields as numbers excplicitly).  The note
doesn't really have anything to do with "Missing ISAM", but just
something to be aware of.

-----------------------------------
Private Sub WriteDataToSqlServerFromExcel()
Dim da As OleDbDataAdapter, conn As OleDbConnection
conn = New OleDbConnection
conn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data
source=C:\someDir\test1.xls;Extended Properties=Excel 8.0"

da = New OleDbDataAdapter
da.SelectCommand = New OleDbCommand
da.SelectCommand.Connection = conn
da.SelectCommand.CommandText = "Select * From [" Sheet1$]"

da.Fill(ds1, "Sheet1OLE")

Dim daS As New SqlDataAdapter, connS As New SqlConnection

connS.ConnectionString = "Data Source=MyServer;Initial
Catalog=MyDB;Integrated Security=True"

daS.SelectCommand = New SqlCommand
daS.SelectCommand.Connection = connS
daS.InsertCommand = New SqlCommand
daS.InsertCommand.Connection = connS

daS.SelectCommand.CommandText = "Select * From Sheet1DBTbl"
daS.Fill(ds1, "sht1DBtbl")

daS.InsertCommand.Parameters.Add("@fdl1", SqlDbType.VarChar, 50, "fld1")
daS.InsertCommand.Parameters.Add("@fdl2", SqlDbType.VarChar, 50, "fld2")
daS.InsertCommand.Parameters.Add("@fdl3", SqlDbType.VarChar, 50, "fld3")
..
daS.InsertCommand.CommandText = "Insert Into Sheet1DBTbl Select @fld1,
@fld2, @fld3, ..."

Dim reader As DataTableReader = ds1.Tables("Sheet1OLE").CreateDataReader
ds1.Tables("sht1DBtbl").Load(reader, LoadOption.Upsert)
daS.Update(ds1, "sht1DBtbl")

End Sub

-------------------------------------------

There are 2 caveats here - 1) Sheet1DBTbl is an existing table on the
server DB (if there isn't one there - you need to create it either at
the server or from code).  2) However many fields there are in the Excel
sheet (and in the table on the sql server - which must match the same
number of columns there are in the Excel sheet and the Server DB table)
-- you have to add the same number of parameters to the DataAdapter
InsertCommand.  So the first half of the procedure above reads the data
from Excel into a memory table in the dataset (ds1) and the 2nd half of
the procedure writes the data from the OLE table to the Sql Server table
using a dataReader object.

One more note:

ds1.Tables("sht1DBtbl").Load(reader, LoadOption.Upsert)

(Except for the tableName -- use your own table name) this is the
correct syntax -- VB2005.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***


Hi Rich!!

Thank you so much for yur reply!!!

I understand what you are doing but in my case I have an extra
problem. Sometimes the Excel file will have a minimum of 4 columns
(this ones will always be present) and then 5 other columns that might
all show up or not dependinf if the user select the checkbox. How do I
reflect that?

I cannot create a table manually in sql server, because i don't know
how many the user wants to see.

how can i do this with vb code?

thanks a lot for all ur time!!!!

regards,


Tammy
 
M

mario

Hi,

You could try to use some third party component for exporting data to
Excel file. I recommend you to use GemBox.Spreadsheet -- here is the
example of importing/exporting data from DataTable to Excel files
(XLS, XLSX, CSV): http://www.gemboxsoftware.com/LDataTable.htm.

If you want to use free component, note that there is free version of
GemBox.Spreadsheet available -- you can even use it in your commercial
applications. Free version comes with 150 rows limit. You can find
free version here: http://www.gemboxsoftware.com/GBSpreadsheetFree.htm.

Mario
GemBox Software
--
GemBox.Spreadsheet for .NET - Easily read and write Excel (XLS, XLSX
or CSV)
or export to HTML files from your .NET apps. See
http://www.gemboxsoftware.com/GBSpreadsheet.htm
--


Hi Tammy,
Here is a method which works for me for reading data from Excel and
writing it to Sql Server. Note: More times than not, you have to prep
the data in the Excel sheet - meaning - eliminating unused rows at then
end of the dataset in Excel, eliminating unused columns also at the end
of the dataset in Excel, making sure that the datatypes in each column
in the Excel sheet are valid (General format usually works OK but
sometimes have to format date fields as dates explicitly, text fields as
text, explicityly, numeric fields as numbers excplicitly). The note
doesn't really have anything to do with "Missing ISAM", but just
something to be aware of.
-----------------------------------
Private Sub WriteDataToSqlServerFromExcel()
Dim da As OleDbDataAdapter, conn As OleDbConnection
conn = New OleDbConnection
conn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data
source=C:\someDir\test1.xls;Extended Properties=Excel 8.0"
da = New OleDbDataAdapter
da.SelectCommand = New OleDbCommand
da.SelectCommand.Connection = conn
da.SelectCommand.CommandText = "Select * From [" Sheet1$]"
da.Fill(ds1, "Sheet1OLE")
Dim daS As New SqlDataAdapter, connS As New SqlConnection
connS.ConnectionString = "Data Source=MyServer;Initial
Catalog=MyDB;Integrated Security=True"
daS.SelectCommand = New SqlCommand
daS.SelectCommand.Connection = connS
daS.InsertCommand = New SqlCommand
daS.InsertCommand.Connection = connS
daS.SelectCommand.CommandText = "Select * From Sheet1DBTbl"
daS.Fill(ds1, "sht1DBtbl")
daS.InsertCommand.Parameters.Add("@fdl1", SqlDbType.VarChar, 50, "fld1")
daS.InsertCommand.Parameters.Add("@fdl2", SqlDbType.VarChar, 50, "fld2")
daS.InsertCommand.Parameters.Add("@fdl3", SqlDbType.VarChar, 50, "fld3")
..
daS.InsertCommand.CommandText = "Insert Into Sheet1DBTbl Select @fld1,
@fld2, @fld3, ..."
Dim reader As DataTableReader = ds1.Tables("Sheet1OLE").CreateDataReader
ds1.Tables("sht1DBtbl").Load(reader, LoadOption.Upsert)
daS.Update(ds1, "sht1DBtbl")
End Sub

There are 2 caveats here - 1) Sheet1DBTbl is an existing table on the
server DB (if there isn't one there - you need to create it either at
the server or from code). 2) However many fields there are in the Excel
sheet (and in the table on the sql server - which must match the same
number of columns there are in the Excel sheet and the Server DB table)
-- you have to add the same number of parameters to the DataAdapter
InsertCommand. So the first half of the procedure above reads the data
from Excel into a memory table in the dataset (ds1) and the 2nd half of
the procedure writes the data from the OLE table to the Sql Server table
using a dataReader object.
One more note:
ds1.Tables("sht1DBtbl").Load(reader, LoadOption.Upsert)
(Except for the tableName -- use your own table name) this is the
correct syntax -- VB2005.

*** Sent via Developersdexhttp://www.developersdex.com***

Hi Rich!!

Thank you so much for yur reply!!!

I understand what you are doing but in my case I have an extra
problem. Sometimes the Excel file will have a minimum of 4 columns
(this ones will always be present) and then 5 other columns that might
all show up or not dependinf if the user select the checkbox. How do I
reflect that?

I cannot create a table manually in sql server, because i don't know
how many the user wants to see.

how can i do this with vb code?

thanks a lot for all ur time!!!!

regards,

Tammy
 
T

TG

Hi,

You could try to use some third party component for exporting data to
Excel file. I recommend you to use GemBox.Spreadsheet -- here is the
example of importing/exporting data from DataTable  to Excel files
(XLS, XLSX, CSV):http://www.gemboxsoftware.com/LDataTable.htm.

If you want to use free component, note that there is free version of
GemBox.Spreadsheet available -- you can even use it in your commercial
applications. Free version comes with 150 rows limit. You can find
free version here:http://www.gemboxsoftware.com/GBSpreadsheetFree.htm.

Mario
GemBox Software
--
GemBox.Spreadsheet for .NET - Easily read and write Excel (XLS, XLSX
or CSV)
or export to HTML files from your .NET apps. Seehttp://www.gemboxsoftware.com/GBSpreadsheet.htm
--

Hi Tammy,
Here is a method which works for me for reading data from Excel and
writing it to Sql Server.  Note:  More times than not, you have toprep
the data in the Excel sheet - meaning - eliminating unused rows at then
end of the dataset in Excel, eliminating unused columns also at the end
of the dataset in Excel, making sure that the datatypes in each column
in the Excel sheet are valid (General format usually works OK but
sometimes have to format date fields as dates explicitly, text fields as
text, explicityly, numeric fields as numbers excplicitly).  The note
doesn't really have anything to do with "Missing ISAM", but just
something to be aware of.
-----------------------------------
Private Sub WriteDataToSqlServerFromExcel()
Dim da As OleDbDataAdapter, conn As OleDbConnection
conn = New OleDbConnection
conn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data
source=C:\someDir\test1.xls;Extended Properties=Excel 8.0"
da = New OleDbDataAdapter
da.SelectCommand = New OleDbCommand
da.SelectCommand.Connection = conn
da.SelectCommand.CommandText = "Select * From [" Sheet1$]"
da.Fill(ds1, "Sheet1OLE")
Dim daS As New SqlDataAdapter, connS As New SqlConnection
connS.ConnectionString = "Data Source=MyServer;Initial
Catalog=MyDB;Integrated Security=True"
daS.SelectCommand = New SqlCommand
daS.SelectCommand.Connection = connS
daS.InsertCommand = New SqlCommand
daS.InsertCommand.Connection = connS
daS.SelectCommand.CommandText = "Select * From Sheet1DBTbl"
daS.Fill(ds1, "sht1DBtbl")
daS.InsertCommand.Parameters.Add("@fdl1", SqlDbType.VarChar, 50, "fld1")
daS.InsertCommand.Parameters.Add("@fdl2", SqlDbType.VarChar, 50, "fld2")
daS.InsertCommand.Parameters.Add("@fdl3", SqlDbType.VarChar, 50, "fld3")
..
daS.InsertCommand.CommandText = "Insert Into Sheet1DBTbl Select @fld1,
@fld2, @fld3, ..."
Dim reader As DataTableReader = ds1.Tables("Sheet1OLE").CreateDataReader
ds1.Tables("sht1DBtbl").Load(reader, LoadOption.Upsert)
daS.Update(ds1, "sht1DBtbl")
End Sub
-------------------------------------------
There are 2 caveats here - 1) Sheet1DBTbl is an existing table on the
server DB (if there isn't one there - you need to create it either at
the server or from code).  2) However many fields there are in the Excel
sheet (and in the table on the sql server - which must match the same
number of columns there are in the Excel sheet and the Server DB table)
-- you have to add the same number of parameters to the DataAdapter
InsertCommand.  So the first half of the procedure above reads the data
from Excel into a memory table in the dataset (ds1) and the 2nd half of
the procedure writes the data from the OLE table to the Sql Server table
using a dataReader object.
One more note:
ds1.Tables("sht1DBtbl").Load(reader, LoadOption.Upsert)
(Except for the tableName -- use your own table name) this is the
correct syntax -- VB2005.
Rich
*** Sent via Developersdexhttp://www.developersdex.com***
Hi Rich!!
Thank you so much for yur reply!!!
I understand what you are doing but in my case I have an extra
problem. Sometimes the Excel file will have a minimum of 4 columns
(this ones will always be present) and then 5 other columns that might
all show up or not dependinf if the user select the checkbox. How do I
reflect that?
I cannot create a table manually in sql server, because i don't know
how many the user wants to see.
how can i do this with vb code?
thanks a lot for all ur time!!!!

Tammy- Hide quoted text -

- Show quoted text -



Thank you all for taking time to answer me.

Mario,

I do not need to export data to Excel.That part of my code works
perfect as it is.



Urkec and Rich,


My problem is importing back into a SQL SERVER TABLE that needs to be
created based on the EXCEL COLUMNS The Excel columns can vary from
only 4 to 9 and everything in between depending on what the user
selects in the code below.


HERE IS WHERE THE USER SELECTS WHICH FIELDS WILL BE SHOWN IN THE
DATAGRIDVIEW: (THIS WORKS PERFECT)

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button3.Click

Cursor.Current = System.Windows.Forms.Cursors.WaitCursor

Dim cn As New SqlConnection("Data Source=" &
lstServers.SelectedValue & ";Initial Catalog=" &
lstDatabases.SelectedValue & ";Integrated Security=SSPI")
Dim cmd As New SqlCommand("usp_DR_Spam_BB_Search_get_recs",
cn)


cmd.CommandTimeout = 0
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@Matter",
lstDatabases.SelectedItem)
cmd.Parameters.AddWithValue("@FileSet",
lstFileSets.SelectedItem)

cn.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader()
Dim ds As New DataSet()
Dim dt As New DataTable("Table1")
ds.Tables.Add(dt)
ds.Load(reader, LoadOption.PreserveChanges, ds.Tables(0))
DataGridView1.Font = New Font("SansSerif", 8.25,
FontStyle.Regular)


DataGridView1.DataSource = ds.Tables(0)

If DataGridView1.Rows.Count > 0 Then

If CheckBox1.CheckState = True Then

DataGridView1.Columns("Last Name").Visible = True


ElseIf CheckBox1.CheckState = False Then

DataGridView1.Columns("Last Name").Visible = False

End If

If CheckBox2.CheckState = True Then

DataGridView1.Columns("First Name").Visible = True

ElseIf CheckBox2.CheckState = False Then

DataGridView1.Columns("First Name").Visible = False

End If

If CheckBox3.CheckState = True Then

DataGridView1.Columns("Domain").Visible = True

ElseIf CheckBox3.CheckState = False Then

DataGridView1.Columns("Domain").Visible = False

End If

If CheckBox4.CheckState = True Then

DataGridView1.Columns("Email").Visible = True

ElseIf CheckBox4.CheckState = False Then

DataGridView1.Columns("Email").Visible = False

End If

If CheckBox5.CheckState = True Then

DataGridView1.Columns("Subject").Visible = True

ElseIf CheckBox5.CheckState = False Then

DataGridView1.Columns("Subject").Visible = False

End If

Else

MessageBox.Show("There are no records using the fileset
selected, please try with a different fileset")

End If


Dim rowNumber As Integer = 1
For Each row As DataGridViewRow In DataGridView1.Rows
If row.IsNewRow Then Continue For
row.HeaderCell.Value = rowNumber.ToString
rowNumber = rowNumber + 1
Next


DataGridView1.AutoResizeRowHeadersWidth(DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders)

Cursor.Current = System.Windows.Forms.Cursors.Default

'cmd.Connection.Close()

End Sub

=====================================================================================================================================

HERE IS WHERE THE USER CAN EXPORT TO XLSX (THIS WORKS PERFECT)


Private Sub exportExcel(ByVal grdView As DataGridView, ByVal fileName
As String, _
ByVal fileExtension As String, ByVal filePath As String)

' Choose the path, name, and extension for the Excel file
Dim myFile As String = filePath & "\" & fileName &
fileExtension


' Open the file and write the headers
Dim fs As New IO.StreamWriter(myFile, False)
fs.WriteLine("<?xml version=""1.0""?>")
fs.WriteLine("<?mso-application progid=""Excel.Sheet""?>")
fs.WriteLine("<ss:Workbook xmlns:ss=""urn:schemas-microsoft-
com:blush:ffice:spreadsheet"">")

' Create the styles for the worksheet
fs.WriteLine(" <ss:Styles>")
' Style for the column headers
fs.WriteLine(" <ss:Style ss:ID=""1"">")
fs.WriteLine(" <ss:Font ss:Bold=""1""/>")
fs.WriteLine(" <ss:Alignment ss:Horizontal=""Center""
ss:Vertical=""Center"" ss:WrapText=""1""/>")
fs.WriteLine(" <ss:Interior ss:Color=""#C0C0C0""
ss:pattern=""Solid""/>")
fs.WriteLine(" </ss:Style>")
' Style for the column information
fs.WriteLine(" <ss:Style ss:ID=""2"">")
fs.WriteLine(" <ss:Alignment ss:Vertical=""Center""
ss:WrapText=""1""/>")
fs.WriteLine(" </ss:Style>")
fs.WriteLine(" </ss:Styles>")

' Write the worksheet contents
fs.WriteLine("<ss:Worksheet ss:Name=""Sheet1"">")
fs.WriteLine(" <ss:Table>")

For Each col As DataGridViewColumn In grdView.Columns
If col.Visible Then
fs.WriteLine(String.Format(" <ss:Column
ss:Width=""{0}""/>", col.Width))
End If
Next
fs.WriteLine(" <ss:Row>")

For Each col As DataGridViewColumn In grdView.Columns
If col.Visible Then
fs.WriteLine(String.Format(" <ss:Cell
ss:StyleID=""1""><ss:Data ss:Type=""String"">{0}</ss:Data></ss:Cell>",
col.HeaderText))
End If
Next
fs.WriteLine(" </ss:Row>")

' Check for an empty row at the end due to Adding allowed on
the DataGridView
Dim subtractBy As Integer
If grdView.AllowUserToAddRows = True Then subtractBy = 2 Else
subtractBy = 1
' Write contents for each cell
For Each row As DataGridViewRow In grdView.Rows
fs.WriteLine(String.Format(" <ss:Row
ss:Height=""{0}"">", row.Height))
For Each col As DataGridViewColumn In grdView.Columns
If col.Visible Then
fs.WriteLine(String.Format(" <ss:Cell
ss:StyleID=""2""><ss:Data ss:Type=""String"">{0}</ss:Data></ss:Cell>",
row.Cells(col.Name).Value.ToString))
End If
Next
fs.WriteLine(" </ss:Row>")
Next


' Close up the document
fs.WriteLine(" </ss:Table>")
fs.WriteLine("</ss:Worksheet>")
fs.WriteLine("</ss:Workbook>")
fs.Close()

' Open the file in Microsoft Excel
' 10 = SW_SHOWDEFAULT
ShellEx(Me.Handle, "Open", myFile, "", "", 10)


End Sub

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button4.Click
' Call the export sub
exportExcel(DataGridView1, "exportedData", ".xls",
My.Computer.FileSystem.SpecialDirectories.Desktop)
End Sub


======================================================================================================================================

AT THIS POINT IS WHERE I AM HAVING TROUBLES. I want either to import
the xlsx file to a SQL SERVER TABLE created on the fly with the fields
of that file OR create a table on the fly with whatever the user had
selected from the checkbox code above.

I had posted the export code at the begnning of the thread.

If anybody has a better idea on how I can achieve what I need to I'd
really be foever thankful!!! :)

Thanks a lot guys!!!!!

Regards,

Tammy
 
R

Rich P

Hi Tammy,

Did the code sample I posted earlier help you overcome the ISAM error
problem? Try that first and see if you can at least import data from
the Excel file. Once you can import data into your .Net app from Excel
then you can manipulate the data as you wish. But, as I mentioned
earlier, a caveat is that even if you successfully import the Excel data
into your app and display it in a datagridview control, if some of the
columns in the datagridview are not displaying any data where there is
data in the Excel sheet - that is where you have to prep the Excel sheet
(explicitly format the column in the Excel Sheet). Believe me, I have
been through this. Users at my place will receive Excel List from
government agencies and need to store the data in the sql server. I
told em that they have to prep the sheets before they can import the
data (and make sure to Copy the Excel file first and then SAVE the
changes to that copy of the Excel file - leaving the original file
alone).

Rich
 
U

urkec

TG said:
Hi,

You could try to use some third party component for exporting data to
Excel file. I recommend you to use GemBox.Spreadsheet -- here is the
example of importing/exporting data from DataTable �to Excel files
(XLS, XLSX, CSV):http://www.gemboxsoftware.com/LDataTable.htm.

If you want to use free component, note that there is free version of
GemBox.Spreadsheet available -- you can even use it in your commercial
applications. Free version comes with 150 rows limit. You can find
free version here:http://www.gemboxsoftware.com/GBSpreadsheetFree.htm.

Mario
GemBox Software
--
GemBox.Spreadsheet for .NET - Easily read and write Excel (XLS, XLSX
or CSV)
or export to HTML files from your .NET apps. Seehttp://www.gemboxsoftware.com/GBSpreadsheet.htm
--

On May 27, 4:58 pm, Rich P <[email protected]> wrote:
Hi Tammy,
Here is a method which works for me for reading data from Excel and
writing it to Sql Server. �Note: �More times than not, you have to prep
the data in the Excel sheet - meaning - eliminating unused rows at then
end of the dataset in Excel, eliminating unused columns also at the end
of the dataset in Excel, making sure that the datatypes in each column
in the Excel sheet are valid (General format usually works OK but
sometimes have to format date fields as dates explicitly, text fields as
text, explicityly, numeric fields as numbers excplicitly). �The note
doesn't really have anything to do with "Missing ISAM", but just
something to be aware of.
-----------------------------------
Private Sub WriteDataToSqlServerFromExcel()
Dim da As OleDbDataAdapter, conn As OleDbConnection
conn = New OleDbConnection
conn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data
source=C:\someDir\test1.xls;Extended Properties=Excel 8.0"
da = New OleDbDataAdapter
da.SelectCommand = New OleDbCommand
da.SelectCommand.Connection = conn
da.SelectCommand.CommandText = "Select * From [" Sheet1$]"
da.Fill(ds1, "Sheet1OLE")
Dim daS As New SqlDataAdapter, connS As New SqlConnection
connS.ConnectionString = "Data Source=MyServer;Initial
Catalog=MyDB;Integrated Security=True"
daS.SelectCommand = New SqlCommand
daS.SelectCommand.Connection = connS
daS.InsertCommand = New SqlCommand
daS.InsertCommand.Connection = connS
daS.SelectCommand.CommandText = "Select * From Sheet1DBTbl"
daS.Fill(ds1, "sht1DBtbl")
daS.InsertCommand.Parameters.Add("@fdl1", SqlDbType.VarChar, 50, "fld1")
daS.InsertCommand.Parameters.Add("@fdl2", SqlDbType.VarChar, 50, "fld2")
daS.InsertCommand.Parameters.Add("@fdl3", SqlDbType.VarChar, 50, "fld3")
..
daS.InsertCommand.CommandText = "Insert Into Sheet1DBTbl Select @fld1,
@fld2, @fld3, ..."
Dim reader As DataTableReader = ds1.Tables("Sheet1OLE").CreateDataReader
ds1.Tables("sht1DBtbl").Load(reader, LoadOption.Upsert)
daS.Update(ds1, "sht1DBtbl")
End Sub

There are 2 caveats here - 1) Sheet1DBTbl is an existing table on the
server DB (if there isn't one there - you need to create it either at
the server or from code). �2) However many fields there are in the Excel
sheet (and in the table on the sql server - which must match the same
number of columns there are in the Excel sheet and the Server DB table)
-- you have to add the same number of parameters to the DataAdapter
InsertCommand. �So the first half of the procedure above reads the data
from Excel into a memory table in the dataset (ds1) and the 2nd half of
the procedure writes the data from the OLE table to the Sql Server table
using a dataReader object.
One more note:
ds1.Tables("sht1DBtbl").Load(reader, LoadOption.Upsert)
(Except for the tableName -- use your own table name) this is the
correct syntax -- VB2005.

*** Sent via Developersdexhttp://www.developersdex.com***
Hi Rich!!
Thank you so much for yur reply!!!
I understand what you are doing but in my case I have an extra
problem. Sometimes the Excel file will have a minimum of 4 columns
(this ones will always be present) and then 5 other columns that might
all show up or not dependinf if the user select the checkbox. How do I
reflect that?
I cannot create a table manually in sql server, because i don't know
how many the user wants to see.
how can i do this with vb code?
thanks a lot for all ur time!!!!

Tammy- Hide quoted text -

- Show quoted text -



Thank you all for taking time to answer me.

Mario,

I do not need to export data to Excel.That part of my code works
perfect as it is.



Urkec and Rich,


My problem is importing back into a SQL SERVER TABLE that needs to be
created based on the EXCEL COLUMNS The Excel columns can vary from
only 4 to 9 and everything in between depending on what the user
selects in the code below.


HERE IS WHERE THE USER SELECTS WHICH FIELDS WILL BE SHOWN IN THE
DATAGRIDVIEW: (THIS WORKS PERFECT)

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button3.Click

Cursor.Current = System.Windows.Forms.Cursors.WaitCursor

Dim cn As New SqlConnection("Data Source=" &
lstServers.SelectedValue & ";Initial Catalog=" &
lstDatabases.SelectedValue & ";Integrated Security=SSPI")
Dim cmd As New SqlCommand("usp_DR_Spam_BB_Search_get_recs",
cn)


cmd.CommandTimeout = 0
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@Matter",
lstDatabases.SelectedItem)
cmd.Parameters.AddWithValue("@FileSet",
lstFileSets.SelectedItem)

cn.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader()
Dim ds As New DataSet()
Dim dt As New DataTable("Table1")
ds.Tables.Add(dt)
ds.Load(reader, LoadOption.PreserveChanges, ds.Tables(0))
DataGridView1.Font = New Font("SansSerif", 8.25,
FontStyle.Regular)


DataGridView1.DataSource = ds.Tables(0)

If DataGridView1.Rows.Count > 0 Then

If CheckBox1.CheckState = True Then

DataGridView1.Columns("Last Name").Visible = True


ElseIf CheckBox1.CheckState = False Then

DataGridView1.Columns("Last Name").Visible = False

End If

If CheckBox2.CheckState = True Then

DataGridView1.Columns("First Name").Visible = True

ElseIf CheckBox2.CheckState = False Then

DataGridView1.Columns("First Name").Visible = False

End If

If CheckBox3.CheckState = True Then

DataGridView1.Columns("Domain").Visible = True

ElseIf CheckBox3.CheckState = False Then

DataGridView1.Columns("Domain").Visible = False

End If

If CheckBox4.CheckState = True Then

DataGridView1.Columns("Email").Visible = True

ElseIf CheckBox4.CheckState = False Then

DataGridView1.Columns("Email").Visible = False

End If

If CheckBox5.CheckState = True Then

DataGridView1.Columns("Subject").Visible = True

ElseIf CheckBox5.CheckState = False Then

DataGridView1.Columns("Subject").Visible = False

End If

Else

MessageBox.Show("There are no records using the fileset
selected, please try with a different fileset")

End If


Dim rowNumber As Integer = 1
For Each row As DataGridViewRow In DataGridView1.Rows
If row.IsNewRow Then Continue For
row.HeaderCell.Value = rowNumber.ToString
rowNumber = rowNumber + 1
Next


DataGridView1.AutoResizeRowHeadersWidth(DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders)

Cursor.Current = System.Windows.Forms.Cursors.Default

'cmd.Connection.Close()

End Sub

=====================================================================================================================================

HERE IS WHERE THE USER CAN EXPORT TO XLSX (THIS WORKS PERFECT)


Private Sub exportExcel(ByVal grdView As DataGridView, ByVal fileName
As String, _
ByVal fileExtension As String, ByVal filePath As String)

' Choose the path, name, and extension for the Excel file
Dim myFile As String = filePath & "\" & fileName &
fileExtension


' Open the file and write the headers
Dim fs As New IO.StreamWriter(myFile, False)
fs.WriteLine("<?xml version=""1.0""?>")
fs.WriteLine("<?mso-application progid=""Excel.Sheet""?>")
fs.WriteLine("<ss:Workbook xmlns:ss=""urn:schemas-microsoft-
com:blush:ffice:spreadsheet"">")

' Create the styles for the worksheet
fs.WriteLine(" <ss:Styles>")
' Style for the column headers
fs.WriteLine(" <ss:Style ss:ID=""1"">")
fs.WriteLine(" <ss:Font ss:Bold=""1""/>")
fs.WriteLine(" <ss:Alignment ss:Horizontal=""Center""
ss:Vertical=""Center"" ss:WrapText=""1""/>")
fs.WriteLine(" <ss:Interior ss:Color=""#C0C0C0""
ss:pattern=""Solid""/>")
fs.WriteLine(" </ss:Style>")
' Style for the column information
fs.WriteLine(" <ss:Style ss:ID=""2"">")
fs.WriteLine(" <ss:Alignment ss:Vertical=""Center""
ss:WrapText=""1""/>")
fs.WriteLine(" </ss:Style>")
fs.WriteLine(" </ss:Styles>")

' Write the worksheet contents
fs.WriteLine("<ss:Worksheet ss:Name=""Sheet1"">")
fs.WriteLine(" <ss:Table>")


This worked for me. I used "Excel 12.0 Xml" (surrounded by quotes) in the
connection string and needed to repeat the workbook path in the FROM clause
([c:\WorkbookName.xlsx].[Sheet1$])


Dim excelConnection As System.Data.OleDb.OleDbConnection = New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=c:\WorkbookName.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES"";")
excelConnection.Open()
Dim excelCommand As New System.Data.OleDb.OleDbCommand("SELECT *
INTO [ODBC; Driver={SQL
Server};Server=ServerName;Database=DBName;Trusted_Connection=yes].[TableName]
FROM [c:\WorkbookName.xlsx].[Sheet1$];", excelConnection)
excelCommand.ExecuteNonQuery()
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top