Viewing Big Data in Excel 2007
Viewing Big Data in Excel 2007
By : Kasim Wirama, MCITP, MCDBA
SQL Server Integration Services (SSIS) is excellent ETL platform to do export import data from and to heterogeneous data source. In this posting, I would like to give my thought about exporting data into excel file as it is most popular alternatives besides flat file and it’s widely used as office spreadsheet application.
Excel has great feature and user friendly capability but it’s has also limitation in terms of maximum 65,536 rows supported. In SSIS 2005 it has excel provider up to version 2003 so you will get hard limit to the maximum rows and in SSIS 2008 it has excel provider up to version 2007 so you will get soft limit to the maximum rows. If you use either SSIS 2005 or SSIS 2008, you may have 2 options to export rows larger than maximum limit. First option is to create custom data source provider as SSIS provides API to extend it. Second option is to export column delimited flat files. If you frequently export big rows into excel file, I suggest you to create custom excel file extension otherwise you can have second option.
I discuss the second option. After you run export process into the delimited file; you can view them in excel 2007. Definitely excel 2003 or older version couldn’t show you whole data in one worksheet. First open your excel file. Then open tab delimited file by clicking at upper left corner and choose Open sub menu.
In open dialog menu, choose all file type and pick your column delimited file. After you click Open button, you will be presented to Text Import Wizard – Step 1 of 3. Choose Delimited option and go to Next button. On Step 2 of 3, choose delimited character and go to Next button. On last step (Step 3 of 3), you can define different data type for each column by clicking each column first and then choose data type in option region (Column Data Format).
Excel 2007 offers great flexibility than previous version. If you have big rows beyond 65,536 rows, Excel 2007 could display them perfectly.