import excel sheet into a sql server table

[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(B yVal sender As System.Object, ByVal e
As System.EventArg s) Handles Button5.Click

'CreateTable()
'GenerateQueryF romDataGridView (DataGridView1, "Spambank",
"temp_spaminfo" )

Dim excelConnection As System.Data.Ole Db.OleDbConnect ion = New
System.Data.Ole Db.OleDbConnect ion("Provider=M icrosoft.Jet.OL EDB.
4.0;Data Source= C:\exporteddata .xlsx;Extended Properties=Exce l
8.0;HDR=YES;")

'Dim sExcelConnectio nString As String =
"Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & (sExcelFileName ) &
";Extended Properties=""Ex cel 8.0;HDR=YES;"""

excelConnection .Open()


Dim excelCommand As New System.Data.Ole Db.OleDbCommand ("SELECT
* INTO [ODBC; Driver={SQL Server};Server= " & lstServers.Sele ctedValue
& ";Database=Spam Bank;Trusted_Co nnection=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.Sele ctedValue & ";Initial Catalog=Spamban k;Integrated
Security=SSPI")
cmd = New SqlCommand("SEL ECT * FROM sys.objects WHERE
object_id = OBJECT_ID(N'tem p_spaminfo')", conn)

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

If (ds.Tables(0).R ows.Count 0) Then
MessageBox.Show ("Table succesfully created in [" &
lstServers.Sele ctedValue & "].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

[Rich P]

Re: import excel sheet into a sql server table

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 WriteDataToSqlS erverFromExcel( )
Dim da As OleDbDataAdapte r, conn As OleDbConnection
conn = New OleDbConnection
conn.Connection String = "provider=Micro soft.Jet.OLEDB. 4.0;data
source=C:\someD ir\test1.xls;Ex tended Properties=Exce l 8.0"

da = New OleDbDataAdapte r
da.SelectComman d = New OleDbCommand
da.SelectComman d.Connection = conn
da.SelectComman d.CommandText = "Select * From [" Sheet1$]"

da.Fill(ds1, "Sheet1OLE" )

Dim daS As New SqlDataAdapter, connS As New SqlConnection

connS.Connectio nString = "Data Source=MyServer ;Initial
Catalog=MyDB;In tegrated Security=True"

daS.SelectComma nd = New SqlCommand
daS.SelectComma nd.Connection = connS
daS.InsertComma nd = New SqlCommand
daS.InsertComma nd.Connection = connS

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

daS.InsertComma nd.Parameters.A dd("@fdl1", SqlDbType.VarCh ar, 50, "fld1")
daS.InsertComma nd.Parameters.A dd("@fdl2", SqlDbType.VarCh ar, 50, "fld2")
daS.InsertComma nd.Parameters.A dd("@fdl3", SqlDbType.VarCh ar, 50, "fld3")
...
daS.InsertComma nd.CommandText = "Insert Into Sheet1DBTbl Select @fld1,
@fld2, @fld3, ..."

Dim reader As DataTableReader = ds1.Tables("She et1OLE").Create DataReader
ds1.Tables("sht 1DBtbl").Load(r eader, LoadOption.Upse rt)
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("sht 1DBtbl").Load(r eader, LoadOption.Upse rt)

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

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[TG]

Re: import excel sheet into a sql server table

On May 27, 4:58 pm, Rich P <rpng...@aol.co mwrote:

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 WriteDataToSqlS erverFromExcel( )
Dim da As OleDbDataAdapte r, conn As OleDbConnection
conn = New OleDbConnection
conn.Connection String = "provider=Micro soft.Jet.OLEDB. 4.0;data
source=C:\someD ir\test1.xls;Ex tended Properties=Exce l 8.0"
>
da = New OleDbDataAdapte r
da.SelectComman d = New OleDbCommand
da.SelectComman d.Connection = conn
da.SelectComman d.CommandText = "Select * From [" Sheet1$]"
>
da.Fill(ds1, "Sheet1OLE" )
>
Dim daS As New SqlDataAdapter, connS As New SqlConnection
>
connS.Connectio nString = "Data Source=MyServer ;Initial
Catalog=MyDB;In tegrated Security=True"
>
daS.SelectComma nd = New SqlCommand
daS.SelectComma nd.Connection = connS
daS.InsertComma nd = New SqlCommand
daS.InsertComma nd.Connection = connS
>
daS.SelectComma nd.CommandText = "Select * From Sheet1DBTbl"
daS.Fill(ds1, "sht1DBtbl" )
>
daS.InsertComma nd.Parameters.A dd("@fdl1", SqlDbType.VarCh ar, 50, "fld1")
daS.InsertComma nd.Parameters.A dd("@fdl2", SqlDbType.VarCh ar, 50, "fld2")
daS.InsertComma nd.Parameters.A dd("@fdl3", SqlDbType.VarCh ar, 50, "fld3")
..
daS.InsertComma nd.CommandText = "Insert Into Sheet1DBTbl Select @fld1,
@fld2, @fld3, ..."
>
Dim reader As DataTableReader = ds1.Tables("She et1OLE").Create DataReader
ds1.Tables("sht 1DBtbl").Load(r eader, LoadOption.Upse rt)
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("sht 1DBtbl").Load(r eader, LoadOption.Upse rt)
>
(Except for the tableName -- use your own table name) this is the
correct syntax -- VB2005.
>
Rich
>
*** Sent via Developersdexht tp://www.developersd ex.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

[=?Utf-8?B?dXJrZWM=?=]

Re: import excel sheet into a sql server table

"TG" wrote:

-----------------------------------
Private Sub WriteDataToSqlS erverFromExcel( )
Dim da As OleDbDataAdapte r, conn As OleDbConnection
conn = New OleDbConnection
conn.Connection String = "provider=Micro soft.Jet.OLEDB. 4.0;data
source=C:\someD ir\test1.xls;Ex tended Properties=Exce l 8.0"

For .xlsx files you need a different connection string. See this link:

http://www.connectionstrings.com/?carrier=excel2007

--
urkec

[mario@gemboxsoftware.com]

Re: import excel sheet into a sql server table

Hi,

You could try to use some third party component for exporting data to
Excel file. I recommend you to use GemBox.Spreadsh eet -- 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.Spreadsh eet 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.Spreadsh eet 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

--


On May 28, 2:54 am, TG <jtam...@yahoo. comwrote:

On May 27, 4:58 pm, Rich P <rpng...@aol.co mwrote:
>
>
>

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 WriteDataToSqlS erverFromExcel( )
Dim da As OleDbDataAdapte r, conn As OleDbConnection
conn = New OleDbConnection
conn.Connection String = "provider=Micro soft.Jet.OLEDB. 4.0;data
source=C:\someD ir\test1.xls;Ex tended Properties=Exce l 8.0"

>

da = New OleDbDataAdapte r
da.SelectComman d = New OleDbCommand
da.SelectComman d.Connection = conn
da.SelectComman d.CommandText = "Select * From [" Sheet1$]"

>

da.Fill(ds1, "Sheet1OLE" )

>

Dim daS As New SqlDataAdapter, connS As New SqlConnection

>

connS.Connectio nString = "Data Source=MyServer ;Initial
Catalog=MyDB;In tegrated Security=True"

>

daS.SelectComma nd = New SqlCommand
daS.SelectComma nd.Connection = connS
daS.InsertComma nd = New SqlCommand
daS.InsertComma nd.Connection = connS

>

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

>

daS.InsertComma nd.Parameters.A dd("@fdl1", SqlDbType.VarCh ar, 50, "fld1")
daS.InsertComma nd.Parameters.A dd("@fdl2", SqlDbType.VarCh ar, 50, "fld2")
daS.InsertComma nd.Parameters.A dd("@fdl3", SqlDbType.VarCh ar, 50, "fld3")
..
daS.InsertComma nd.CommandText = "Insert Into Sheet1DBTbl Select @fld1,
@fld2, @fld3, ..."

>

Dim reader As DataTableReader = ds1.Tables("She et1OLE").Create DataReader
ds1.Tables("sht 1DBtbl").Load(r eader, LoadOption.Upse rt)
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("sht 1DBtbl").Load(r eader, LoadOption.Upse rt)

>

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

>

Rich

>

*** Sent via Developersdexht tp://www.developersd ex.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

[TG]

Re: import excel sheet into a sql server table

On May 28, 8:05 am, ma...@gemboxsof tware.com wrote:

Hi,
>
You could try to use some third party component for exporting data to
Excel file. I recommend you to use GemBox.Spreadsh eet -- 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.Spreadsh eet 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.Spreadsh eet for .NET - Easily read and write Excel (XLS, XLSX
or CSV)
or export to HTML files from your .NET apps. Seehttp://www.gemboxsoftw are.com/GBSpreadsheet.h tm
--
>
On May 28, 2:54 am, TG <jtam...@yahoo. comwrote:
>
>
>

On May 27, 4:58 pm, Rich P <rpng...@aol.co mwrote:

>

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 WriteDataToSqlS erverFromExcel( )
Dim da As OleDbDataAdapte r, conn As OleDbConnection
conn = New OleDbConnection
conn.Connection String = "provider=Micro soft.Jet.OLEDB. 4.0;data
source=C:\someD ir\test1.xls;Ex tended Properties=Exce l 8.0"

>

da = New OleDbDataAdapte r
da.SelectComman d = New OleDbCommand
da.SelectComman d.Connection = conn
da.SelectComman d.CommandText = "Select * From [" Sheet1$]"

>

da.Fill(ds1, "Sheet1OLE" )

>

Dim daS As New SqlDataAdapter, connS As New SqlConnection

>

connS.Connectio nString = "Data Source=MyServer ;Initial
Catalog=MyDB;In tegrated Security=True"

>

daS.SelectComma nd = New SqlCommand
daS.SelectComma nd.Connection = connS
daS.InsertComma nd = New SqlCommand
daS.InsertComma nd.Connection = connS

>

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

>

daS.InsertComma nd.Parameters.A dd("@fdl1", SqlDbType.VarCh ar, 50, "fld1")
daS.InsertComma nd.Parameters.A dd("@fdl2", SqlDbType.VarCh ar, 50, "fld2")
daS.InsertComma nd.Parameters.A dd("@fdl3", SqlDbType.VarCh ar, 50, "fld3")
..
daS.InsertComma nd.CommandText = "Insert Into Sheet1DBTbl Select @fld1,
@fld2, @fld3, ..."

>

Dim reader As DataTableReader = ds1.Tables("She et1OLE").Create DataReader
ds1.Tables("sht 1DBtbl").Load(r eader, LoadOption.Upse rt)
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("sht 1DBtbl").Load(r eader, LoadOption.Upse rt)

>

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

>

Rich

>

*** Sent via Developersdexht tp://www.developersd ex.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- 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(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button3.Click

Cursor.Current = System.Windows. Forms.Cursors.W aitCursor

Dim cn As New SqlConnection(" Data Source=" &
lstServers.Sele ctedValue & ";Initial Catalog=" &
lstDatabases.Se lectedValue & ";Integrate d Security=SSPI")
Dim cmd As New SqlCommand("usp _DR_Spam_BB_Sea rch_get_recs",
cn)


cmd.CommandTime out = 0
cmd.CommandType = CommandType.Sto redProcedure
cmd.Parameters. AddWithValue("@ Matter",
lstDatabases.Se lectedItem)
cmd.Parameters. AddWithValue("@ FileSet",
lstFileSets.Sel ectedItem)

cn.Open()
Dim reader As SqlDataReader = cmd.ExecuteRead er()
Dim ds As New DataSet()
Dim dt As New DataTable("Tabl e1")
ds.Tables.Add(d t)
ds.Load(reader, LoadOption.Pres erveChanges, ds.Tables(0))
DataGridView1.F ont = New Font("SansSerif ", 8.25,
FontStyle.Regul ar)


DataGridView1.D ataSource = ds.Tables(0)

If DataGridView1.R ows.Count 0 Then

If CheckBox1.Check State = True Then

DataGridView1.C olumns("Last Name").Visible = True


ElseIf CheckBox1.Check State = False Then

DataGridView1.C olumns("Last Name").Visible = False

End If

If CheckBox2.Check State = True Then

DataGridView1.C olumns("First Name").Visible = True

ElseIf CheckBox2.Check State = False Then

DataGridView1.C olumns("First Name").Visible = False

End If

If CheckBox3.Check State = True Then

DataGridView1.C olumns("Domain" ).Visible = True

ElseIf CheckBox3.Check State = False Then

DataGridView1.C olumns("Domain" ).Visible = False

End If

If CheckBox4.Check State = True Then

DataGridView1.C olumns("Email") .Visible = True

ElseIf CheckBox4.Check State = False Then

DataGridView1.C olumns("Email") .Visible = False

End If

If CheckBox5.Check State = True Then

DataGridView1.C olumns("Subject ").Visible = True

ElseIf CheckBox5.Check State = False Then

DataGridView1.C olumns("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.R ows
If row.IsNewRow Then Continue For
row.HeaderCell. Value = rowNumber.ToStr ing
rowNumber = rowNumber + 1
Next


DataGridView1.A utoResizeRowHea dersWidth(DataG ridViewRowHeade rsWidthSizeMode .AutoSizeToAllH eaders)

Cursor.Current = System.Windows. Forms.Cursors.D efault

'cmd.Connection .Close()

End Sub

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

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


Private Sub exportExcel(ByV al 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:office:spre adsheet"">")

' 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=""C enter"" ss:WrapText=""1 ""/>")
fs.WriteLine(" <ss:Interior ss:Color=""#C0C 0C0""
ss:Pattern=""So lid""/>")
fs.WriteLine(" </ss:Style>")
' Style for the column information
fs.WriteLine(" <ss:Style ss:ID=""2"">")
fs.WriteLine(" <ss:Alignment ss:Vertical=""C enter""
ss:WrapText=""1 ""/>")
fs.WriteLine(" </ss:Style>")
fs.WriteLine(" </ss:Styles>")

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

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

For Each col As DataGridViewCol umn In grdView.Columns
If col.Visible Then
fs.WriteLine(St ring.Format(" <ss:Cell
ss:StyleID=""1" "><ss:Data ss:Type=""Strin g"">{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.AllowUs erToAddRows = True Then subtractBy = 2 Else
subtractBy = 1
' Write contents for each cell
For Each row As DataGridViewRow In grdView.Rows
fs.WriteLine(St ring.Format(" <ss:Row
ss:Height=""{0} "">", row.Height))
For Each col As DataGridViewCol umn In grdView.Columns
If col.Visible Then
fs.WriteLine(St ring.Format(" <ss:Cell
ss:StyleID=""2" "><ss:Data ss:Type=""Strin g"">{0}</ss:Data></ss:Cell>",
row.Cells(col.N ame).Value.ToSt ring))
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.Hand le, "Open", myFile, "", "", 10)


End Sub

Private Sub Button4_Click(B yVal sender As System.Object, ByVal e
As System.EventArg s) Handles Button4.Click
' Call the export sub
exportExcel(Dat aGridView1, "exportedDa ta", ".xls",
My.Computer.Fil eSystem.Special Directories.Des ktop)
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

[Rich P]

Re: import excel sheet into a sql server table

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

*** Sent via Developersdex http://www.developersdex.com ***

[=?Utf-8?B?dXJrZWM=?=]

Re: import excel sheet into a sql server table

"TG" wrote:

On May 28, 8:05�am, ma...@gemboxsof tware.com wrote:

Hi,

You could try to use some third party component for exporting data to
Excel file. I recommend you to use GemBox.Spreadsh eet -- 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.Spreadsh eet 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.Spreadsh eet for .NET - Easily read and write Excel (XLS, XLSX
or CSV)
or export to HTML files from your .NET apps. Seehttp://www.gemboxsoftw are.com/GBSpreadsheet.h tm
--

On May 28, 2:54 am, TG <jtam...@yahoo. comwrote:

On May 27, 4:58 pm, Rich P <rpng...@aol.co mwrote:

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 WriteDataToSqlS erverFromExcel( )
Dim da As OleDbDataAdapte r, conn As OleDbConnection
conn = New OleDbConnection
conn.Connection String = "provider=Micro soft.Jet.OLEDB. 4.0;data
source=C:\someD ir\test1.xls;Ex tended Properties=Exce l 8.0"

da = New OleDbDataAdapte r
da.SelectComman d = New OleDbCommand
da.SelectComman d.Connection = conn
da.SelectComman d.CommandText = "Select * From [" Sheet1$]"

da.Fill(ds1, "Sheet1OLE" )

Dim daS As New SqlDataAdapter, connS As New SqlConnection

connS.Connectio nString = "Data Source=MyServer ;Initial
Catalog=MyDB;In tegrated Security=True"

daS.SelectComma nd = New SqlCommand
daS.SelectComma nd.Connection = connS
daS.InsertComma nd = New SqlCommand
daS.InsertComma nd.Connection = connS

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

daS.InsertComma nd.Parameters.A dd("@fdl1", SqlDbType.VarCh ar, 50, "fld1")
daS.InsertComma nd.Parameters.A dd("@fdl2", SqlDbType.VarCh ar, 50, "fld2")
daS.InsertComma nd.Parameters.A dd("@fdl3", SqlDbType.VarCh ar, 50, "fld3")
..
daS.InsertComma nd.CommandText = "Insert Into Sheet1DBTbl Select @fld1,
@fld2, @fld3, ..."

Dim reader As DataTableReader = ds1.Tables("She et1OLE").Create DataReader
ds1.Tables("sht 1DBtbl").Load(r eader, LoadOption.Upse rt)
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("sht 1DBtbl").Load(r eader, LoadOption.Upse rt)

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

Rich

*** Sent via Developersdexht tp://www.developersd ex.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- 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(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button3.Click
>
Cursor.Current = System.Windows. Forms.Cursors.W aitCursor
>
Dim cn As New SqlConnection(" Data Source=" &
lstServers.Sele ctedValue & ";Initial Catalog=" &
lstDatabases.Se lectedValue & ";Integrate d Security=SSPI")
Dim cmd As New SqlCommand("usp _DR_Spam_BB_Sea rch_get_recs",
cn)
>
>
cmd.CommandTime out = 0
cmd.CommandType = CommandType.Sto redProcedure
cmd.Parameters. AddWithValue("@ Matter",
lstDatabases.Se lectedItem)
cmd.Parameters. AddWithValue("@ FileSet",
lstFileSets.Sel ectedItem)
>
cn.Open()
Dim reader As SqlDataReader = cmd.ExecuteRead er()
Dim ds As New DataSet()
Dim dt As New DataTable("Tabl e1")
ds.Tables.Add(d t)
ds.Load(reader, LoadOption.Pres erveChanges, ds.Tables(0))
DataGridView1.F ont = New Font("SansSerif ", 8.25,
FontStyle.Regul ar)
>
>
DataGridView1.D ataSource = ds.Tables(0)
>
If DataGridView1.R ows.Count 0 Then
>
If CheckBox1.Check State = True Then
>
DataGridView1.C olumns("Last Name").Visible = True
>
>
ElseIf CheckBox1.Check State = False Then
>
DataGridView1.C olumns("Last Name").Visible = False
>
End If
>
If CheckBox2.Check State = True Then
>
DataGridView1.C olumns("First Name").Visible = True
>
ElseIf CheckBox2.Check State = False Then
>
DataGridView1.C olumns("First Name").Visible = False
>
End If
>
If CheckBox3.Check State = True Then
>
DataGridView1.C olumns("Domain" ).Visible = True
>
ElseIf CheckBox3.Check State = False Then
>
DataGridView1.C olumns("Domain" ).Visible = False
>
End If
>
If CheckBox4.Check State = True Then
>
DataGridView1.C olumns("Email") .Visible = True
>
ElseIf CheckBox4.Check State = False Then
>
DataGridView1.C olumns("Email") .Visible = False
>
End If
>
If CheckBox5.Check State = True Then
>
DataGridView1.C olumns("Subject ").Visible = True
>
ElseIf CheckBox5.Check State = False Then
>
DataGridView1.C olumns("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.R ows
If row.IsNewRow Then Continue For
row.HeaderCell. Value = rowNumber.ToStr ing
rowNumber = rowNumber + 1
Next
>
>
DataGridView1.A utoResizeRowHea dersWidth(DataG ridViewRowHeade rsWidthSizeMode .AutoSizeToAllH eaders)
>
Cursor.Current = System.Windows. Forms.Cursors.D efault
>
'cmd.Connection .Close()
>
End Sub
>
=============== =============== =============== =============== =============== =============== =============== =============== =============
>
HERE IS WHERE THE USER CAN EXPORT TO XLSX (THIS WORKS PERFECT)
>
>
Private Sub exportExcel(ByV al 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:office:spre adsheet"">")
>
' 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=""C enter"" ss:WrapText=""1 ""/>")
fs.WriteLine(" <ss:Interior ss:Color=""#C0C 0C0""
ss:Pattern=""So lid""/>")
fs.WriteLine(" </ss:Style>")
' Style for the column information
fs.WriteLine(" <ss:Style ss:ID=""2"">")
fs.WriteLine(" <ss:Alignment ss:Vertical=""C enter""
ss:WrapText=""1 ""/>")
fs.WriteLine(" </ss:Style>")
fs.WriteLine(" </ss:Styles>")
>
' Write the worksheet contents
fs.WriteLine("< ss:Worksheet ss:Name=""Sheet 1"">")
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.Ole Db.OleDbConnect ion = New
System.Data.Ole Db.OleDbConnect ion("Provider=M icrosoft.ACE.OL EDB.12.0;Data
Source=c:\Workb ookName.xlsx;Ex tended Properties=""Ex cel 12.0 Xml;HDR=YES"";" )
excelConnection .Open()
Dim excelCommand As New System.Data.Ole Db.OleDbCommand ("SELECT *
INTO [ODBC; Driver={SQL
Server};Server= ServerName;Data base=DBName;Tru sted_Connection =yes].[TableName]
FROM [c:\WorkbookName .xlsx].[Sheet1$];", excelConnection )
excelCommand.Ex ecuteNonQuery()


--
urkec