How to force column explicitly to string type before we export to excel

Question

How to force column explicitly to string type before we export to excel

Viewing 12 posts - 16 through 27 (of 27 total)

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

Phil Parkin SSC Guru Points: 247233 More actions · Answer 1

komal145 (10/4/2016)
the columns are overlapped in excel. so is there a way taht it is not overlapped while exporting to excel through ssis?

No. The overlapping has nothing to do with the way in which the export is performed.

Thom A SSC Guru Points: 99143 More actions · Answer 2

Is SSIS therefore the right choice to export your data? it almost sounds like you're just simply exporting data out of a SQL table.

Would it not be easier to export it by a different means, for example a simple formatted Excel document in Reporting Services, where you can control the formatting of the document. SSIS has no way of formatting the document, as it's not designed for that type of process.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Eric M Russell SSC Guru Points: 125625 More actions · Answer 3

Excel is a marvelous tool for presentation and analysis, but .XLS and .XLSX are terrible formats for data interchange.

There have been cases where the business would submit excel documents for import into the database and then complain because some rows appeared missing. It turns out that they were Hiding ranges of rows inside the sheet, I guess to make data entry more manageable. As it turns out, SSIS ignores Hidden rows whem importing Excel sheets, treating them as deleted. There were other occasions where the user would highlight a row within the sheet and Strikethrough the text. To them, the row was visually "deleted", but such formatting has no contextual meaning for SSIS and it is still imported.

Another thing that will happen with Excel is that "numeric" values containing an leading space will be excluded from aggregate function like @SUM or @COUNT. It's very easy when entering data to accidentally hit the space bar, and it's difficult to miss when looking at the data formatted with a proportional font. There have been high profile cases where corporate financials or economic forecasts have been published to the public domain but later redacted because it was later discovered to contain Excel data entry errors.

Excel errors and science papers

http://www.economist.com/blogs/graphicdetail/2016/09/daily-chart-3

Fixing this Excel error transforms high-debt countries from recession to growth

http://qz.com/75035/fixing-this-excel-error-transforms-high-debt-countries-from-recession-to-growth/

If there is one feature I'd love to see added is the ability to stronly type worksheet columns, meaning for example that all values within a column would be constrained to integer values, valid dates or text, behaving more like a flat database table.

For this reason, I prefer TAB Delimited for data interchange or for publishing raw data.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Phil Parkin SSC Guru Points: 247233 More actions · Answer 4

If there is one feature I'd love to see added is the ability to stronly type worksheet columns, meaning for example that all values within a column would be constrained to integer values, valid dates or text, behaving more like a flat database table.

I'd like to propose this counter feature: the complete removal of all pseudo-database functionality in Excel

Then maybe people will start using Access instead. I know how much we all love Access here, though I think we'll agree that it's a huge step up from Excel for data purposes.

Eric M Russell SSC Guru Points: 125625 More actions · Answer 5

Phil Parkin (10/5/2016)
If there is one feature I'd love to see added is the ability to stronly type worksheet columns, meaning for example that all values within a column would be constrained to integer values, valid dates or text, behaving more like a flat database table.
I'd like to propose this counter feature: the complete removal of all pseudo-database functionality in Excel
Then maybe people will start using Access instead. I know how much we all love Access here, though I think we'll agree that it's a huge step up from Excel for data purposes.

Excel is an essential tool because of it's simplicity, but the downside is it's flexibility. The option of strong datatyping and constraints would benefit Excel, even outside the context of data exchange.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Phil Parkin SSC Guru Points: 247233 More actions · Answer 6

Eric M Russell (10/5/2016)
Phil Parkin (10/5/2016)
If there is one feature I'd love to see added is the ability to stronly type worksheet columns, meaning for example that all values within a column would be constrained to integer values, valid dates or text, behaving more like a flat database table.
I'd like to propose this counter feature: the complete removal of all pseudo-database functionality in Excel
Then maybe people will start using Access instead. I know how much we all love Access here, though I think we'll agree that it's a huge step up from Excel for data purposes.
Excel is an essential tool because of it's simplicity, but the downside is it's flexibility. The option of strong datatyping and constraints would benefit Excel, even outside the context of data exchange.

And my suggestion was tongue-in-cheek;-)

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 7

Phil Parkin (10/5/2016)
Eric M Russell (10/5/2016)
Phil Parkin (10/5/2016)
If there is one feature I'd love to see added is the ability to stronly type worksheet columns, meaning for example that all values within a column would be constrained to integer values, valid dates or text, behaving more like a flat database table.
I'd like to propose this counter feature: the complete removal of all pseudo-database functionality in Excel
Then maybe people will start using Access instead. I know how much we all love Access here, though I think we'll agree that it's a huge step up from Excel for data purposes.
Excel is an essential tool because of it's simplicity, but the downside is it's flexibility. The option of strong datatyping and constraints would benefit Excel, even outside the context of data exchange.
And my suggestion was tongue-in-cheek;-)

Great idea! And, enough people adopt Acess, maybe we can't get rid of SQL Server. 😀

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

komal145 SSCrazy Eights Points: 9920 More actions · Answer 8

Thanks everyone for taking time and replying , this export to excel is not direct , i have check other tables and do lot of stuff to get this point . So , i have to do it in ssis.

I have faced issues and facing issues with excel from long time.

Thanks for providing useful information about the stuff. I always appreciate your help and this is great community with so many talented people helping other people to learn.

Jeff Moden SSC Guru Points: 1004717 More actions · Answer 9

komal145 (10/5/2016)
i have check other tables and do lot of stuff to get this point . So , i have to do it in ssis.

Not really. You can do all of the hard stuff in T-SQL and then just use SSIS to export the result. I even avoid that part of SSIS by using the ACE Drivers.

--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)

Eric M Russell SSC Guru Points: 125625 More actions · Answer 10

Jeff Moden (10/5/2016)
komal145 (10/5/2016)
i have check other tables and do lot of stuff to get this point . So , i have to do it in ssis.
Not really. You can do all of the hard stuff in T-SQL and then just use SSIS to export the result. I even avoid that part of SSIS by using the ACE Drivers.

Jeff, you're not a fan of SSIS, are you? 🙂

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Phil Parkin SSC Guru Points: 247233 More actions · Answer 11

Eric M Russell (10/6/2016)
Jeff Moden (10/5/2016)
komal145 (10/5/2016)
i have check other tables and do lot of stuff to get this point . So , i have to do it in ssis.
Not really. You can do all of the hard stuff in T-SQL and then just use SSIS to export the result. I even avoid that part of SSIS by using the ACE Drivers.
Jeff, you're not a fan of SSIS, are you? 🙂

After many years of fighting, Jeff has, I suspect, developed a Pavlovian twitch reaction whenever he sees the letters SSIS.

Jeff Moden SSC Guru Points: 1004717 More actions · Answer 12

Phil Parkin (10/6/2016)
Eric M Russell (10/6/2016)
Jeff Moden (10/5/2016)
komal145 (10/5/2016)
i have check other tables and do lot of stuff to get this point . So , i have to do it in ssis.
Not really. You can do all of the hard stuff in T-SQL and then just use SSIS to export the result. I even avoid that part of SSIS by using the ACE Drivers.
Jeff, you're not a fan of SSIS, are you? 🙂
After many years of fighting, Jeff has, I suspect, developed a Pavlovian twitch reaction whenever he sees the letters SSIS.

Heh...

"NIAGRA FALLS!!!! Slowly I turned... step by step... inch by inch..."

--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)