by Paul Kohler
5. January 2010 19:47
We are managing lots of "lookup data" through a big fat Excel spreadsheet. We have a simple tool that reads the sheets and produces insert statements (using ADO.NET).
The problem was that some fields were coming through NULL when I knew they were not.The short answer is "IMEX=1"
Basically the provider looks at the first few rows and guesses the type from the cell values, in my case the first few were numbers followed by text codes. When the provider gets to the text values its in “number” mode so defaults to a NULL value.
You can use the "IMEX" property to make the provider look further and go into an import mode. See the KB article for more info.
http://support.microsoft.com/kb/194124
PK :-)
PS: To connect to an excel file within ADO.NET use the "System.Data.OleDb" provideran a connection string similar to:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source="foo.xls";Persist Security Info=False;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"
Subscribe