VB.NET Windows Form Reading Excel dumping data into SQL server
-
I situation is I need to pull the data from an Excel Spreadsheet and Insert it into a SQL Server DB. connecting excel via OleDb connection: strExCN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""" which is connecting as it should. I hope someone can lend a hand on this. I can open the excel file and begins to read but when I come across an empty file I get a dbNull error. I have changed my select statement from "select * from [worksheet$]" to "select isnull(ColumnName, '') from [worksheet$]" and now am receiving and error saying OleDbException was unhandled Wrong number of arguments used with function in query expression 'IsNull('Column Name', '')'. Any help on this error would be wonderful. Thanks in advance culby sl
-
I situation is I need to pull the data from an Excel Spreadsheet and Insert it into a SQL Server DB. connecting excel via OleDb connection: strExCN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""" which is connecting as it should. I hope someone can lend a hand on this. I can open the excel file and begins to read but when I come across an empty file I get a dbNull error. I have changed my select statement from "select * from [worksheet$]" to "select isnull(ColumnName, '') from [worksheet$]" and now am receiving and error saying OleDbException was unhandled Wrong number of arguments used with function in query expression 'IsNull('Column Name', '')'. Any help on this error would be wonderful. Thanks in advance culby sl
culbysl wrote:
I can open the excel file and begins to read but when I come across an empty file I get a dbNull error.
When you come acrossed an empty Excel FILE?? Or Cell?? If it's an empty file, you can just capture the error in a Try/Catch block and move on:
' Setup connection stuff...
' Setup your loop, enumerating your files...
Try
' Open the connection
' Execute the SELECT statement
Catch ex As Exception
' Handle the emtpy file problem here...
Finally
If connection is not closed then
Close connection
End Try
' Loop around to process the next file...culbysl wrote:
Wrong number of arguments used with function in query expression 'IsNull('Column Name', '')'.
You can't use Excel functions in the SELECT statement because Excel is not doing the processing here. You should either stick with the * or specify the columns you're actually goint to use. Now, if your code is bombing try to use a DBNull value from a cell, then your code has to check to see if that value is DBNull before trying to use it. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome