我需要一个解决方法Excel的猜测数据类型问题 [英] I need a workaround for Excel Guessing Data Types problem

问题描述

我要创建一个实用程序来从Excel导入数据到Oracle数据库，

I'm creating a utility to import data from Excel to Oracle database,

我有一个固定的模板的Excel文件，

I have a fixed template for the excel file,

现在，当我试图通过Jet提供和ADO.Net导入数据 - 奥莱连接的工具，我发现了以下问题：有是一些列尚未导入，因为在混合数据类型的列[字符串和数字]

Now, when I'm trying to import the data by Jet provider and ADO.Net - Ole connection tools, I found the following problem: there're some columns haven't been imported because there are mixed data types in their columns [string and number],

我看着在互联网上这个问题，我找到了原因是从Excel 猜测数据类型

I looked for this problem on the internet I found the reason is guessing data types from Excel

负载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);

我应该怎么做，告诉Excel中不必猜测，给我数据，文本？

What should I do to tell Excel STOP GUESSING and give me the data as Text ?

如果没有，你能帮助我与任何变通办法？

if there isn't, can you help me with any workarounds ?

在此先感谢

推荐答案

我找到了一个解决方案，通过增加IMEX = 1的连接字符串，但有它特殊的格式，descriped以下的link 。

I found a solution by adding IMEX=1 for the connection string, but there's a special format for it which descriped in the following link.

该IMEX参数是使用混合的数字和字母值的列。   Excel驱动程序通常会扫描前几行   为了确定用于每个列的数据类型。如果列被确定为数字   基于第一多个行的扫描，则任何与字母字符在此列的行会   返回为空。所述IMEX参数（1是输入模式）强制列到数据类型   文本，以便字母数字值妥善处理。

The IMEX parameter is for columns that use mixed numeric and alpha values. The Excel driver will typically scan the first several rows in order to determine what data type to use for each column. If a column is determined to be numeric based upon a scan of the first several rows, then any rows with alpha characters in this column will be returned as Null. The IMEX parameter (1 is input mode) forces the data type of the column to text so that alphanumeric values are handled properly.

问候

这篇关于我需要一个解决方法Excel的猜测数据类型问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！