Excel Colum Formatting

Question

Post reply

Excel Colum Formatting

ajolson1964

SSC Veteran

Points: 224
More actions
August 27, 2010 at 7:15 am

#237253

I use SQL 2008 and I am exporting a SQL statement to excel 2007 file. I am getting major formatting issues. It appears that although I set the column value to be integer or date ….. Whenever it exports it just converts everything back to text or “General” in the respective column destination file. I have read up on various blogs about this issue and it seems what happens when importing into excel by Default excel looks at the first row of data (in this case the header row) and everything below that becomes the value for that field. That is undesirable. Other than formatting the first row then hiding it is there another solution or am I just missing something somewhere?
Any help on this issue will be greatly appreciated.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

Using OpenRowset/OpenDatasource, it can't be done.

However, you can accomplish the formatting by:

1. Use the Export wizard.

2. Use a SSIS package.

3. Use the sp_OA procedures to perform OLE Automation. This article might be helpful[/url], but it's not supported by Microsoft.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ajolson1964 SSC Veteran Points: 224 More actions · Answer 2

Actually I am using and SSIS package and still coming up with the same issue. I have checked my Metadata adn it is set up correctly. Also in the "Excel data flow destination" those column Names and Data types are correct for excel 2007 Yet when I export everything comes up text. Now if I export to xls (not xlsx) dates seem to convert over just fine. So saving the file in 2003 it works but when saving in 2007 not so much.

To give you the full picture of what I am doing. I run File system task that copies over my data file with a template file (thus deleting all data within it) then I run data flow task that exports a SQL statement to the data file.

WayneS SSC Guru Points: 95460 More actions · Answer 3

Can you try creating a spreadsheet instead of copying a template? Just use the Execute SQL Task to run a create table statement, pointing to the Excel destination. If the file isn't there, it will be created. Then export the data to it.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ajolson1964 SSC Veteran Points: 224 More actions · Answer 4

Yea did that too and still having the same issue. I is a nightmare no doubt.

ajolson1964 SSC Veteran Points: 224 More actions · Answer 5

For those who are interested I wanted to post my work around for this issue. I finally came up with one. Although not the most Ideal situation it will work for my purposes.

Problem:

Utilizing SSIS I wanted to exported a T-SQL statement from SQL 2008 to a excel 2007 file. Additionally I wanted the information to start on the 8th row of the spreadsheet named HR-317. Rows 1 – 8 are used for calculations on the data.

Solution:

I created a SQL statement. Within my SSIS package I created a data flow that exported to an excel destination.

The first time I ran this I had the “excel destination” create a dummy file in 2003 format. When I was prompted I created a tab for that file and named it ‘HR407’, I copied that format and save it to a text file for later use.

I deleted the file and the excel connection and then started over again but this time I created a 2007 file. When I got to the point to create the sheet inside of the created xlsx file I deleted all the information in the Create Table box and replaced it with the information I saved in the text file. This gave me the correct formatting for each field. This had to be done as the auto generated one for XLSX does not have correct data types for excel. Once I did this I opened up the excel connection and change the Connection string path to xml

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\bhihnbwfa01\Groups\HR\Verification\HR_317CurrentView.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=NO";

I ran the report and it created the file and placed the information in it as expected however the formatting of each integer and date field was converted to string.

To format the file correctly I cut the header row and pasted it in row 9. I then copied the row and pasted the copy in row 8. I went through and changed each of the fields to represent the data I wanted. So if column A was an number I replaced the Column name in row 8 with 1234 then formatted to number. I did this in each of the columns that needed reformatting. I hid this row. Then closed the file.

Then I copied the file to my template folder.

I went back to the SSIS package and opened the connection manager for excel and un-checked the box that says First row has Column names. Then I when to the excel destination button and re-assigned each row to the new F1 column in the spreadsheet.

After this I create a flow tab to copy my template file to the data file. When I ran the report Poof all my formatting issues were gone. Dates were date’s numbers were numbers. The only issue I have is that if you have a field that has a combination of numbers and text you have to make that field text . If it is a number field the SSIS package will bomb out.

Once the file exported I opened the file and deleted the hidden row. And all is good.

Long way to get there but I got there. Trust me I tried every other thing I could think of. This was the only thing that worked. I did export to xml then to excel no good. Did export to delimited file then to excel no good, did export to txt file then to excel and no good. Sent the file to an access DB then to excel and no good.

Excel 2007 is not the most graceful when exporting to it from sql.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

WayneS (8/27/2010)
Using OpenRowset/OpenDatasource, it can't be done.
However, you can accomplish the formatting by:
1. Use the Export wizard.
2. Use a SSIS package.
3. Use the sp_OA procedures to perform OLE Automation. This article might be helpful[/url], but it's not supported by Microsoft.

You could populate an Excel template using JET drivers on 32 bit machines. Dunno if they actually ever came out with the Jet drivers for 64 bit machines...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WayneS SSC Guru Points: 95460 More actions · Answer 7

Jeff Moden (8/27/2010)
WayneS (8/27/2010)
Using OpenRowset/OpenDatasource, it can't be done.
However, you can accomplish the formatting by:
1. Use the Export wizard.
2. Use a SSIS package.
3. Use the sp_OA procedures to perform OLE Automation. This article might be helpful[/url], but it's not supported by Microsoft.
You could populate an Excel template using JET drivers on 32 bit machines. Dunno if they actually ever came out with the Jet drivers for 64 bit machines...

Nope, and the Jet drivers will NOT be upgraded to 64-bit.

However, with Office 2010, you can get 64-bit drivers from Microsoft here. Haven't tried working with them yet, but it's on my to-do list to get it figured out.

Edit: and you still won't be able to do formatting with them.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

It's not a matter of formatting with them... it's a matter of not destroying formatting with them.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)