473,695 Members | 2,647 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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
Jun 27 '08 #1
7 12064
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 ***
Jun 27 '08 #2
TG
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
Jun 27 '08 #3
"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
Jun 27 '08 #4
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
Jun 27 '08 #5
TG
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
Jun 27 '08 #6
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 ***
Jun 27 '08 #7
"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
Jun 27 '08 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
2430
by: nicholas | last post by:
What would be the best and easiest way to implement a web page that allows me to: - import an Ms Excel-sheet in a database on a Ms Sql 2000 Server - this should be done through an asp.net webpage - I should be able to update or add records thx 4 advice Nic
1
1812
by: gotgame0314 | last post by:
I am new to asp.net and would like to know the easiest way to view an Excel file in my asp app.
3
7467
by: Santa-D | last post by:
I've got an excel sheet that I need to import into a table, however, I want the function to automatically manipulate the data prior to it being imported. For example, i have a field called and the format is 16107 for example, when the VBA function is running I want it to add "EA" to the record so it becomes EA16107 but the field I want it to import into is called another field in the same dataset I want to import is , this
1
3013
by: tamatem | last post by:
I have an excel sheet that contain colummns as in a table in a sql data base i want to transfer this data from the sheet to the table from business logic code layer not from the enterprise manager by wizard what can i do?? ...please urgent
6
4476
by: vj83 | last post by:
Hi, I have a C#.net application in which i have read the datas from excel sheet and displayed in a datagrid in my Aspx form. The code is here private void Button2_Click(object sender, System.EventArgs e) { try
1
2219
ammoos
by: ammoos | last post by:
Hi friends Actually my problem is with the excel sheets. I have convert one of my access report to excel sheet that have too many records. After I convert the report to excel successfully, I cant open the excel sheet, because it showing the following error while I trying to open the excel sheet Cell table: too many rows or columns Help me please Thanks
3
5583
by: inepu | last post by:
I have some excel sheets that I would like to manage in access. I've created the access tables and relationships; the excel sheet is only one table and with duplicate data, I'd like to make sure that when I import the sheet, the proper tables are filled correctly. For example, the excel sheet is in the format clientID Name phone City Country In acces, I have a "Cities" table and a "Coutries" table, with the fields (e.g. for cities) ID...
0
2748
by: satenova | last post by:
Hello Friends, I am newbie when it comes to vb.net and i need to import excel sheet into access db using vb.net application. Here is the code i use for now to upload excel sheet and it works perfectly but i when i upload the file again it should include only the new records by checking the existence of ID field which i use as primary key. For now it pops up the message"Table Exists already"Could anyone help me pls? Dim...
2
2702
by: vikassawant | last post by:
hi, It's very crazy problem.I want to import excel sheet data in to JTable but I can't find any solution. Actually what I want,suppose there are 7 rows and 5 columns in a table.If I select only 2 rows today and want to select remaining rows afterwords.For that need to save that table data. Now I am storing as a excel file but can't import that file when want to select remaining rows. Please tell if you have any other idea...
0
8553
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8971
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8822
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8815
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5827
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4332
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4570
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2994
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2251
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.