Types de données OleDB et Excel mixte: données manquantes

Question

J'ai une feuille de calcul Excel que je veux lire dans un datatable - tout est bien, sauf pour une colonne particulière dans ma feuille Excel. La colonne, "ProductID", est un mélange de valeurs comme l' ########## et n#########.

J'ai essayé de laisser OleDB tout gérer par lui-même automatiquement en le lisant dans un dataset/datatable, mais toutes les valeurs dans "ProductID" comme l' n###### sont manquantes, ignorée et laissée en blanc. J'ai essayé manuellement la création de ma DataTable par une boucle sur chaque ligne avec un datareader, mais avec les mêmes résultats.

Voici le 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);
}

Je ne comprends pas pourquoi il ne me laisse pas lire dans des valeurs comme l' n######. Comment puis-je faire cela?

Answer 1

À l'aide de .Net 4.0 et la lecture des fichiers Excel, j'ai eu un problème similaire avec OleDbDataAdapter - c'est à dire la lecture dans un mélange de type de données sur un "PartID" colonne dans MS Excel, où un PartID valeur peut être numérique (p. 561) ou du texte (par exemple HL4354), même si la colonne d'excel a été mis en forme en tant que "Texte".

À partir de ce que je peux dire, ADO.NET choisit le type de données basé sur la majorité des valeurs dans la colonne (avec une cravate va type de données numérique). c'est à dire si la plupart des PartID dans l'ensemble de l'échantillon sont numériques, ADO.NET va déclarer la colonne à être numérique. Donc ADO.Net va tenter de jeter chaque cellule à un certain nombre, qui ne pour la "texte" PartID de valeurs et de ne pas importer ces "texte" PartID de l'.

Ma solution a été de définir l' OleDbConnection connectionstring pour utiliser Extended Properties=IMEX=1;HDR=NO pour indiquer que c'est une Importation et que la / les table(s) ne sera pas inclure les en-têtes. Le fichier excel contient un en-tête de ligne, dans ce cas, dites ado.net ne pas l'utiliser. Puis, plus tard dans le code, la suppression de cette ligne d'en-tête à partir du dataset et voilà vous avez mixte type de données pour cette colonne.

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

// maintenant, vous pouvez utiliser LINQ pour rechercher les champs

    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 2

Pas de problème sh4, content que cela aide w / le problème de type mixte.

La colonne DateTime est un autre animal que je me souviens qui m'a causé beaucoup de chagrin dans le passé ... nous avons un fichier Excel que nous traitons, qui OleDbDataAdapter convertit parfois les dates en un type de données double (apparemment, Excel stocke les dates sous forme de doubles, qui codent le nombre de jours écoulés depuis le 0 janvier 1900).

La solution de contournement consistait à utiliser:

 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 3

Il y a deux façons de gérer mixte types de données et excel.

Méthode 1

• Ouvrez votre feuille de calcul excel et de définir le format de colonne pour le format souhaité manuellement. Dans ce cas, 'Texte'.

Méthode 2

• Il y a un "hack" qui consiste à ajouter "IMEX=1" à votre chaîne de connexion comme suit:

  Provider=Microsoft.Jet.OLEDB.4.0;Données Source=myfile.xls;Extended Properties=Excel 8.0;IMEX=1

• Il va tenter de gérer mixte format Excel fonction de la façon dont il est défini dans votre base de registre. Il peut être défini localement par vous, mais pour un serveur, ce n'est probablement pas une option.

Answer 4

@Brian Wells Merci, votre suggestion a fait le tour, mais pas totalement... a Travaillé pour le domaine mixte int-chaîne, mais les colonnes datetime est allé avec des personnages étranges après, je l'ai donc appliqué un "hack" sur le "hack".

1.- Faire un Système.Io.Fichier.Copie et de créer une copie du fichier excel.

2.- Modifier les en-têtes de colonne Datetime par programmation à l'exécution de quelque chose dans le format datetime, c'est à dire "01/01/0001".

3.- Enregistrer le fichier excel, puis appliquez votre tour de faire la requête avec HDR=NO pour le fichier modifié.

Difficile, oui, mais il a travaillé, et reasonabily rapide, si quelqu'un a une alternative à cela, je serai heureux de les entendre.

Salutations.

P. D. Excusez mon anglais, ce n'est pas ma langue maternelle.