locked
Import data from Excel File into a dataTable: column limit? RRS feed

  • Question

  • Hi all. I wrote this method which reads a .xlsx file and copies the data into a DataTable. The code worked well, but I never run it with a huge file. Now I have to import an Excel file which has more or less 800 columns: I run the code and find that the dataTable after the import has only 255 columns.

    I found in these forums that dataTables haven't such columns limits, so I'm asking if someone knows why my dataTable has only that number of columns insted of the real number of columns in the file and how can I change the code to avoid this problem.

    The method is:

    public static DataTable ImportData(string fileName, TextBox box)
        {
          string myConnString = String.Concat(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=", fileName,";Extended Properties=\"Excel 12.0;MaxScanRows=1;HDR=YES;IMEX=1\"");
          string sql = "select * from [Foglio1$]";
          OleDbConnection condati = new OleDbConnection(myConnString);

          try
          {
            condati.Open();
            DataTable dtbdati = new DataTable();
            OleDbDataAdapter dtadati = new OleDbDataAdapter(sql, condati);
            dtadati.Fill(dtbdati);
            condati.Close();
            return dtbdati;
          }
          catch
          {       
            AppendTextToTextbox(box, "Excel connection error.", 100);
            MessageBox.Show("Excel connection error. Please retry.");
            return null;
          }
        }

    Wednesday, July 21, 2010 11:55 AM

Answers

  • 255 is the column limit for provider i guess. Anyway try to use 'Range" in select command and see what is happening.

     string sql = "select * from [Foglio1$A1:BP1200]"; // apply range to the query

    • Marked as answer by Alex Liang Wednesday, July 28, 2010 10:00 AM
    Wednesday, July 21, 2010 1:57 PM
  • You will either need to use Excel Automation or find a data provider that supports more than 255 columns. You might want to try Val's (xPortTools) xlReader library to see if it has this limitation (or perhaps he will respond to this forum thread).

    http://www.xporttools.net/Default.aspx

     

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Alex Liang Wednesday, July 28, 2010 10:00 AM
    Wednesday, July 21, 2010 7:06 PM
  • I think Mike_99 is right. This is some sort of limitation in provider. It definitely was limitation in Jet, since older (xls) file format did not support more than 255 columns. As Paul pointed out, if you use my xlReader, you should be able to load all the columns
    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by Alex Liang Wednesday, July 28, 2010 10:00 AM
    Thursday, July 22, 2010 10:25 AM

All replies

  • 255 is the column limit for provider i guess. Anyway try to use 'Range" in select command and see what is happening.

     string sql = "select * from [Foglio1$A1:BP1200]"; // apply range to the query

    • Marked as answer by Alex Liang Wednesday, July 28, 2010 10:00 AM
    Wednesday, July 21, 2010 1:57 PM
  • What can you suggest to solve this problem and go beyond this limit? I can't modify the "range" each time I open a file, so there should be another way. I think I'm not the only one who has to open such big Excel files, I suppose there should be a solution, even using other objects.

    Please, help me, I need to test an application urgently and this bottleneck is driving me crazy...

    Wednesday, July 21, 2010 2:59 PM
  • You will either need to use Excel Automation or find a data provider that supports more than 255 columns. You might want to try Val's (xPortTools) xlReader library to see if it has this limitation (or perhaps he will respond to this forum thread).

    http://www.xporttools.net/Default.aspx

     

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Alex Liang Wednesday, July 28, 2010 10:00 AM
    Wednesday, July 21, 2010 7:06 PM
  • hi,

    I had dealt with excel several times in my carrier and found that some times ado.net/OledB/Odbc dataproviders fails to read the file or unable to find the column of our interest. So after good enough investigation i found that excel has some provision to mark vertical cells/ columns not to be treated as datacolumns. Please forgive me since I dont remember how to convert columns to data columns or vice-versa. but you can test your excel columns by desining sql-query (for office-2007: Data {tab}>> From Other Sources >>From Microsoft Query). if certain columns are missing or unable to select then these are those which are infected.

    If this is not the issuse then may be look in this link: http://www.connectionstrings.com/excel-2007 for right connection strings, use this link http://davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx querying using ado.net dbfactory or use this article http://technet.microsoft.com/en-us/library/ee692882.aspx

    Hope this solves the issue. do let us know your experience

    Regards,


    Manish Patil http://patilmanishrao.wordpress.com Posting is provided AS IS with no warranties, and confers no rights.
    Thursday, July 22, 2010 9:30 AM
  • I think Mike_99 is right. This is some sort of limitation in provider. It definitely was limitation in Jet, since older (xls) file format did not support more than 255 columns. As Paul pointed out, if you use my xlReader, you should be able to load all the columns
    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by Alex Liang Wednesday, July 28, 2010 10:00 AM
    Thursday, July 22, 2010 10:25 AM
  • Hello,

    you could also try this Excel C# / Excel VB.NET library.

    It supports DataTable imports within just one method call.

    Here is a sample code how to import Excel to DataTable :

    var ef = new ExcelFile();
    ef.LoadXls("ExcelData.xls");
    
    // Initialize DataTable (skip this if you have DataTable definition)
    var dt = new DataTable();
    dt.Columns.Add("name", typeof(string));
    dt.Columns.Add("birth", typeof(DateTime));
    
    var ws = ef.Worksheets[0];
    	
    // Extract data to DataTable
    ws.ExtractToDataTable(dt, ws.Rows.Count, ExtractDataOptions.StopAtFirstEmptyRow, ws.Rows[0], ws.Columns[0]);
    

    Friday, July 23, 2010 8:09 AM
  • This does not work. Should not have been marked as an answer.

    PaulVA

    Tuesday, May 20, 2014 2:41 PM