c# - registrado - Cómo forzar a ADO.Net a usar solo System.String DataType en los lectores TableSchema

oledbconnection c# excel

Question

Estoy usando OleDbConnection para consultar una hoja de cálculo de Excel 2007. Quiero forzar que OleDbDataReader use solo cadenas como el tipo de datos de columna.

El sistema está mirando las primeras 8 filas de datos e infiriendo que el tipo de datos es Doble. El problema es que en la fila 9 tengo una cadena en esa columna y el OleDbDataReader está devolviendo un valor nulo, ya que no se pudo convertir a un doble.

He usado estas cadenas de conexión:

Provider = Microsoft.ACE.OLEDB.12.0; Data Source = "ExcelFile.xlsx"; Persist Security Info = False; Propiedades extendidas = "Excel 12.0; IMEX = 1; HDR = No"

Provider = Microsoft.Jet.OLEDB.4.0; Data Source = "ExcelFile.xlsx"; Persist Security Info = False; Propiedades extendidas = "Excel 8.0; HDR = No; IMEX = 1"

Mirando al lector. GetSchemaTable (). Filas [7] .ItemArray [5], es dataType is Double.

La fila 7 en este esquema se correlaciona con la columna específica en Excel con la que estoy teniendo problemas. ItemArray [5] es su columna DataType

¿Es posible crear un TableSchema personalizado para el lector, así que al acceder a ExcelFiles, puedo tratar todas las celdas como texto en lugar de permitir que el sistema intente inferir el tipo de datos?

Encontré buena información en esta página: consejos para leer hojas de cálculo de Excel con ADO.NET

La principal peculiaridad de la interfaz ADO.NET es cómo se manejan los tipos de datos. (Notarás que he evitado cuidadosamente la cuestión de qué tipos de datos se devuelven al leer la hoja de cálculo.) ¿Estás listo para esto? ADO.NET escanea las primeras 8 filas de datos y, basándose en eso, adivina el tipo de datos para cada columna. ¡Luego intenta forzar todos los datos de esa columna a ese tipo de datos, devolviendo NULL cada vez que falla la coerción!

Gracias,
Keith

Aquí hay una versión reducida de mi código:

using (OleDbConnection connection = new OleDbConnection(BuildConnectionString(dataMapper).ToString()))
{
    connection.Open();
    using (OleDbCommand cmd = new OleDbCommand())
    {
        cmd.Connection = connection;
        cmd.CommandText = SELECT * from [Sheet1$];
        using (OleDbDataReader reader = cmd.ExecuteReader())
        {
            using (DataTable dataTable = new DataTable("TestTable"))
            {
                dataTable.Load(reader);
                base.SourceDataSet.Tables.Add(dataTable);
            }
        }
    }
}

Answer 1

Mira la respuesta final en esta página .

Acaba de notar que la página a la que se refiere dice lo mismo ...

Actualización :

El problema parece ser con el motor JET en sí mismo y no con ADO. Una vez que JET decide sobre el tipo, se adhiere a él. Cualquier cosa que se haga después de eso no tiene ningún efecto; como lanzar los valores a la cadena en el SQL (por ejemplo, Cstr ([Columna])) solo se devuelve una cadena vacía.

En este punto (si no hay otras respuestas) optaría por otros métodos: modificar la hoja de cálculo; modificar el registro (no es ideal, ya que te estarás metiendo con la configuración de cada otra aplicación que usa JET); Automatización de Excel o un componente de terceros que no usa JET.

Si la opción de Automatización es lenta, tal vez solo utilícela para guardar la hoja de cálculo en un formato diferente que sea más fácil de manejar.

Answer 2

Como habrás descubierto, OLEDB usa Jet, que está limitado en la manera en que puede modificarse. Si está configurado utilizando OleDbConnection para leer desde un archivo de Excel, debe establecer el valor HKLM/.../Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows en cero para que el sistema escanee todo el conjunto de resultados. .

Dicho esto, si está abierto a usar un motor alternativo para leer desde un archivo de Excel, puede considerar probar ExcelDataReader . Lee todas las columnas como cadenas, pero le permitirá usar los métodos dataReader.Getxxx para obtener valores escritos. Aquí hay una muestra que llena un DataSet :

DataSet result;
const string path = @"..../Test.xlsx";
using ( var fileStream = new FileStream( path, FileMode.Open, FileAccess.Read ) )
{
    using ( var excelReader = ExcelReaderFactory.CreateOpenXmlReader( fileStream ) )
    {
        excelReader.IsFirstRowAsColumnNames = true;
        result = excelReader.AsDataSet();
    }
}

Answer 3

Nota para el sistema operativo de 64 bits está aquí:

My Computer/HKEY_LOCAL_MACHINE/SOFTWARE/Wow6432Node/Microsoft/Jet/4.0/Engines/Excel

Answer 4

Me he enfrentado al mismo problema y he determinado que esto es algo que mucha gente experimenta comúnmente. Aquí hay una serie de soluciones que se han sugerido, muchas de las cuales he intentado implementar:

1. Agregue lo siguiente a su cadena de conexión ( Fuente ):

TypeGuessRows = 0; ImportMixedTypes = Texto

2. Agregue lo siguiente a su cadena de conexión ( Fuente , Más Discusión , Aún Más ):

IMEX = 1; HDR = NO;

3. Edite las siguientes configuraciones de registro, desactive "TypeGuessRows" e "ImportMixedTypes" en "Texto" ( Fuente , No recomendado , Más documentación ):

Hkey_Local_Machine / Software / Microsoft / Jet / 4.0 / Engines / Excel / TypeGuessRows Hkey_Local_Machine / Software / Microsoft / Jet / 4.0 / Engines / Excel / ImportMixedTypes

4. Considere usar una biblioteca alternativa para leer el archivo Excel:

   • EPPlus
   • ExcelDataReader (también sugerido sea @Thomas)
   • OpenXml

5. Formatee todos los datos en el archivo de origen como Texto (al menos las primeras 8 filas), aunque entiendo que normalmente no es práctico ( Fuente , aunque esto está relacionado con SSIS, pero son los mismos conceptos)

6. Use un archivo Schema.ini para definir el tipo de datos antes de importar el archivo, encontré esto en relación con el uso de "Jet.OleDb" directamente, tal vez requiriendo que modifique su cadena de conexión. Esto solo puede aplicarse a CSV. No he probado este enfoque. ( Fuente , Publicación relacionada )

Ninguno de estos me ha funcionado (aunque creo que han funcionado para otros). Soy de la opinión expresada por @Asher de que realmente no hay una buena solución para este problema. En mi software simplemente muestro un mensaje de error para el usuario (si alguna columna requerida contiene valores vacíos) indicándolos que formateen todas las columnas como "Texto" .

Honestamente, creo que este libro es más aplicable a la situación. El problema, ya mencionado varias veces es:

• "El tipo de datos en el destino es varchar, pero el tipo de datos supuesto de" doble "anula cualquier dato que no encaje." ( Fuente )

• "Pero el problema es realmente con el OLEDBDataReader. El problema es que si ve la mayoría de los números en una columna, asume que todo es un número: si un elemento de la fila que se lee no es un número, ¡simplemente lo establece en nulo! ¡Ay! "( Fuente )

• "El problema parece ser con el motor JET en sí mismo y no con ADO. Una vez que JET decide sobre el tipo, se adhiere a él". (@ Asher)

Aunque no he encontrado nada de esto documentado en una capacidad oficial, creo que está muy claro que se trata de una decisión de diseño intencional y simplemente cómo funciona la Biblioteca de base de datos de Jet . Dudo en llamar a esta biblioteca totalmente inútil porque creo que para muchas personas algunas de estas soluciones sí funcionan, pero hasta ahora para mi proyecto, he llegado a la conclusión de que esta biblioteca no puede leer múltiples tipos de datos en una sola columna y no es adecuada para la recuperación de datos generales.