Click here to Skip to main content
15,891,253 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi Everyone,

OLEDB cannot support >256 columns, I know
Excel 2007 and later can support 16k columns.

what methods do we have left to try and extract the information on 1 pass ?

SSIS SQL Server ?
Open XML ?

Convert to CSV and read ?

Can you use OLEDB to read a named range eg. A-x (where x<256 columns) and then x->n where n=>256 columns and x is 255th/256th column ?

Running out of ideas, wish Microsoft would update their antiquated OLEDB limit !

anyone out there willing to create an OLEDB library that addresses this limitation ?

Thanks,
Posted
Comments
Maciej Los 17-Sep-12 13:45pm    
What kind of connetion string do you use?
AU Jase 17-Sep-12 16:52pm    
what answer is this ? I know connection string !
Maciej Los 17-Sep-12 17:00pm    
Not necessary...
Asking about connection string i would like to know the drivers you use and the version of Excel (probably 2k7)...
Maciej Los 17-Sep-12 16:57pm    
What kind of data and why your file exceeds 256 columns?
AU Jase 17-Sep-12 18:16pm    
all I can say, is the .xlsx files I need to work on are constantly being filled past 255 columns, so I need a solution, not why do you need types of answers ! Its like saying, why do you go to work ? why do you need to go to codeproject

Try to use PIA (Primary Interop Assemblies, for exmple: http://www.microsoft.com/en-us/download/details.aspx?id=3508[^]) to work with Excel files.
It is possible to work with any tables' ranges, that Excel supports itself.
Here's code example:
C#
{
    //...
    string file_path = @"D:\doc\excel.xlsx";
    Microsoft.Office.Interop.Excel.Application xlApp =
        new Microsoft.Office.Interop.Excel.ApplicationClass();
    // Disabling messageboxes to prevent losing control on Excel app
    xlApp.DisplayAlerts = false;
    Microsoft.Office.Interop.Excel.Workbook xlWorkBook = 
        xlApp.Workbooks.Open(file_path, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
    xlWorkBook.DoNotPromptForConvert = true;
    for (int k = 1; k <= xlWorkBook.Worksheets.Count; k++){
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)this.xlWorkBook.Worksheets.get_Item(k);
        int cCnt = xlWorkSheet.Cells.Count;
        // processing all cells used, but can call named ranges
        Microsoft.Office.Interop.Excel.Range range = xlWorkSheet.UsedRange;
        for (int rCnt = 1; rCnt <= range.Rows.Count; rCnt++){
            for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++){
                Microsoft.Office.Interop.Excel.Range rng =    (Microsoft.Office.Interop.Excel.Range)range.Cells[rCnt, cCnt];
                // do something with range
            }
        }
     }
    //...
}
 
Share this answer
 
Comments
AU Jase 17-Sep-12 16:51pm    
this solution I know, it is automation with interop, its slow and is not thread safe. OLEDB is superior, fast and can do multithreading but at the cost of only <256 columns.

You would think Microsoft with 16384 column support in excel 2007 and later would back it up with their ACE.OLEDB driver also supporting 16k columns, but no, OLEDB is still in the dark ages !


There surely is a way, some suggest convert the .xlsx to .csv and then read, but what do you use to convert >256 .xlsx to csv ?

Also tried Open XML SDK from microsoft, ClosedXML having issues with object not set to reference (null) trying to read .xlsx as XML

Out of ideas !
Maciej Los 17-Sep-12 16:53pm    
Yes, this is an idea, but have you ever try to load data using this method?
AU Jase 17-Sep-12 18:14pm    
I tried OpenXML, ClosedXML and got the object not set to reference error (null)

Interop,is very time consuming, I know it will work, but I need fast response, user screen/web form will be locked whilst it loads (as interop runs on UI thread)
Using OpenXML SDK, further investigation, problem solved

As .xlsx files are XML spec compatible, reading with XML solves the issue.
 
Share this answer
 
I would never suggest Interop stuffs, go with ExcelDataReader a good opensource Lib so far found good:
http://exceldatareader.codeplex.com/[^]

Also this does not have any dependancy on MS Office.
 
Share this answer
 
Comments
AU Jase 18-Sep-12 1:06am    
you maybe right, trying to use open xml sdk on asp.net is slow with memory leaks
Use SQL like this

select * from [Sheet1$IV:JT]


then combine the results in memory
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900