Ik heb een Excel-werkblad dat ik in een datatabel wil inlezen – alles is in orde, behalve één bepaalde kolom in mijn Excel-blad. De kolom ‘ProductID’ is een mix van waarden zoals ##########en n#########.
Ik heb geprobeerd OleDB alles automatisch zelf te laten afhandelendoor het in te lezen in een dataset/datatable, maar alle waarden in ‘ProductID’ zoals n######ontbreken, worden genegeerd en worden leeg gelaten. Ik heb geprobeerd mijn DataTable handmatig te maken door elke rij te doorlopen met een datareader, maar met exact dezelfde resultaten.
Hier is de 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);
}
Ik begrijp niet waarom ik waarden als n######niet kan inlezen. Hoe kan ik dit doen?
Antwoord 1, autoriteit 100%
Bij het gebruik van .Net 4.0 en het lezen van Excel-bestanden had ik een soortgelijk probleem met OleDbDataAdapter– dwz het lezen van een gemengd gegevenstype in een “PartID”-kolom in MS Excel, waar een PartID-waarde kan zijn numeriek (bijv. 561) of tekst (bijv. HL4354), ook al was de Excel-kolom opgemaakt als “Tekst”.
Voor zover ik kan zien, kiest ADO.NET het gegevenstype op basis van de meeste waarden in de kolom (met een gelijkspel naar het numerieke gegevenstype). d.w.z. als de meeste PartID’s in de voorbeeldset numeriek zijn, zal ADO.NET de kolom als numeriek verklaren. Daarom zal ADO.Net proberen elke cel naar een nummer te casten, wat zal mislukken voor de “tekst” PartID-waarden en die “tekst” PartID’s niet importeert.
Mijn oplossing was om de OleDbConnection-verbindingsreeks in te stellen om Extended Properties=IMEX=1;HDR=NOte gebruiken om aan te geven dat dit een import is en dat de tabel(len) zal geen headers bevatten. Het Excel-bestand heeft een kopregel, dus zeg in dit geval tegen ado.net dat het het niet mag gebruiken. Verwijder later in de code die koprij uit de dataset en voilà, je hebt een gemengd gegevenstype voor die kolom.
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();
// nu kun je LINQ gebruiken om in de velden te zoeken
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")
});
Antwoord 2, autoriteit 9%
Verschillende forums die ik vond beweren dat het probleem zou worden opgelost door IMEX=1;TypeGuessRows=0;ImportMixedTypes=Texttoe te voegen aan de uitgebreide eigenschappen in de verbindingsreeks, maar dit was niet het geval. Ik heb dit probleem uiteindelijk opgelost door “HDR=NO” toe te voegen aan de uitgebreide eigenschappen in de verbindingsreeks (zoals Brian Wells hierboven laat zien), zodat ik gemengde typen kon importeren.
Vervolgens heb ik een generieke code toegevoegd om de kolommen een naam te geven na de eerste rij met gegevens, en vervolgens de eerste rij te verwijderen.
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;
}
Antwoord 3, autoriteit 6%
Geen probleem sh4, blij dat het helpt bij het probleem met het gemengde type.
De DateTime-kolom is een heel ander dier waarvan ik me herinner dat het me in het verleden verdriet heeft bezorgd… we hebben één Excel-bestand dat we verwerken en dat de OleDbDataAdapter soms datums naar een dubbel gegevenstype converteert (blijkbaar slaat Excel datums op als dubbels, wat coderen voor het aantal dagen verstreken sinds 0 januari 1900).
De oplossing was om te gebruiken:
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;
}
}
Antwoord 4, autoriteit 5%
Er zijn twee manieren om met gemengde datatypes om te gaan & uitblinken.
Methode 1
- Open uw Excel-spreadsheet en stel het kolomformaat handmatig in op het gewenste formaat. In dit geval ‘Tekst’.
Methode 2
-
Er is een “hack” die bestaat uit het toevoegen van “IMEX=1” aan uw verbindingsreeks vind ik leuk:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=mijnbestand.xls;Extended Properties=Excel 8.0;IMEX=1
-
Hiermee wordt geprobeerd gemengde Excel-indelingen te verwerken volgens hoe het in uw register is ingesteld. Dit kan je lokaal instellen, maar voor een server is dit waarschijnlijk geen optie.
Antwoord 5
@Brian Wells Dank je, je suggestie werkte, maar niet helemaal… Werkte voor de gemengde veld int-string, maar de datetime-kolommen gingen daarna met vreemde tekens, dus ik paste een “hack” toe over de “hacken”.
1.- Voer een System.Io.File.Copy uit en maak een kopie van het Excel-bestand.
2.- Wijzig de Datetime-kolomkoppen programmatisch tijdens runtime naar iets in datetime-formaat, bijv. “01/01/0001”.
3.- Sla de Excel op en pas vervolgens uw truc toe door de query met HDR=NO uit te voeren op het gewijzigde bestand.
Tricky, ja, maar werkte, en redelijk snel, als iemand een alternatief hiervoor heeft, hoor ik het graag.
Gegroet.
PD Excuseer mijn Engels, het is niet mijn moedertaal.
Antwoord 6
Snelkoppeling –> als je een gemengde kolom in Excel hebt: Sorteer je kolom Z naar A
Ik heb vrijwel alle antwoorden hier doorgenomen en sommige werkten voor mij en sommige niet, maar geen enkele was wenselijk voor mij omdat ADO op de een of andere manier de gegevens niet koos in een kolom met gemengd type die ik in mijn Excel had het dossier. Ik moest HDR=NOinstellen om ADO mijn spreadsheetkolom te laten lezen die een combinatie is van tekst en cijfers en op die manier verlies ik de mogelijkheid om kolomkoppen in mijn SQL-instructies te gebruiken, wat niet goed is. Als de volgorde van kolommen in het Excel-bestand verandert, resulteert de SQL-instructie in een fout of verkeerde uitvoer.
In een kolom met gemengd gegevenstype is de sleutel de eerste 8 rijen. ADO bepaalt het gegevenstype voor de kolom op basis van de eerste 8 rijenDus als u uw verbindingsreeks nog steeds wilt wijzigen met de uitgebreide parameters, sorteert u eenvoudig uw kolom Z tot A in uw Excel-bestand voordat u de gegevens door ADO leest dus op deze manier zijn de rijen bovenaan de tekst en wordt je kolom als tekst gekozen.
Als uw eerste rijen getallen zijn (ongeacht of uw kolom is ingesteld om TEKST in Excel op te maken), zal ADO die kolommen als een numeriek type bepalen, dus zodra het de onderstaande tekstrijen leest, kan het deze niet in getallen gieten. In het tegenovergestelde geval, als de kolom bepaalde tekst is, als een rij een nummer is, kan deze als tekst worden gegoten.