mvc - OleDB y tipos de datos mixtos de Excel: datos faltantes

export datatable to excel c# windows forms

Question

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 ########## n######### .

Traté de dejar que OleDB manejara todo automáticamente, leyéndolo en un conjunto de datos / tabla de datos, pero cualquier valor en ''ProductID'' como n###### falta, se ignora y se deja en blanco. Intenté crear manualmente mi DataTable al recorrer cada fila con un lector de datos, pero con 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 en valores como n###### . ¿Cómo puedo hacer esto?

Answer 1

@Brian Wells Gracias, tu sugerencia hizo el truco, pero no totalmente ... Funcionó para el campo mixto int-string, pero las columnas de fecha y hora fueron con caracteres extraños después de eso, así que apliqué un "hack" sobre el "hack" .

1.- Realice un System.Io.File.Copy y cree una copia del archivo de Excel.

2.- Modifique los encabezados de las columnas de fecha y hora programáticamente en el tiempo de ejecución a algo en formato de fecha y hora, es decir, "01/01/0001".

3.- Guarde el excel, y luego aplique su truco haciendo la consulta con HDR = NO al archivo modificado.

Difícil, sí, pero funcionó, y razonablemente rápido, si alguien tiene alguna alternativa a esto, estaré encantado de escuchar.

Saludos.

PD Disculpe mi inglés, no es mi lengua materna.

Answer 2

Hay dos formas de manejar tipos de datos mixtos y excel.

Método 1

• Abra su hoja de cálculo de Excel y configure manualmente el formato de columna en el formato deseado. En este caso, ''Texto''.

Método 2

• Hay un "truco" que consiste en agregar "IMEX = 1" a su cadena de conexión de la siguiente manera:

  Proveedor = Microsoft.Jet.OLEDB.4.0; Fuente de datos = miarchivo.xls; Propiedades extendidas = Excel 8.0; IMEX = 1

• Esto intentará manejar formatos mixtos de Excel según cómo esté configurado en su registro. Esto puede establecerlo usted localmente, pero para un servidor, probablemente esta no sea una opción.

Answer 3

No hay problema sh4, me alegro de que ayude con el problema de tipo mixto.

La columna DateTime es un animal completo que recuerdo que me causó dolor en el pasado ... tenemos un archivo de Excel que procesamos que OleDbDataAdapter a veces convertirá las fechas a un tipo de datos doble (aparentemente Excel almacena fechas como dobles, que codifican el número de días transcurridos desde el 0 de enero de 1900).

La solución fue usar:

OleDbConnection mobjExcelConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtExcelFile.Text + @";Extended Properties=""Excel 8.0;IMEX=1;HDR=Yes;""");

OleDbDataAdapter mobjExcelDataAdapter = new OleDbDataAdapter("Select * from [" + txtSheet.Text + "$] where [Supplier ID] <> '''' ", mobjExcelConn);


DateTime dtShipStatus = DateTime.MinValue;
shipStatusOrig = excelRow["Est Ship Date"].ToString(); // excelRow is DataRow in the DataSet via the OleDbDataAdapter             

if (shipStatusOrig != string.Empty)
{
    // Date may be read in via oledb adapter as a double
    if (IsNumeric(shipStatusOrig))
    {
        double d = Convert.ToDouble(shipStatusOrig);
        dtShipStatus = DateTime.FromOADate(d);

        if (DateTime.TryParse(dtShipStatus.ToString(), out dtShipStatus))
        {
            validDate = true;
            Debug.WriteLine("{0} converted: ", dtShipStatus.ToString("s"));
        }
    }
    else
    {
        if (ValidateShipDate(shipStatusOrig))
        {
            dtShipStatus = DateTime.Parse(shipStatusOrig);
            validDate = true;
            Debug.WriteLine("{0} converted: ", dtShipStatus.ToString("s"));
        }
        else
        {
            validDate = false;
            MessageBox.Show("Invalid date format in the Excel spreadsheet./nLine # " + progressBar1.Value + ", the ''Ship Status'' value ''" + shipStatusOrig + "'' is invalid./nDate should be in a valid date time format./ne.g. M/DD/YY, M.D.Y, YYYY-MM-DD, etc.", "Invaid Ship Status Date");
        }
    }
...
}
        public static Boolean IsNumeric (Object Expression)
        {
            if(Expression == null || Expression is DateTime)
                return false;

            if(Expression is Int16 || Expression is Int32 || Expression is Int64 || Expression is Decimal || Expression is Single || Expression is Double || Expression is Boolean)
                return true;

            try
            {
                if(Expression is string)
                    Double.Parse(Expression as string);
                else
                   Double.Parse(Expression.ToString());
                return true;
            } catch {} // just dismiss errors but return false

            return false;
        }

        public bool ValidateShipDate(string shipStatus)
        {
            DateTime startDate;
            try
            {
                startDate = DateTime.Parse(shipStatus);
                return true;
            }
            catch
            {
                return false;
            }
        }

Answer 4

Usando .Net 4.0 y leyendo archivos de Excel, tuve un problema similar con OleDbDataAdapter , es decir, leyendo en un tipo de datos mixto en una columna "PartID" en MS Excel, donde un valor PartID puede ser numérico (por ejemplo, 561) o texto (por ej. HL4354 ), aunque la columna de Excel se formateó como "Texto".

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

Mi solución fue establecer OleDbConnection connectionstring para usar Extended Properties=IMEX=1;HDR=NO para indicar que esto es una Importación y que la tabla (s) no incluirá encabezados. El archivo de Excel tiene una fila de encabezado, por lo que en este caso, indique 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 voilà ha mezclado el tipo de datos 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")
                });

Answer 5

Varios foros que encontré afirman que al agregar IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text to the Extended Properties en la cadena de conexión solucionaría el problema, pero este no era 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, 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;
    }

Answer 6

Acceso directo -> si tienes una columna de tipo mixto en Excel: ordena tu columna de Z a A

Revisé todas las respuestas aquí y algunas funcionaron para mí y otras no, sin embargo, ninguna era deseable para mí porque de alguna manera ADO no eligió los datos en una columna de tipo mixto que tenía en mi archivo de Excel. Tuve que configurar HDR=NO para que ADO leyera mi columna de hoja de cálculo que es una mezcla de texto y números y de esa manera pierdo la capacidad de usar encabezados de columna en mis declaraciones de SQL, lo que no es bueno. Si el orden de las columnas cambia en el archivo de Excel, la instrucción SQL dará como resultado un error o una salida incorrecta.

En una columna de tipo de datos mixtos, la clave son las primeras 8 filas. ADO determina el tipo de datos para la columna en base a las primeras 8 filas Por lo tanto, si aún desea modificar su cadena de conexión con los parámetros extendidos, simplemente ordene su columna Z a A en su archivo Excel antes de leer los datos por ADO para que así las filas en la parte superior son las de texto y luego su columna se seleccionará como texto.

Si sus filas iniciales son números (independientemente de si su columna está configurada para formatear TEXTO en Excel), ADO determinará esas columnas como un tipo numérico, por lo que una vez que lea las filas de texto a continuación, no podrá convertirlas en números. En el caso opuesto, si la columna es texto determinado, si hay una fila si número, se puede convertir como texto.