I need a workaround for an Excel Guessing Data problem

Question

I am creating a utility to import data from Excel into an Oracle database,

I have a fixed template for an excel file,

Now, when I try to import data using the Jet provider and ADO.Net - Ole, I found the following problem: some columns were not imported because their mixed data types have mixed data types columns [row and number],

I searched this problem online. I found a reason to guess data types from Excel

Download Code:

connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0};Extended Properties=Excel 8.0;"); string columns = "P_ID, FULL_NAME_AR, job_no, GENDER, BIRTH_DATE, RELIGION, MARITAL_STATUS, NAT_ID, JOB_Name, FIRST_HIRE_DATE, HIRE_DATE, CONTRACT_TYPE, GRADE_CODE, QUALIFICATION"; string sheetName = "[Emps$]"; OleDbCommand command = new OleDbCommand(string.Format("select {0} from {1} where p_id is not null", columns, sheetName), connection); connection.Open(); dr = command.ExecuteReader(); DataTable table = new DataTable(); table.Load(dr); 

What should I do to tell Excel STOP GUESSING and provide me data as text?

If not, can you help me with any workarounds?

Thanks in advance

Answer 1

I found a solution by adding IMEX = 1 for the connection string, but there is a special format there that I described in the following link .

The IMEX parameter is for columns that use mixed numeric and alpha values. The Excel driver typically scans the first few rows to determine which data type to use for each column. If a column is determined to be numeric based on checking the first few rows, then any rows with alpha characters in that column will be returned as Null. The IMEX parameter (1 - input mode) forces the data type of the column to text so that the alphanumeric values ​​are processed properly.

Hi

Answer 2

This is not entirely correct! Obviously, Jet / ACE ALWAYS takes a string type if the first 8 lines are empty, regardless of IMEX = 1, and always uses a number type if the first 8 lines are numbers (again, regardless of IMEX = 1). Even when I made entries read in the registry to 0, I still had the same problem. This was the only sure way to make it work:

 try { Console.Write(wsReader.GetDouble(j).ToString()); } catch //Lame unfixable bug { Console.Write(wsReader.GetString(j)); } 

Answer 3

Can you work with a great finish? In this example, running in Excel, the mixed data will be placed in a SQL Server table:

 Dim cn As New ADODB.Connection scn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _ & sFullName _ & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";" cn.Open scn s = "SELECT Col1, Col2, Col3 INTO [ODBC;Description=TEST;DRIVER=SQL Server;" _ & "SERVER=Some\Instance;Trusted_Connection=Yes;" _ & "DATABASE=test].TableZ FROM [Sheet1$]" cn.Execute s 

Answer 4

An alternative solution is to add or change the TypeGuessRows parameter in the registry. By setting its value to 0, the complete document will be checked.

Unfortunately, the settings can be found in different places of the registry, depending on which libraries and versions you installed them.

For example: [HKEY_LOCAL_MACHINE \ SOFTWARE \ Wow6432Node \ Microsoft \ Jet \ 4.0 \ Engines \ Excel] "TypeGuessRows" = dword: 00000000

[HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Office \ 14.0 \ Access Connectivity Engine \ Engines \ Excel] "TypeGuessRows" = dword: 00000000

It will also prevent truncation of text data longer than 255 characters. This happens if you have a number for TypeGuessRows greater than 0, and the first text longer than 255 characters occurs outside of this number.

See Also Setting TypeGuessRows for Excel ACE Driver .