OLEDB i mieszane typy danych Excel: brak danych
Mam arkusz Excel, który chcę przeczytać w datatable-wszystko jest dobrze, z wyjątkiem jednej konkretnej kolumny w moim arkuszu Excel. Kolumna "ProductID" jest mieszanką wartości takich jak ##########
i n#########
.
Próbowałem pozwolić OleDB zająć się wszystkim automatycznie przez wczytanie go do zbioru danych/datatable, ale wszystkie wartości w 'ProductID', takie jak n######
, są pomijane, ignorowane i pozostawione puste. Próbowałem ręcznie utworzyć mój DataTable przez zapętlenie każdego wiersza za pomocą datareadera, ale z dokładnie te same wyniki.
Oto kod:
// 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);
}
Nie rozumiem, dlaczego nie pozwala mi czytać w wartościach takich jak n######
. Jak mogę to zrobić?
6 answers
Używając. Net 4.0 i czytając pliki Excela, miałem podobny problem z OleDbDataAdapter
- tzn. czytając mieszany typ danych na kolumnie "PartID" w MS Excel, gdzie wartość PartID może być numeryczna (np. 561) lub tekstowa (np. HL4354), mimo że kolumna excel została sformatowana jako "Text".
Z tego co wiem, ADO.NET typ danych wybiera na podstawie większości wartości w kolumnie (z opcją przechodzenia do liczbowego typu danych). tzn. jeśli większość partycji w zestawie przykładowym jest liczbowa, ADO.NET zadeklaruje kolumnę jako liczbową. Dlatego ADO.Net będzie próbował rzucić każdą komórkę do liczby, co nie powiedzie się dla wartości" text "PartID i nie zaimportuje tych" text " PartID.
Moim rozwiązaniem było ustawienie OleDbConnection
connectionstring, aby używać Extended Properties=IMEX=1;HDR=NO
, aby wskazać, że jest to Import i że tabela(y) nie będzie zawierać nagłówków. Plik excel ma wiersz nagłówka, więc w tym przypadku powiedz ado.net nie używać go. Następnie w kodzie usuń ten wiersz nagłówka ze zbioru danych i voilà masz mieszany typ danych dla tej kolumny.
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();
// Teraz możesz użyć LINQ do przeszukiwania pól
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")
});
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-04-16 16:39:27
Kilka forów stwierdziłem, że dodanie IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text
do rozszerzonych właściwości w łańcuchu połączeń rozwiązałoby problem, ale tak nie było. W końcu rozwiązałem ten problem, dodając" HDR = NO " do rozszerzonych właściwości w łańcuchu połączeń (jak pokazuje Brian Wells powyżej), aby móc importować typy mieszane.
Następnie dodałem kod ogólny, aby nazwać kolumny po pierwszym wierszu danych, a następnie usunąć pierwszy wiersz.
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;
}
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-10-28 16:27:00
No problem sh4, cieszę się, że pomaga w / problem typu mieszanego.
Kolumna DateTime to zupełnie inne zwierzę, które pamiętam, sprawiało mi smutek w przeszłości... mamy jeden plik Excela, który przetwarzamy, że OleDbDataAdapter czasami konwertuje daty na podwójny typ danych(najwyraźniej Excel przechowuje daty jako podwójne, co koduje liczbę dni, które upłynęły od 0 stycznia 1900).
Obejście polegało na użyciu:
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;
}
}
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-05-03 14:01:27
Istnieją dwa sposoby obsługi mieszanych typów danych & excel.
Metoda 1
- otwórz arkusz kalkulacyjny excel i ustaw ręcznie format kolumny na żądany format. W tym przypadku "tekst".
Metoda 2
-
Istnieje "hack", który polega na dodaniu "IMEX=1" do łańcucha połączeń w następujący sposób:
Provider=Microsoft.Jet.OLEDB.4.0; źródło danych = myfile.XLS; Extended Properties = Excel 8.0; IMEX=1
Będzie to próba obsługi mieszanych formatów Excel zgodnie z tym, jak jest ustawiony w rejestrze. Może to być ustawione lokalnie przez Ciebie, ale dla serwera prawdopodobnie nie jest to opcja.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-07-12 21:22:28
@Brian Wells Dziękuję, Twoja sugestia pomogła, ale nie do końca... Pracował dla mieszanego pola int-string, ale kolumny DateTime poszedł z dziwnymi znakami po tym, więc zastosowałem "hack" nad "hack".
1.- Wykonaj System. Io. File. Copy i utwórz kopię pliku excel.
2.- Programowo zmodyfikować nagłówki kolumn Datetime w czasie wykonywania na coś w formacie datetime, np. "01/01/0001".
3.- Zapisz Excela, a następnie zastosuj swoją sztuczkę robiąc zapytanie z HDR = NO do zmodyfikowanego pliku.
Tricky, tak, ale działa, i rozsądnie szybko, jeśli ktoś ma jakąś alternatywę dla tego, z przyjemnością to usłyszę.
Pozdrawiam.
P. D. Wybacz mój angielski, to nie jest mój język ojczysty.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-04-28 14:33:47
Skrót -- > jeśli masz kolumnę typu mieszanego w Excelu: Sortuj kolumnę Z do a
Przejrzałem tutaj wszystkie odpowiedzi i niektóre z nich działały dla mnie, a niektóre nie, jednak żadna nie była dla mnie pożądana, ponieważ jakoś ADO nie wybrał danych w kolumnie typu mieszanego, które miałem w moim pliku Excel. Musiałem ustawić HDR=NO
, aby ADO czytał moją kolumnę arkusza kalkulacyjnego, która jest mieszanką tekstu i liczb i w ten sposób tracę możliwość korzystania z nagłówków kolumn w moim SQL wypowiedzi, które nie są dobre. Jeśli kolejność kolumn zmieni się w pliku Excel, polecenie SQL spowoduje błąd lub błędne wyjście.
W kolumnie mieszany typ danych kluczem jest pierwsze 8 wierszy. ADO określa typ danych dla kolumny na podstawie pierwszych 8 wierszy , więc jeśli nadal chcesz zmodyfikować łańcuch połączenia z rozszerzonymi parametrami, po prostu Sortuj kolumnę Z do a w pliku Excel przed odczytaniem danych przez ADO, więc w ten sposób wiersze na górze są tekstowe a następnie twoja kolumna zostanie wybrana jako tekst.
Jeśli początkowe wiersze są liczbami (niezależnie od tego, czy kolumna jest ustawiona na formatowanie tekstu w Excelu) ADO określi te kolumny jako typ liczbowy, więc po odczytaniu wierszy tekstowych poniżej nie może ich wrzucić do liczby. W przeciwnym przypadku, jeśli kolumna jest określony tekst, jeśli dowolny wiersz, jeśli liczba, może być rzucany jako tekst.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-06-05 17:56:20