Using an Excel sheet as a DB table

  • Thread starter Thread starter cj
  • Start date Start date
C

cj

I have to take an excel sheet and for each of the 40,000 some customer
records look up their address from a Sql Server database using their
customer number found in the first column of the excel table and add the
address columns addr1, addr2, city, state, zip to the end of the excel
table records so I can return the spreadsheet to accounting. I'm
thinking I like the approach of accessing the excel sheet via oledb
database like below. I don't need to display it in the datagrid in the
final program I was just making sure I had the data loaded into a
datatable. Actually I don't know that I need the datatable. How do I
loop through this oledb table record by record? How do I add fields to
the end of the records once I've found the address? Do I need to add
columns to the spreadsheet ahead of time for the address data? Any
ideas would be appreciated. I'll post this while I continue to try to
figure it out.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Button1.Enabled = False
MyOpenFileDialog = New OpenFileDialog
MyOpenFileDialog.Title = "Open Excel file"
MyOpenFileDialog.FileName = ""
MyOpenFileDialog.InitialDirectory = "c:\"
MyOpenFileDialog.DefaultExt = ".XLS"
MyOpenFileDialog.Filter = "XLS files (*.XLS)|*.XLS|All files
(*.*)|*.*"
MyOpenFileDialog.RestoreDirectory = True

If MyOpenFileDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
Dim filename As String = MyOpenFileDialog.FileName

Dim mydt As New System.Data.DataTable

Dim myExcelConnection As New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=" & filename & "; Extended Properties=""Excel 8.0""")
Dim myExcelSqlCommand As String = "SELECT * FROM [" & "all
ANIs" & "$]"
Dim myExcelAdapter = New
System.Data.OleDb.OleDbDataAdapter(myExcelSqlCommand, myExcelConnection)
myExcelAdapter.fill(mydt)
DataGridView1.DataSource = mydt
End If
Button1.Enabled = True
End Sub
 
I have to take an excel sheet and for each of the 40,000 some customer
records look up their address from a Sql Server database using their
customer number found in the first column of the excel table and add the
address columns addr1, addr2, city, state, zip to the end of the excel
table records so I can return the spreadsheet to accounting. I'm
thinking I like the approach of accessing the excel sheet via oledb
database like below. I don't need to display it in the datagrid in the
final program I was just making sure I had the data loaded into a
datatable. Actually I don't know that I need the datatable. How do I
loop through this oledb table record by record? How do I add fields to
the end of the records once I've found the address? Do I need to add
columns to the spreadsheet ahead of time for the address data? Any
ideas would be appreciated. I'll post this while I continue to try to
figure it out.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Button1.Enabled = False
MyOpenFileDialog = New OpenFileDialog
MyOpenFileDialog.Title = "Open Excel file"
MyOpenFileDialog.FileName = ""
MyOpenFileDialog.InitialDirectory = "c:\"
MyOpenFileDialog.DefaultExt = ".XLS"
MyOpenFileDialog.Filter = "XLS files (*.XLS)|*.XLS|All files
(*.*)|*.*"
MyOpenFileDialog.RestoreDirectory = True

If MyOpenFileDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
Dim filename As String = MyOpenFileDialog.FileName

Dim mydt As New System.Data.DataTable

Dim myExcelConnection As New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=" & filename & "; Extended Properties=""Excel 8.0""")
Dim myExcelSqlCommand As String = "SELECT * FROM [" & "all
ANIs" & "$]"
Dim myExcelAdapter = New
System.Data.OleDb.OleDbDataAdapter(myExcelSqlCommand, myExcelConnection)
myExcelAdapter.fill(mydt)
DataGridView1.DataSource = mydt
End If
Button1.Enabled = True
End Sub
Click to expand...

I found when working with Excel and having to add it was easier to
pull out the Excel interop objects. They're a little tricky to work
with but you can wield a lot more power when it comes to adding and
removing columns. Otherwise I would think that you would add columns
to your Excel sheet the same way you would a SQL table = "ALTER TABLE
[" & "all ANIs" & "$] ADD City Varchar(50) NOT NULL"
 
Ok. Since the table to be updated is an excel spreadsheet I can go
ahead an make columns for street, city, state and zip before we begin
any programming. That being done I can open it with and oledb
connection from VB. I then open the customer address table on the sql
server with a sqldb connection. Is there any way to run an update
command on the oledb connection referencing the table in the
sqlconnection??????

Something like:

update excel
set excel.street=sql.street
set excel.city=sql.city
set excel.state=sql.state
set excel.zip=sql.zip
where excel.cust_no=sql.cust_no

doubt my syntax is correct but you see what I wish to do.



cfps.Christian said:
I have to take an excel sheet and for each of the 40,000 some customer
records look up their address from a Sql Server database using their
customer number found in the first column of the excel table and add the
address columns addr1, addr2, city, state, zip to the end of the excel
table records so I can return the spreadsheet to accounting. I'm
thinking I like the approach of accessing the excel sheet via oledb
database like below. I don't need to display it in the datagrid in the
final program I was just making sure I had the data loaded into a
datatable. Actually I don't know that I need the datatable. How do I
loop through this oledb table record by record? How do I add fields to
the end of the records once I've found the address? Do I need to add
columns to the spreadsheet ahead of time for the address data? Any
ideas would be appreciated. I'll post this while I continue to try to
figure it out.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Button1.Enabled = False
MyOpenFileDialog = New OpenFileDialog
MyOpenFileDialog.Title = "Open Excel file"
MyOpenFileDialog.FileName = ""
MyOpenFileDialog.InitialDirectory = "c:\"
MyOpenFileDialog.DefaultExt = ".XLS"
MyOpenFileDialog.Filter = "XLS files (*.XLS)|*.XLS|All files
(*.*)|*.*"
MyOpenFileDialog.RestoreDirectory = True

If MyOpenFileDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
Dim filename As String = MyOpenFileDialog.FileName

Dim mydt As New System.Data.DataTable

Dim myExcelConnection As New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=" & filename & "; Extended Properties=""Excel 8.0""")
Dim myExcelSqlCommand As String = "SELECT * FROM [" & "all
ANIs" & "$]"
Dim myExcelAdapter = New
System.Data.OleDb.OleDbDataAdapter(myExcelSqlCommand, myExcelConnection)
myExcelAdapter.fill(mydt)
DataGridView1.DataSource = mydt
End If
Button1.Enabled = True
End Sub
Click to expand...

I found when working with Excel and having to add it was easier to
pull out the Excel interop objects. They're a little tricky to work
with but you can wield a lot more power when it comes to adding and
removing columns. Otherwise I would think that you would add columns
to your Excel sheet the same way you would a SQL table = "ALTER TABLE
[" & "all ANIs" & "$] ADD City Varchar(50) NOT NULL"
Click to expand...
 
Hello cj,

From your post, my understanding on this issue is: you wonder how to update
the rows in Excel according to the query in Database with customer No. If
I'm off base, please feel free to let me know.

According to your pseudo code:
update excel set excel.street=sql.street set excel.city=sql.city set
excel.state=sql.state set excel.zip=sql.zip where excel.cust_no=sql.cust_no
I think we could follow the logic below:
1. Use a OleDbDataReader object to read the customer no. in excel one by
one.
2. For the current customer number read by the OleDbDataReader object, use
a SqlDataReader object to query the other customer information from Database
3. Use a OleDbCommand object to update the row in Excel.

Here are some pseudo codes for your reference:
' 1. Use a OleDbDataReader object to read the customer no. in excel one by
one.
' create a OleDbCommand object here: cmd
cmd.Connection = excelConn ' the excel connection
cmd.CommandText = "SELECT custom_no FROM [Sheet1$]"
' create a OleDbDataReader object: cuNoReader
cuNoReader = cmd.ExecuteReader()

' 2. For the current customer no read by the OleDbDataReader object, use a
SqlDataReader
' object to query the other customer information from Database
' create a SqlCommand object here: sqlCmd
sqlCmd.Connection = sqlConn ' the sql connection
sqlCmd.CommandText = "SELECT * FROM [CUTable] WHERE custom_no=@custom_no"
sqlCmd.Parameters.Add("@custom_no", SqlDbType.Int)

' 3. Use a OleDbCommand object to update the row in Excel.
' create a OleDbCommand object here: updateCmd
updateCmd.Connection = excelConn ' the excel connection
updateCmd.CommandText = "UPDATE [Sheet1$] SET street=@street,city=@city
WHERE custom_no=@custom_no"
updateCmd.Parameters.Add("@street", OleDbType.VarChar, 255)
updateCmd.Parameters.Add("@city", OleDbType.VarChar, 255)
updateCmd.Parameters.Add("@custom_no", OleDbType.Integer)

While cuNoReader.Read() ' read the customer number one by one
Dim cu_no as integer = (int)cuNoReader["custom_no"] // get the
customer no
' read the customer info for the customer from sql db
sqlCmd.Parameters["@custom_no"].Value = cu_no
Dim sqlReader as SqlDataReader = sqlCmd.ExecuteReader();
if sqlReader.Read() then
' update the row in the excel
updateCmd.Parameters["@custom_no"].Value = cu_no
updateCmd.Parameters["@street"].Value =
sqlReader["street"].ToString()
updateCmd.Parameters["@city"].Value = sqlReader["city"].ToString()
updateCmd.ExecuteNonQuery()
End If
sqlReader.Close()
End While
' close the connection and readers

Please let me know if you have any other concerns, or need anything else.

Sincerely,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

==================================================
For MSDN subscribers whose posts are left unanswered, please check this
document: http://blogs.msdn.com/msdnts/pages/postingAlias.aspx

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications. If you are using Outlook Express/Windows Mail, please make sure
you clear the check box "Tools/Options/Read: Get 300 headers at a time" to
see your reply promptly.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
thanks.
Hello cj,

From your post, my understanding on this issue is: you wonder how to update
the rows in Excel according to the query in Database with customer No. If
I'm off base, please feel free to let me know.

According to your pseudo code:
update excel set excel.street=sql.street set excel.city=sql.city set
excel.state=sql.state set excel.zip=sql.zip where excel.cust_no=sql.cust_no
I think we could follow the logic below:
1. Use a OleDbDataReader object to read the customer no. in excel one by
one.
2. For the current customer number read by the OleDbDataReader object, use
a SqlDataReader object to query the other customer information from Database
3. Use a OleDbCommand object to update the row in Excel.

Here are some pseudo codes for your reference:
' 1. Use a OleDbDataReader object to read the customer no. in excel one by
one.
' create a OleDbCommand object here: cmd
cmd.Connection = excelConn ' the excel connection
cmd.CommandText = "SELECT custom_no FROM [Sheet1$]"
' create a OleDbDataReader object: cuNoReader
cuNoReader = cmd.ExecuteReader()

' 2. For the current customer no read by the OleDbDataReader object, use a
SqlDataReader
' object to query the other customer information from Database
' create a SqlCommand object here: sqlCmd
sqlCmd.Connection = sqlConn ' the sql connection
sqlCmd.CommandText = "SELECT * FROM [CUTable] WHERE custom_no=@custom_no"
sqlCmd.Parameters.Add("@custom_no", SqlDbType.Int)

' 3. Use a OleDbCommand object to update the row in Excel.
' create a OleDbCommand object here: updateCmd
updateCmd.Connection = excelConn ' the excel connection
updateCmd.CommandText = "UPDATE [Sheet1$] SET street=@street,city=@city
WHERE custom_no=@custom_no"
updateCmd.Parameters.Add("@street", OleDbType.VarChar, 255)
updateCmd.Parameters.Add("@city", OleDbType.VarChar, 255)
updateCmd.Parameters.Add("@custom_no", OleDbType.Integer)

While cuNoReader.Read() ' read the customer number one by one
Dim cu_no as integer = (int)cuNoReader["custom_no"] // get the
customer no
' read the customer info for the customer from sql db
sqlCmd.Parameters["@custom_no"].Value = cu_no
Dim sqlReader as SqlDataReader = sqlCmd.ExecuteReader();
if sqlReader.Read() then
' update the row in the excel
updateCmd.Parameters["@custom_no"].Value = cu_no
updateCmd.Parameters["@street"].Value =
sqlReader["street"].ToString()
updateCmd.Parameters["@city"].Value = sqlReader["city"].ToString()
updateCmd.ExecuteNonQuery()
End If
sqlReader.Close()
End While
' close the connection and readers

Please let me know if you have any other concerns, or need anything else.

Sincerely,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

==================================================
For MSDN subscribers whose posts are left unanswered, please check this
document: http://blogs.msdn.com/msdnts/pages/postingAlias.aspx

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications. If you are using Outlook Express/Windows Mail, please make sure
you clear the check box "Tools/Options/Read: Get 300 headers at a time" to
see your reply promptly.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Click to expand...
 
Back
Top