1

I have a short piece of c# code to generate an xml file, from a given excel file. It's working fine.

System.Data.OleDb.OleDbConnection MyConnection;
System.Data.DataSet ds;
System.Data.OleDb.OleDbDataAdapter MyCommand;
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source='xxx.xls';Extended Properties=Excel 12.0;");
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from ["+fileName+"$]", MyConnection);// 
MyCommand.TableMappings.Add("Table", "row");                
ds = new System.Data.DataSet();
MyCommand.Fill(ds);
MyConnection.Close();
ds.WriteXml("xxx.xml");

But I realized yesterday, that sometimes, my xml is lacking data.

For exemple , if I have a column in excel that goes like this :

 Number
 3
 4
 5
 6
 23
 ff
 5

The generate xml file does not contains ff, and I can't figure out why. Does the data set do an implicit casting of types and detect that Number is a column of integer ? If not, what do you think is the problem ?

Thanks

Kypaz

Kypaz
  • 411
  • 3
  • 11
  • This is a well know problem with Excel and OleDb. See here http://stackoverflow.com/questions/3232281/oledb-mixed-excel-datatypes-missing-data?rq=1 – Steve Jul 03 '15 at 07:50
  • Yep thanks, changing the extended properties worked : `Extended Properties='Excel 12.0;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text;'` – Kypaz Jul 03 '15 at 08:04

0 Answers0