OleDB e tipi di dati Excel misti: dati mancanti

Ho un foglio di lavoro di Excel che voglio leggere in un datatable – tutto va bene tranne che per una particolare colonna nel mio foglio Excel. La colonna “ProductID” è un insieme di valori come ########## e n######### .

Ho provato a permettere a OleDB di gestire tutto automaticamente da solo leggendolo in un dataset / datatable, ma tutti i valori in “ProductID” come n###### sono mancanti, ignorati e lasciati vuoti. Ho provato a creare manualmente il mio DataTable eseguendo il ciclo di ogni riga con un datareader, ma con gli stessi esatti risultati.

Ecco il codice:

 // 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); } 

Non capisco perché non mi permette di leggere valori come n###### . Come posso fare questo?

Utilizzando .Net 4.0 e leggendo i file di Excel, ho riscontrato un problema simile con OleDbDataAdapter , ovvero la lettura in un tipo di dati misto su una colonna “PartID” in MS Excel, dove un valore PartID può essere numerico (ad esempio 561) o testo (ad es. HL4354 ), anche se la colonna excel è stata formattata come “Testo”.

Da quello che posso dire, ADO.NET sceglie il tipo di dati basato sulla maggior parte dei valori nella colonna (con un legame che va al tipo di dati numerici). cioè se la maggior parte dei PartID presenti nel set di esempio sono numerici, ADO.NET dichiarerà la colonna come numerica. Pertanto, ADO.Net tenterà di trasmettere ogni cella a un numero, che non riuscirà per i valori PartID “testo” e non importerà quei PartID “di testo”.

La mia soluzione era quella di impostare la connessione di connessione OleDbConnection per utilizzare Extended Properties=IMEX=1;HDR=NO per indicare che si tratta di un’importazione e che le tabelle non includeranno le intestazioni. Il file excel ha una riga di intestazione, quindi in questo caso indica ad ado.net di non usarlo. Quindi, più avanti nel codice, rimuovi la riga di intestazione dal set di dati e voilà hai tipi di dati misti per quella colonna.

 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(); 

// ora puoi usare LINQ per cercare i campi

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

Diversi forum che ho trovato affermano che aggiungendo IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text nelle Proprietà estese nella stringa di connessione risolverebbe il problema, ma questo non era il caso. Ho finalmente risolto questo problema aggiungendo “HDR = NO” alle Proprietà estese nella stringa di connessione (come illustrato da Brian Wells sopra) in modo da poter importare tipi misti.

Ho quindi aggiunto un codice generico per denominare le colonne dopo la prima riga di dati, quindi rimuovere la prima riga.

  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; } 

Nessun problema sh4, lieto che aiuti w / il problema di tipo misto.

La colonna DateTime è tutto l’altro animale che ricordo mi ha causato dolore in passato … abbiamo un file excel che elaboriamo che OleDbDataAdapter a volte converte le date in un doppio tipo di dati (apparentemente Excel memorizza le date come doppie, che codificano il numero di giorni trascorsi dal gennaio 0, 1900).

La soluzione alternativa era quella di utilizzare:

 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, MDY, 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; } } 

Esistono due modi per gestire tipi di dati misti ed Excel.

Metodo 1

  • Apri il foglio di calcolo Excel e imposta manualmente il formato della colonna nel formato desiderato. In questo caso, “Testo”.

Metodo 2

  • C’è un “trucco” che consiste nell’aggiungere “IMEX = 1” alla tua stringa di connessione in questo modo:

    Provider = Microsoft.Jet.OLEDB.4.0; Origine dati = myfile.xls; Proprietà estese = Excel 8.0; IMEX = 1

  • Questo tenterà di gestire i formati misti di Excel in base a come è impostato nel registro. Questo può essere impostato localmente da te, ma per un server, questa probabilmente non è un’opzione.

@Brian Wells Grazie, il tuo suggerimento ha fatto il trucco, ma non totalmente … Ha funzionato per il campo misto int-string, ma le colonne datetime sono andate con caratteri strani dopo questo, quindi ho applicato un “hack” al “hack” .

1.- Fai un System.Io.File.Copy e crea una copia del file excel.

2.- Modificare le intestazioni delle colonne Datetime a livello di programmazione in fase di esecuzione su qualcosa in formato data / ora, ad esempio “01/01/0001”.

3.- Salvare l’Excel, quindi applicare il trucco eseguendo la query con HDR = NO nel file modificato.

Tricky, sì, ma ha funzionato, e abbastanza velocemente, se qualcuno ha qualche alternativa a questo, sarò felice di sentire.

Saluti.

PD Scusa il mio inglese, non è la mia lingua madre.

Collegamento -> se hai una colonna di tipo misto in Excel: ordina la colonna Z in A

Ho praticamente esaminato tutte le risposte qui e alcune di esse hanno funzionato per me e altre no, tuttavia nessuna era desiderabile per me perché in qualche modo ADO non ha scelto i dati in una colonna di tipo misto che avevo nel mio file Excel. Ho dovuto impostare HDR=NO per fare in modo che ADO leggesse la colonna del mio foglio di calcolo che è un mix di testo e numeri e in questo modo perdo la possibilità di utilizzare le intestazioni di colonna nelle mie istruzioni SQL che non sono buone. Se l’ordine delle colonne cambia nel file Excel, l’istruzione SQL genererà un errore o un output errato.

In una colonna di tipo di dati misti, la chiave è le prime 8 righe. ADO determina il tipo di dati per la colonna in base alle prime 8 righe Quindi se vuoi ancora modificare la tua stringa di connessione con i parametri estesi, semplicemente ordina la colonna Z in A sul tuo file Excel prima di leggere i dati con ADO in modo da le righe in alto sono quelle di testo e quindi la colonna verrà selezionata come testo.

Se le tue righe iniziali sono numeri (indipendentemente dal fatto che la colonna sia impostata per formattare TEXT in Excel) ADO determinerà le colonne come un tipo numerico, quindi una volta letta le righe di testo sottostanti, non può convertirle in numeri. Nel caso opposto, se la colonna è un testo determinato, se una riga qualsiasi contiene un numero, può essere trasmessa come testo.