webentwicklung-frage-antwort-db.com.de

OleDB & gemischte Excel-Datentypen: fehlende Daten

Ich habe ein Excel-Arbeitsblatt, das ich in eine Datentabelle einlesen möchte - bis auf eine bestimmte Spalte in meinem Excel-Arbeitsblatt ist alles gut. Die Spalte 'ProductID' ist eine Mischung aus Werten wie ########## und n#########.

Ich habe versucht OleDB alles automatisch von selbst erledigen lassen indem ich es in einen Datensatz/eine Datentabelle einliest, aber alle Werte in 'ProductID' wie n###### fehlen, werden ignoriert und leer gelassen. Ich habe versucht, meine DataTable manuell zu erstellen, indem ich jede Zeile mit einem Datenbereich durchlaufe, jedoch mit den exakt gleichen Ergebnissen.

Hier ist der Code:

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

Ich verstehe nicht, warum ich Werte wie n###### nicht einlesen kann. Wie kann ich das machen? 

49
rlb.usa

Bei der Verwendung von .NET 4.0 und dem Lesen von Excel-Dateien hatte ich ein ähnliches Problem mit OleDbDataAdapter - dh das Lesen eines gemischten Datentyps in einer "PartID" -Spalte in MS Excel, wobei ein PartID-Wert numerisch (zB 561) oder Text (zB HL4354) sein kann ), obwohl die Excel-Spalte als "Text" formatiert wurde. 

Wie ich das beurteilen kann, wählt ADO.NET den Datentyp basierend auf der Mehrheit der Werte in der Spalte (wobei ein Zusammenhang mit dem numerischen Datentyp besteht). Wenn also die meisten PartIDs im Beispielsatz numerisch sind, wird die Spalte von ADO.NET als numerisch deklariert. Daher versucht ADO.Net, jede Zelle in eine Zahl umzuwandeln, die für die "text" -PartID-Werte fehlschlägt und diese "text" -PartIDs nicht importiert. 

Meine Lösung bestand darin, die OleDbConnection-Verbindungszeichenfolge so zu setzen, dass Extended Properties=IMEX=1;HDR=NO verwendet wird, um anzuzeigen, dass dies ein Import ist und dass die Tabelle (n) keine Header enthalten. Die Excel-Datei hat eine Kopfzeile, in diesem Fall sollten Sie ado.net mitteilen, dass Sie diese nicht verwenden soll. Entfernen Sie später im Code diese Kopfzeile aus der Datenmenge und Sie haben gemischten Datentyp für diese Spalte.

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

// Jetzt können Sie LINQ verwenden, um die Felder zu durchsuchen

    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")
                });
101
Brian Wells

Mehrere Foren, die ich gefunden habe, behaupten, dass das Problem durch das Hinzufügen von IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text zu den erweiterten Eigenschaften in der Verbindungszeichenfolge behoben werden könnte. Dies war jedoch nicht der Fall. Ich löste dieses Problem schließlich, indem zu den erweiterten Eigenschaften in der Verbindungszeichenfolge "HDR = NO" hinzugefügt wurde (wie Brian Wells oben zeigt), sodass ich gemischte Typen importieren konnte.

Ich habe dann einen generischen Code hinzugefügt, um die Spalten nach der ersten Datenzeile zu benennen, und dann die erste Zeile zu entfernen.

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

Kein Problem, sh4, froh, es hilft bei der gemischten Ausgabe.

Die DateTime-Spalte ist ein ganz anderes Tier, von dem ich mich erinnere, dass es in der Vergangenheit zu Trauer gekommen ist. Wir haben eine Excel-Datei, die vom OleDbDataAdapter-Objekt manchmal in einen doppelten Datentyp konvertiert wird (anscheinend speichert Excel Datumsangaben als Doppelte, wodurch die Zahl verschlüsselt wird der seit dem 1. Januar 1900 verstrichenen Tage). 

Die Problemumgehung war zu verwenden:

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;
            }
        }
6
Brian Wells

Es gibt zwei Möglichkeiten, gemischte Datentypen und Excel zu behandeln.

Methode 1

  • Öffnen Sie Ihre Excel-Tabelle und stellen Sie das Spaltenformat manuell auf das gewünschte Format ein. In diesem Fall "Text".

Methode 2

  • Es gibt einen "Hack", der darin besteht, "IMEX = 1" an Ihre Verbindungszeichenfolge anzuhängen wie folgt:

    Provider = Microsoft.Jet.OLEDB.4.0; Datenquelle = MeineDatei.xls; Erweiterte Eigenschaften = Excel 8.0; IMEX = 1

  • Dadurch wird versucht, gemischte Excel-Formate entsprechend der Einstellung in Ihrer Registrierung zu verarbeiten. Sie können dies lokal festlegen, aber für einen Server ist dies wahrscheinlich keine Option.

5
rlb.usa

@Brian Wells Vielen Dank, dein Vorschlag hat den Trick erfüllt, aber nicht ganz ... Für das gemischte Feld int-string gearbeitet, aber die datetime-Spalten wurden mit seltsamen Zeichen versehen, daher habe ich einen "Hack" über den "Hack" angewendet .

1.- Führen Sie eine System.Io.File.Copy aus und erstellen Sie eine Kopie der Excel-Datei.

2. Ändern Sie die Datetime-Spaltenüberschriften zur Laufzeit programmatisch auf etwas im Datumsformat, d. H. "01/01/0001".

3. Speichern Sie die Excel-Datei und wenden Sie Ihren Trick bei der Abfrage mit HDR = NO auf die geänderte Datei an.

Tricky ja, aber gearbeitet, und ziemlich schnell, wenn jemand eine Alternative dazu hat, würde ich mich freuen zu hören.

Schöne Grüße.

P.D. Entschuldigen Sie mein Englisch, es ist nicht meine Muttersprache.

1
sh4

Tastenkombination -> Wenn Sie in Excel eine Spalte mit gemischten Typen haben: Sortieren Sie Ihre Spalte Z in A

Ich habe alle Antworten hier durchgelesen und einige von ihnen haben für mich gearbeitet und andere nicht, jedoch war keine für mich wünschenswert, da ADO irgendwie nicht die Daten in einer gemischten Kolumne auswählte, die ich in meiner hatte Excel-Datei. Ich musste HDR=NO so einstellen, dass ADO meine Tabellenspalte liest, die eine Mischung aus Text und Zahlen ist. Auf diese Weise verliere ich die Möglichkeit, Spaltenköpfe in meinen SQL-Anweisungen zu verwenden, was nicht gut ist. Wenn sich die Reihenfolge der Spalten in der Excel-Datei ändert, führt die SQL-Anweisung zu einem Fehler oder einer falschen Ausgabe. 

In einer Spalte mit gemischten Datentypen sind die ersten 8 Zeilen der Schlüssel. ADO bestimmt den Datentyp für die Spalte basierend auf den ersten 8 Zeilen. Wenn Sie Ihre Verbindungszeichenfolge weiterhin mit den erweiterten Parametern ändern möchten, sortieren Sie einfach Ihre Spalte Z bis A in Ihrer Excel-Datei, bevor Sie die Daten lesen von ADO, so dass die Zeilen oben die Textzeilen sind und Ihre Spalte dann als Text ausgewählt wird. 

Wenn Ihre ersten Zeilen Zahlen sind (unabhängig davon, ob Ihre Spalte in Excel auf TEXT formatiert ist) ADO bestimmt diese Spalten als numerischen Typ. Sobald die darunterliegenden Textzeilen gelesen werden, können diese nicht in eine Zahl umgewandelt werden. Im umgekehrten Fall kann die Spalte als Text definiert werden, wenn die Spalte als Text definiert ist.

0
Ibo