| Author |
Topic |
|
DL
Starting Member
5 Posts |
 Posted - 2011-01-27 : 18:09:07
|
| Any idea how to get rid of leading apostrophe, while exporting from SQL to Excel via SSIS?Thnx, DL |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-01-27 : 18:28:52
|
Declare @string char(10)Set @string= '''test' SELECT @string,replace(@string,'''','')You can replace it in the select statement. Are you sure you want to? it isn't displayed in excel, only makes sure numeric fields are presented as text when leading numbers in the cell (which is useful if they are larger numbers)
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
DL
Starting Member
5 Posts |
Posted - 2011-01-28 : 09:52:37
|
| Thanks for the response, dataguru1971. Unfortunately it is not about replacing apostrophe in sql - rest assured there's no apostrophe in my data. SSIS is attaching ['] automatically to every cell while exporting to Excel - that's what I need to prevent from happening.Thnx, DL |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-01-28 : 17:50:23
|
It's not actual data, it is only used by excel to show the contained cell value as text. The ' is ignored other than that. It won't affect an import or anything. For numerical data, be sure to specify the correct datatype in your dataflow/column definitions. A date going to a text column will send the date as a string (not a number)...
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
DL
Starting Member
5 Posts |
Posted - 2011-01-29 : 14:03:01
|
| "It won't affect an import" - not the case. ['] is not actually ignored and does affect all the data processing ;-(Thnx, DL |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-01-29 : 21:18:10
|
I don't see, nor recall ever seeing that issue on numerous regularly scheduled imports from excel. For pure curiosity I just exported a table to excel, saw the single quotes in the formula bar of excel, then used the wizard to import that sheet to a new table and they definitely weren't in the table.Are you importing them into SQL?...How exactly is it affecting data processing? I am curious now...I would love to see them in SQL, but it is an excel formatting...you can't control it from SSIS for text values without writing script to manipulate the workbook...and even then, you can only remove them from numbers/values/dates that might have them in excel (if the cells are formatted as text)
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
DL
Starting Member
5 Posts |
Posted - 2011-01-31 : 09:56:44
|
| Having apostrophe in excel's formula bar is all it takes to mess the 3rd party import process that I am feeding. It is a bizzar issue... I was able to resolve it by forcing string type for all unicode strings that SSIS assumed: in SSIS - Right Click "Excel Destination" --> Advanced Properties --> Input and Output properties --> in tree view select "Excel destination Input" --> External ColumnsThnx, DL |
|
|
|
DL
Starting Member
5 Posts |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-01-31 : 12:42:17
|
quote:
Originally posted by dataguru1971
be sure to specify the correct datatype in your dataflow/column definitions.
Kinda what I was getting at above regarding the datatype specs..I presumed you were already specifying the destination datatypes somehow..Glad you got it sorted out.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
donbuz
Starting Member
1 Post |
Posted - 2011-08-22 : 10:55:20
|
quote:
Originally posted by DL
Having apostrophe in excel's formula bar is all it takes to mess the 3rd party import process that I am feeding. It is a bizzar issue... I was able to resolve it by forcing string type for all unicode strings that SSIS assumed: in SSIS - Right Click "Excel Destination" --> Advanced Properties --> Input and Output properties --> in tree view select "Excel destination Input" --> External ColumnsThnx, DL
Hello DL,The simple quote ['] preceding dates actually prevent end-users of the MS-Excel file created then sent by my SSIS package to participate correctly into 'group by' feature in pivot table and any other date-based functionality.I am not sure I understand exactly how I need to convert the dates data from SQL Server (if that is necessary, by using the 'Data Conversion' transformation) and how I need to set up my 'Excel Destination' (or MS-Excel file) to properly bypass this problem.As you apparently solved this issue, I would appreciate your help on the matter,Thank you for your time and help, |
|
|
|
|
SSIS export to Excel ['] issue