SSIS export to table in Excel - numerics exported as text

Question

SSIS export to table in Excel - numerics exported as text

allen davidson

SSCertifiable

Points: 7042
More actions
July 30, 2010 at 8:56 am

#225624

Hi all, I have seen a few post around the subject of data type problems when exporting to Excel. My problem is INT data exporting as character data (green flag warning in Excel after export).
The most promising post was in this forum where the suggestion was to edit the table definition in the create table dialogue within SISS and change INT to LONG. This works fine until I change things in the Excel sheet where the table is , i.e. I widened the columns and the subsequent export converted my INT data to text.
Anyone got other suggestions, I have seen and tried the option about how Jet samples the first few rows but this seems to apply more to importing from Excel, not Exporting.
Thanks
Allen

Viewing 6 posts - 1 through 5 (of 5 total)

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

Baskar B.V SSCrazy Points: 2823 More actions · Answer 1

allen davidson (7/30/2010)
Hi all, I have seen a few post around the subject of data type problems when exporting to Excel. My problem is INT data exporting as character data (green flag warning in Excel after export).
The most promising post was in this forum where the suggestion was to edit the table definition in the create table dialogue within SISS and change INT to LONG. This works fine until I change things in the Excel sheet where the table is , i.e. I widened the columns and the subsequent export converted my INT data to text.
Anyone got other suggestions, I have seen and tried the option about how Jet samples the first few rows but this seems to apply more to importing from Excel, not Exporting.
Thanks
Allen

If you select the existing original worksheet you can get into this kind of issue. Instead try to create the new worksheet from the excel destination component and use it for import. In that case integet value will not get displayed in TEXT format.

[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font]

allen davidson SSCertifiable Points: 7042 More actions · Answer 2

Thanks Baskar - If i understand correctly - run the create table in the SSIS data destination then copy the resuting tab and make changes to the copy.

I'll try that!

Cheers

Allen

Baskar B.V SSCrazy Points: 2823 More actions · Answer 3

allen davidson (7/30/2010)
Thanks Baskar - If i understand correctly - run the create table in the SSIS data destination then copy the resuting tab and make changes to the copy.
Cheers
Allen

What i meant to say is in your excel destination which you would have selected as data flow destination and click on the "new" button next to the "Name of the excel sheet". That would create a new worksheet inside the excel document. After you done with the data mapping the data would get loaded into the new worksheet inside the excel document without the format as TEXT. Let us know if you have any question.

[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font]

allen davidson SSCertifiable Points: 7042 More actions · Answer 4

Thanks for clarification Baskar. Will try monday & post back.

Cheers

allen davidson SSCertifiable Points: 7042 More actions · Answer 5

Thanks Baskar, works a treat - I think I was making things difficult for myself by changing the column definitions in the generated create table script.

Cheers

Allen