Is there a better way to specify "null" values ​​in Excel?

Question

I have an Excel 2007 workbook that contains data tables that I import into DataTable objects using ADO.NET.

In some experiments, I was able to find two different ways to indicate that ADO.NET should be considered as "null":

• The cell is completely empty.
• Cell contains #N/A

Unfortunately, both of these problems are problematic:

• Most of my data columns in Excel are generated using formulas, but in Excel it is not possible to create a formula that results in a completely empty cell. And only a complete empty cell will be considered zero (an empty row will not work).

• Any formula that evaluates to #N/A (either due to an actual search error or due to the use of the NA() function) will be considered zero. This seemed like the perfect solution until I discovered that the Excel workbook must be open for this to work. As soon as you close the book, OLEDB suddenly starts to see all these #N/A as lines. This causes the following exceptions to be thrown when populating the DataTable:

  The input string was not in the correct format. Failed to save <# N / A> in Value column. The expected type is Int32.

Question: How can I specify a null value through an Excel formula without having to open a workbook when I populate a DataTable ? Or what can be done to make #N/A values ​​be considered zero even when the workbook is closed?

In case this is important, my connection string is constructed using the following method:

 var builder = new OleDbConnectionStringBuilder { Provider = "Microsoft.ACE.OLEDB.12.0", DataSource = _workbookPath }; builder.Add("Extended Properties", "Excel 12.0 Xml;HDR=Yes;IMEX=0"); return builder.ConnectionString; 

( _workbookPath is the full path to the book).

I tried both IMEX=0 and IMEX=1 , but that doesn't matter.

Answer 1

You are faced with the brickwork experienced by many very frustrated Excel users. Unfortunately, Excel as a company tool is widespread and seems quite reliable, unfortunately, because each cell / column / row has a variant data type, which makes it a nightmare for processing by other tools such as MySQL, SQL Server, R, RapidMiner, SPSS and the list goes on. It seems that Excel 2007/2010 is not very well supported, and even more so when using 32/64 bit versions, which today is scandalous.

The main problem is that when ACE / Jet accesses each field in Excel, they use the "TypeGuessRows" registry setting to determine the number of rows to evaluate the data type. The default value for Scan Lines is 8 lines. The TypeGuessRows registry entry can specify an integer value from one (1) to sixteen (16) lines, or you can specify zero (0) to scan all existing lines. If you cannot change the registry settings (for example, in 90% of office environments), this makes life difficult, since guessing lines are limited to the first 8.

For example, without changing the registry If the first occurrence of # N / A is within the first 8 lines, IMEX = 1 will return an error in the form of the string "# N / A". If IMEX = 0, then # N / A will return "Null".

If the first occurrence of # N / A goes beyond the first 8 lines, then both IMEX = 0 and IMEX = 1 return "Null" (provided that the required data type is numeric).

With a registry change (TypeGuessRows = 0) everything should be fine.

There are probably 4 options:

• Change the registry setting TypeGuessRows = 0

• List all possible types in the first 8 lines as “dummy data” (for example, memo / nchar (max) fields / # N / A errors, etc.)

• Fix all data type anomalies in Excel

• Do not use Excel - seriously worth considering!

Edit: Just put the boot in :) two more things that really annoy me; if the first field on the sheet is empty in the first 8 lines, and you cannot edit the registry settings, then the whole sheet is returned as empty (there are a lot of funny conversations that tell managers that they are fools to merge cells!). In addition, if in Excel 2007/2010 you have a department that returns a sheet s> 255 columns / fields, you will have huge problems if you need non-contiguous imports (for example, the key in column 1 and the data in columns 255+)