VB.net reading Excel page

Question

I am trying to read an Excel page but I get the following error:

Input string was not in a correct format.Couldn't store <555-555-5555> in PHONE_NUM Column. Expected type is Double.

It seems that the oledb command is expecting the column to be of type double (but, in fact, it is a string for phone numbers). I think I read somewhere that this happens because the command looks as the first couple of items in the column and determines what the type should be based off of that. Is there anyway to always read all values in as a string?

This is the code I am using:

Dim connection As OleDb.OleDbConnection
Dim command As OleDb.OleDbCommand
Dim adapter As New OleDb.OleDbDataAdapter
Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + myFile + ";Extended Properties=Excel 12.0;"
Dim pgOne As String = "Page1$"
Dim pgTwo As String = "Page2$"
Dim selectOne As String = "SELECT * FROM [" & pgOne & "] WHERE COL1<>''"
Dim selectTwo As String = "SELECT * FROM [" & pgTwo & "]"

connection = New OleDb.OleDbConnection(connString)
Try
   If connection.State = ConnectionState.Open Then
   Else
      connection.Open()
   End If

   command = New OleDb.OleDbCommand(selectOne, connection)
   adapter.SelectCommand = command
   adapter.Fill(ds, "Table1")

   adapter.SelectCommand.CommandText = selectTwo
   adapter.Fill(ds, "Table2")
Catch ex As OleDb.OleDbException
Finally
   adapter.Dispose()
   If (Not command Is Nothing) Then
      command.Dispose()
   End If
   connection.Close()
End Try

score 0 · Answer 1 · answered Jun 25 '13 at 11:32

0

You can get the tables to fill as strings in one of two ways. If you want to read them in as only a string you can add a part to your connection string that should read intermixed datatype columns as text. Alternatively force them into string types by reading in a header row as data. You can also use schemas to read in table definitions but I would need to find a little more info on that if these don't work for you.

The first option is simpler to do:

First change your connection string to add IMEX=1

'Add IMEX=1
Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;IMEX=1;Data Source=" + myFile + ";Extended Properties=Excel 12.0;"

This should then provide you with String based columns on any datacolumns that have mixed data types in their rows.

Another way to do it is force the string column type by reading the header row as a datarow by specifying there is no header row in your connection string. You can then remove this after to have a string column of data.

'Add HDR=NO
Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;HDR=NO;Data Source=" + myFile + ";Extended Properties=Excel 12.0;"

I will research a little more if these don't work for you.

answered Jun 25 '13 at 11:32

Tom 'Blue' Piddock

2,131
1
21
36

Thanks for the response. I tried both suggestions but get the same error "Could not find installable ISAM." when trying to open the connection. Any thoughts? – jason Jun 25 '13 at 11:44
1

Well, the ISAM error was fixed (http://stackoverflow.com/questions/8120699/could-not-find-installable-isam) but I am still having issues. Using IMEX=1 does not work. Trying to use HDR=NO causes another error "No value given for one or more required parameters." – jason Jun 25 '13 at 11:54
try IMEX=1;HDR=NO; together - still no dice then I'll find out about the Schema side of things – Tom 'Blue' Piddock Jun 25 '13 at 12:23
Yeah, that didnt work. I ended up just doing a work around, where I create the table columns manually. Ill post what i did in the original question. Thanks for the help, though! – jason Jun 25 '13 at 12:56
Ah so you created the Schema first :P I didn't manage to get on my lunch break to do it. Good job. – Tom 'Blue' Piddock Jun 25 '13 at 15:21

score 0 · Accepted Answer · answered Jul 25 '13 at 15:35

0

As mentioned, I was unable to fix this so I just ended up creating a schema manually and formatting the data as it was bound.

answered Jul 25 '13 at 15:35

jason

3,821
10
63
120

VB.net reading Excel page

2 Answers2