September 2, 2010 at 7:44 am
Hi,
I got 723 columns by joining 3 tables and there are 160000 records....
what is the best way to load the data into excel...is it possible in 2007 excel...pleasee let me know...
September 2, 2010 at 8:04 am
in Excel 2007 the climit is 16384 columns by 1048576 rows so it will fit technically.
However I believe I had read somewhere that the connection to Excel from SQL 2008 had not been updated and so you are still bound by the old limit of 65,536 rows and 256 columns.
There are ways around this for example you code output a CSV file having no limit at all beyond what is reasonably useful.
Now if your question is how to accomplish this you can do this from a SQL script directly or from SSIS which would you be more apt to use?
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
September 2, 2010 at 8:14 am
It was bothering me so I went and looked around and found where there seems to be an updated Jet driver available that will allow you to use the new limits in Excel 2007. Here is a quote from a forumn post I found on msdn.
While you can use an Excel connection manager to connect to a data source that uses Microsoft Office Excel 2003 or earlier, you cannot use an Excel connection manager to connect to a data source that uses Microsoft Office Excel 2007. Also, you cannot use the Microsoft Jet 4.0 OLE DB Provider to connect to an Excel 2007 data source. To connect to an Excel 2007 data source, use an OLE DB connection manager, and for Provider, select Microsoft Office 12.0 Access Database Engine OLE DB Provider. Then, on the All page of the Connection Manager dialog box, for Extended Properties, enter Excel 12.0.
So it seems it is poissible.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
September 2, 2010 at 8:36 am
thanks a lot Dan
September 2, 2010 at 9:23 am
You could also export to csv and then open the csv file in Excel.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply