CODEWITHSUNDEEP
asp.netvb.netexceloledb
Mudassir Hasan
Mudassir Hasan

Reputation: 28741

NULL being inserted for column values when importing from Excel to DataSet

I am importing excel data to a datatable in my application and facing problem for some particular column values.

Some cells in a excel sheet column CustomerUniqID show warning with green mark in corner.

The number is formatted as text or preceded by apostrophe.

These cells value are not imported and show blank value when dataset is filled from Excel sheet.

Dim query As String = "SELECT CINT(CustomerUniqID),[Status] FROM [Sheet1$]"
Dim conn As New OleDbConnection(conStr)
If conn.State = ConnectionState.Closed Then
   conn.Open()
End If
Dim cmd As New OleDbCommand(query, conn)
Dim da As New OleDbDataAdapter(cmd)
Dim ds As New DataSet()
da.Fill(ds)

My connection string is

<add name ="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=2'"/>

The column CustomerUniqID contains numbers and I am unable to import these cells value. How to do this ?

Upvotes: 1

Views: 7116

Answers (6)

Marcin - user2676388
Marcin - user2676388

Reputation: 66

I came across same issue and almost gave up , but I tried this:

Extended Properties=\"Excel 12.0 Xml;HDR=Yes;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=0\"";

and it worked. This one is from Jet.OLEDB but it works with ACE.

"Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows". That's the key to not letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows. This might hurt performance. Please also note that adding the IMEX=1 option might cause the IMEX feature to set in after just 8 rows. Use IMEX=0 instead to be sure to force the registry TypeGuessRows=0 (scan all rows) to work."

Taken from https://www.connectionstrings.com/excel/

Upvotes: 1

Nima Derakhshanjan
Nima Derakhshanjan

Reputation: 1404

i faced this issue before,and the only way could solve the issue was converting cells to number,but not from format's menu ! I did it like below image

enter image description here

have a look at this link please,hope it helps

Upvotes: 1

Ňɏssa P&#248;ngjǣrdenlarp
Ňɏssa P&#248;ngjǣrdenlarp

Reputation: 38865

The only way I could get it to fail as described in the original post, is if the escaped/text cells were further down than what I was originally testing. It is too bad that OleDB wont use a Schema.ini with an excel file because that would allow a very clean and simple solution, alas...

Sample data used:

Country    Capital     Population   Rank
France     Paris       2.25         7
Canada     Toronto     2.5          6
Egypt      Cairo       10.2         9
...

It actually uses 16 rows with the last 3 "Rank" items being escaped as text (e.g. '2). These all show the green corner warning sign in Excel.

Since OleDB does not read/use a Schema, it decides the data type for each column from the first N rows (defined as 8 in my registry). When the escaped cells do not match that, it returns a DBNull value. Attempts to convert the column via SQL (CInt, Val) fail because OleDB has already decided that the data there does not match before the conversion can be applied.

In some cases, I would read the sheet in twice. First getting the "good" columns in their proper datatype into one DataTable; then again getting the 'dirty' column as text and manually converting the data. This would be useful in cases where there are other numeric columns are in the data set and you dont want them to be converted to text/string.

For the case posted, if there really are only 2 columns involved, you should be able to use one table read in as text; and add a numeric column to receive the converted value. Rather than converting from one table to another, convert from one column to another. (Just ask, if you want an example, but it is just a subset of the following).

In either case, the "trick" is to use a different connection string to force OleDB to read the data as text. Apparently both HDR=No and IMEX=1 are needed for this, at least with my config:

Dim TextConStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Temp\capitals.xls;Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"

This example/text code uses the 2 table approach to verify that other numerics (Population) are not converted, just Rank:

' ConStr to allow OleDB to guess the datatypes   
Dim TypedConStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Temp\capitals.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=2';"

' ConStr to force OleDB to read it all as Text 
Dim TextConStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Temp\capitals.xls;Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"

' get the typed columns into a DT - skip Rank as dirty column
Dim SQL = "SELECT Country, Capital, Population FROM [Capitals$]"
Using con As New OleDbConnection(TypedConStr),
    da As New OleDbDataAdapter(SQL, con)

    dsPop.Tables.Add("Pop")
    da.Fill(dsPop.Tables("Pop"))
End Using

' create a new temp DT containing just the naughty column
' use the generic F/Field index in the SQL (we told Ole there was no header)
SQL = "SELECT F4 As RankText FROM [Capitals$]"
' create connection forcing the contents to text:
Using con As New OleDbConnection(TextConStr),
    da As New OleDbDataAdapter(SQL, con)

    dsPop.Tables.Add("RankText")
    da.Fill(dsPop.Tables("RankText"))
End Using
' remove the header row
dsPop.Tables("RankText").Rows.RemoveAt(0)

'create a new INT col in Dt(0)
dsPop.Tables("Pop").Columns.Add("Rank", GetType(Int32))

' convert Tbl(1) text to Int and store in Tbl(0) 
For n As Integer = 0 To dsPop.Tables(1).Rows.Count - 1
    dsPop.Tables("Pop").Rows(n).Item("Rank") = 
           Convert.ToInt32(dsPop.Tables("RankText").Rows(n).Item(0).ToString)
Next

'optional: remove the [RankText] tbl since we are done with it
dgv.DataSource = dsPop.Tables("Pop")

' report the datatype of the last row rank:
tbDataType.Text = dsPop.Tables("Pop").Rows(14).Item("Rank").GetType.ToString

In the immediate window, the types reported are as expected:

? dspop.Tables("Pop").Rows(0).Item(2)       ' (population - paris)
2.25 {Double}
? dspop.Tables("RankText").Rows(0).Item(0)  ' temp table text
"7" {String}
? dspop.Tables("Pop").Rows(0).Item(3)       ' converted, merged value
7 {Integer}

For me, OleDB is automatically converting '3 to "3". In other words, it is omitting the leading tick/apostrophe when it converts to text. Since there can be numerous possibilities resulting from the combinations of Excel versions and OleDB.ACE and OleDb.Jet, we might want a fall back converter (I wrote this after adding back ticks to Excel by accident, maybe it will of value to someone):

Private Function GetNumericValue(s As String) As Integer
    ' ToDo add exception handling
    If Char.IsDigit(s(0)) Then
        Return Convert.ToInt32(s)
    Else
        Return Convert.ToInt32(
            New String(s.ToCharArray(1, s.Length - 1))
            )
    End If
End Function

It will only examine the first char for a non-numeral, otherwise it might convert "1234 Main Street Suite 56" to 123456 which is likely not desirable. The result:

enter image description here
Russia, Japan and Portugal were the rows which had the Rank data escaped as text.

Resources:

Upvotes: 6

John Smith
John Smith

Reputation: 7407

I am familiar with this error that you are talking about, "The number is formatted as text or preceded by apostrophe." There is an application I use on my job that stores all their data as text values, and when I export to excel every numeric field has this happen to it. It has to do with the underlying type of the data. It doesn't matter if you type in a number (ex. 123) into a cell (or even into a database for that matter); what matters is the type that it is being interpreted as. So 123 <> "123" (which is a string). The issue is likely that the database you are trying to load the data into has the field with a numeric type (ex. int) but the program importing the data does not recognize it as a numeric, but instead a text value; thus it is throwing NULLS to compensate.

But this is good in some cases, for example, do any of these numbers have leading zeros? If so, You would WANT to store them as Text values to preserve the leading zeros. If not, a work around would be to (in excel 2010) go to the Data tab => text to columns, and then run through the wizard to get the correct data type. This essentially parses the values. It does not look like the problem is with your connection if everything else is loading correctly. This should be fixable directly in Excel.

Upvotes: 1

A Beginner
A Beginner

Reputation: 447

The issue may be due to you tried to convert a nonnumeric value to an integral value. For expression, if we are use following expression: =CInt(“ABC”), we would get the #Error value in our report.

Please refer to the custom code below to work around the issue:

Public Function Conv(ByVal A as String) 
Dim B as string
Dim C as Integer
If isnumeric(A)  Then
C=CInt(A)
Return C
else
B=CStr(A)
Return B
End If 
End Function

Upvotes: 0

TGlatzer
TGlatzer

Reputation: 6248

Your problem is the data access to excel. The jet engine interprets the column data by the first 8 (or something similar) rows. So if the first 8 cells (excluding headers) are numbers, the jet engine will assume a number type for that column. Every following cell, which does not match that data-type will be NULL.

You can find more information here: I need a workaround for Excel Guessing Data Types problem

Upvotes: 0

Related Questions