none
Excel to dataset issue. RRS feed

  • Question

  • Hi all, I am using OLEDB for loading a csv file (from excel) into a dataset. 2 of the fields are time formats like 12:34:00 AM and they look fine in the csv file, but in the dataset they appear like 12/30/1899 12:04. I need to parse the dates so I can get a timespan from them (this part I can do with the right format). Any ideas how to stop the formatting issue?
    Close counts in horseshoes, handgranades and nuclear missiles!
    Monday, January 4, 2010 3:33 AM

Answers

  • Hi,

    with this Excel C# library you can easily handle different types in Excel file and DataTable. Here is example code of Excel to DataSet import tailored for your problem:

    var ef = new ExcelFile();
    ef.LoadCsv("FileName.csv", CsvType.CommaDelimited);
    
    // Initialize DataTable (skip this if you have DataTable definition)
    DataTable dt = new DataTable();
    dt.Columns.Add("time1", typeof(DateTime));
    dt.Columns.Add("time2", typeof(DateTime));
    
    var ws = ef.Worksheets[0];
    // Manage ExtractDataError.WrongType error
    ws.ExtractDataEvent += (sender, e) =>
    {
    	if (e.ErrorID == ExtractDataError.WrongType)
    	{
    		if (e.Mapping.DataSetColumn == "time1" || e.Mapping.DataSetColumn == "time2")
    		{
    			e.DataTableValue = DateTime.Parse(e.ExcelValue);
    			e.Action = ExtractDataEventAction.Continue;
    		}
    	}
    };
    
    // Extract data to DataTable
    ws.ExtractToDataTable(dt, 1000, ExtractDataOptions.StopAtFirstEmptyRow, ws.Rows[0], ws.Columns[0]);
    • Marked as answer by MadnessIsMine Tuesday, March 9, 2010 10:26 PM
    Tuesday, March 9, 2010 2:33 PM

All replies

  • OLEDB provider will not return only time portion from a CSV file. It will construct the full dat/time value and since the date portion is missing in CSV, it will use default value of 12/30/1899. But you still should be able to manipulate with the time portion of it without any problems and you should be able to present value on screen in any format? What exactly are you trying to achieve that is not working in your case?


    Val Mazur (MVP) http://www.xporttools.net
    Monday, January 4, 2010 11:21 AM
    Moderator
  • That part would not bother me, but the times are now missing the seconds. I am getting time difference B/T 2 times in this file. So I am not getting an accurate timespan. Thanks for the help.

    Close counts in horseshoes, handgranades and nuclear missiles!
    Monday, January 4, 2010 2:43 PM
  • You can modify data type and its precision in import wizard.Use Table mapping
    Thanks Mike --------Please mark as answer if it is useful----------
    Tuesday, January 5, 2010 11:05 AM
  • Thanks for the reply Mike, but I am not familiar with that process. Can you explain further?
    Close counts in horseshoes, handgranades and nuclear missiles!
    Tuesday, January 5, 2010 3:04 PM
  • @Mike_999 if you give a suggestion you should be willing to follow through. I too help other in different forums. It's just good etiquette.
    Wednesday, January 6, 2010 1:31 AM
  • Thanks for the reply Mike, but I am not familiar with that process. Can you explain further?
    Close counts in horseshoes, handgranades and nuclear missiles!
    check this one.http://www.aspose.com/community/forums/thread/197395/date-value-formatting-using-worksheet.cells-and-csv.aspx
    Thanks Mike --------Please mark as answer if it is useful----------
    Thursday, January 7, 2010 11:41 AM
  • I am not using Aspose so this does not make sense.
    Wednesday, January 13, 2010 11:13 PM
  • When you read CSV file, do you have Schema.ini file? If yes, do you specify type inside of the CSV as DateTime or as Char? Using Char for the time column, in your particular scenario, could give you full control how you could parse time values inside of client application instead of relying on OLEDB provider. You could load values as strings and then do extra step inside of client code to parse time string values and convert them into the actual time.
    Val Mazur (MVP) http://www.xporttools.net
    Thursday, January 14, 2010 11:33 AM
    Moderator
  • Hi,

    with this Excel C# library you can easily handle different types in Excel file and DataTable. Here is example code of Excel to DataSet import tailored for your problem:

    var ef = new ExcelFile();
    ef.LoadCsv("FileName.csv", CsvType.CommaDelimited);
    
    // Initialize DataTable (skip this if you have DataTable definition)
    DataTable dt = new DataTable();
    dt.Columns.Add("time1", typeof(DateTime));
    dt.Columns.Add("time2", typeof(DateTime));
    
    var ws = ef.Worksheets[0];
    // Manage ExtractDataError.WrongType error
    ws.ExtractDataEvent += (sender, e) =>
    {
    	if (e.ErrorID == ExtractDataError.WrongType)
    	{
    		if (e.Mapping.DataSetColumn == "time1" || e.Mapping.DataSetColumn == "time2")
    		{
    			e.DataTableValue = DateTime.Parse(e.ExcelValue);
    			e.Action = ExtractDataEventAction.Continue;
    		}
    	}
    };
    
    // Extract data to DataTable
    ws.ExtractToDataTable(dt, 1000, ExtractDataOptions.StopAtFirstEmptyRow, ws.Rows[0], ws.Columns[0]);
    • Marked as answer by MadnessIsMine Tuesday, March 9, 2010 10:26 PM
    Tuesday, March 9, 2010 2:33 PM
  • CikaPero, thanks. It will take me a little while to test it, I'll get back...
    Tuesday, March 9, 2010 8:47 PM
  • Well it did not use the 12/30/1899 date, but it still strips off the seconds - which is my critical need for parsing the time difference b/t these 2 columns. I will try using a string value and use Date.Parse instead.
    Tuesday, March 9, 2010 9:46 PM
  • Well that worked - Date.parse(...). I have to say OleDb jet could not have done this, so thanks bunches!!!
    Tuesday, March 9, 2010 10:26 PM
  • @CikaPero some questions with using this library. Does this mean it will work without Excel being installed on a target machine, or will there be an unknown file type error?
    Wednesday, March 10, 2010 11:53 PM
  • Sorry I read the website, I'm a good now.
    Friday, March 12, 2010 11:14 PM