OleDB y tipos de datos mixtos de Excel: datos faltantes

Tengo una hoja de cálculo de Excel que quiero leer en una tabla de datos; todo está bien excepto una columna en particular en mi hoja de Excel. La columna 'ProductID' es una combinación de valores como ##########y n#########.

Intenté dejar que OleDB manejara todo por sí solo automáticamente leyéndolo en un conjunto de datos/tabla de datos, pero n######faltan valores en 'ProductID', se ignoran y se dejan en blanco. Intenté crear manualmente mi DataTable recorriendo cada fila con un lector de datos, pero obtuve exactamente los mismos resultados.

Aquí está el código:

// add the column names manually to the datatable as column_1, column_2, ...
for (colnum = 0; colnum < num_columns; colnum ++){
  ds.Tables["products"].Columns.Add("column_" +colnum , System.Type.GetType("System.String")); 
}
while(myDataReader.Read()){
  // loop through each excel row adding a new respective datarow to my datatable 
  DataRow a_row = ds.Tables["products"].NewRow();
  for (col = 0; col < num_columns; col ++){
    try {  a_row[col] = rdr.GetString(col);  }
    catch {  a_row[col] = rdr.GetValue(col).ToString(); }
  }
  ds.Tables["products"].Rows.Add(a_row);
}

No entiendo por qué no me deja leer valores como n######. ¿Cómo puedo hacer esto?

Jul 13 '10 04:07 rlb.usa

Al usar .Net 4.0 y leer archivos de Excel, tuve un problema similar, es decir, al OleDbDataAdapterleer un tipo de datos mixto en una columna "PartID" en MS Excel, donde un valor de PartID puede ser numérico (por ejemplo, 561) o texto (por ejemplo, HL4354). , aunque la columna de Excel tenía el formato "Texto".

Por lo que puedo decir, ADO.NET elige el tipo de datos en función de la mayoría de los valores de la columna (con un vínculo con el tipo de datos numéricos). es decir, si la mayoría de los PartID en el conjunto de muestra son numéricos, ADO.NET declarará que la columna es numérica. Por lo tanto, ADO.Net intentará convertir cada celda a un número, lo que fallará para los valores de PartID de "texto" y no importará esos PartID de "texto".

Mi solución fue configurar la OleDbConnectioncadena de conexión que se utilizará Extended Properties=IMEX=1;HDR=NOpara indicar que se trata de una importación y que las tablas no incluirán encabezados. El archivo de Excel tiene una fila de encabezado, por lo que en este caso dígale a ado.net que no lo use. Luego, más adelante en el código, elimine esa fila de encabezado del conjunto de datos y listo, tendrá tipos de datos mixtos para esa columna.

string sql = "SELECT F1, F2, F3, F4, F5 FROM [sheet1$] WHERE F1 IS NOT NULL";

OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PrmPathExcelFile + @";Extended Properties=""Excel 8.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text""");

OleDbCommand cmd = new OleDbCommand(sql, connection);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);

DataSet ds = new DataSet();
ds.Tables.Add("xlsImport", "Excel");
da.Fill(ds, "xlsImport");

// Remove the first row (header row)
DataRow rowDel = ds.Tables["xlsImport"].Rows[0];
ds.Tables["xlsImport"].Rows.Remove(rowDel);

ds.Tables["xlsImport"].Columns[0].ColumnName = "LocationID";
ds.Tables["xlsImport"].Columns[1].ColumnName = "PartID";
ds.Tables["xlsImport"].Columns[2].ColumnName = "Qty";
ds.Tables["xlsImport"].Columns[3].ColumnName = "UserNotes";
ds.Tables["xlsImport"].Columns[4].ColumnName = "UserID";

connection.Close();

// ahora puedes usar LINQ para buscar los campos

    var data = ds.Tables["xlsImport"].AsEnumerable();
    var query = data.Where(x => x.Field<string>("LocationID") == "COOKCOUNTY").Select(x =>
                new Contact
                {
                    LocationID= x.Field<string>("LocationID"),
                    PartID = x.Field<string>("PartID"),
                    Quantity = x.Field<string>("Qty"),
                    Notes = x.Field<string>("UserNotes"),
                    UserID = x.Field<string>("UserID")
                });

Apr 19 '2011 19:04 Brian Wells

Varios foros que encontré afirman que agregar IMEX=1;TypeGuessRows=0;ImportMixedTypes=Textpropiedades extendidas en la cadena de conexión solucionaría el problema, pero este no fue el caso. Finalmente resolví este problema agregando "HDR=NO" a las Propiedades extendidas en la cadena de conexión (como muestra Brian Wells arriba) para poder importar tipos mixtos.

Luego agregué un código genérico para nombrar las columnas después de la primera fila de datos y luego eliminé la primera fila.

    public static DataTable ImportMyDataTableFromExcel(string filePath)
    {
        DataTable dt = new DataTable();

        string fullPath = Path.GetFullPath(filePath);

        string connString =
           "Provider=Microsoft.Jet.OLEDB.4.0;" +
           "Data Source=\"" + fullPath + "\";" +
           "Extended Properties=\"Excel 8.0;HDR=No;IMEX=1;\"";

        string sql = @"SELECT * FROM [sheet1$]";

        using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sql, connString))
        {
            dataAdapter.Fill(dt);
        }

        dt = BuildHeadersFromFirstRowThenRemoveFirstRow(dt);

        return dt;
    }

    private static DataTable BuildHeadersFromFirstRowThenRemoveFirstRow(DataTable dt)
    {
        DataRow firstRow = dt.Rows[0];

        for (int i = 0; i < dt.Columns.Count; i++)
        {
            if(!string.IsNullOrWhiteSpace(firstRow[i].ToString())) // handle empty cell
              dt.Columns[i].ColumnName = firstRow[i].ToString().Trim();
        }

        dt.Rows.RemoveAt(0);

        return dt;
    }

Aug 21 '2012 21:08