SSIS Excel Destination exporting data with leading apostrophe
i cant believe i am struggling with this so much. you would think that since SSIS and Excel are both Mirosoft products they would interact seamlessly...
I have some data in a SQL Server table which i want to export to Excel. None of the data in any of the columns in SQL server have a leading apostrophe. So the solution is not use REPLACE.
Once I create an empty file through the Excel Destination task I open the file and format the columns to what I need them to be save and close the file. I run the package when i open the file all columns are preceded with a ' e.g. 'WA or '1234. If i recreate the file again following the procedure above and i format the columns and enter data into the first row, save the file and rerun the package, the data in the excel file is inserted correctly without any leading '.
How do i remove the leading apostrophe without having to insert a template row?
What is the best practice solution for this? The data is going to be uploaded into another database at another company and they dont want the leading apostrophe's.
excel ssis
asked Nov 15 '18 at 0:06
lemlem
61
|
show 4 more comments
i cant believe i am struggling with this so much. you would think that since SSIS and Excel are both Mirosoft products they would interact seamlessly...
I have some data in a SQL Server table which i want to export to Excel. None of the data in any of the columns in SQL server have a leading apostrophe. So the solution is not use REPLACE.
Once I create an empty file through the Excel Destination task I open the file and format the columns to what I need them to be save and close the file. I run the package when i open the file all columns are preceded with a ' e.g. 'WA or '1234. If i recreate the file again following the procedure above and i format the columns and enter data into the first row, save the file and rerun the package, the data in the excel file is inserted correctly without any leading '.
How do i remove the leading apostrophe without having to insert a template row?
What is the best practice solution for this? The data is going to be uploaded into another database at another company and they dont want the leading apostrophe's.
excel ssis
asked Nov 15 '18 at 0:06
lemlem
61
1
Yes it's suprising how difficult integration is between the tools. It's due partially to the 'not quite a table, not quite a schema' nature of excel but also there are some long standing truly terrible bugs which should have been fixed long ago. My first suggestion, without knowng the background, is to export to tab separated text file instead and open that in excel. It will again perform it's own peculiar butchery on the data during this process but it might be closer to what you need.
– Nick.McDermaid
Nov 15 '18 at 1:05
Nick, we were supplying the data as .csv for a few months. They have changed the process on their side and now require the output to be excel.
– lem
Nov 15 '18 at 5:05
1
Then they're in for a world of pain because if they are importing their data, there are just as many gotchas on the import side. The only data transfer format worse than CSV is XLS.... anyway I"m sure there is a solution, I'll have a google
– Nick.McDermaid
Nov 15 '18 at 5:10
The behaviour you describe has something to so with the excel driver incorrectly guessing data types. (which will also be an issue on the import side). What happens if you pre-format all columns as "General"? Does this shed any light on it? stackoverflow.com/questions/16705342/…
– Nick.McDermaid
Nov 15 '18 at 5:13
1
If they are moving to excel because of problems with CSV then they are going the wrong way. They should be using tab seperated or somehting equally innocuous like a pipe ( | ) or squiggle ( ~ )
– Nick.McDermaid
Nov 15 '18 at 5:15
|
show 4 more comments
i cant believe i am struggling with this so much. you would think that since SSIS and Excel are both Mirosoft products they would interact seamlessly...
I have some data in a SQL Server table which i want to export to Excel. None of the data in any of the columns in SQL server have a leading apostrophe. So the solution is not use REPLACE.
Once I create an empty file through the Excel Destination task I open the file and format the columns to what I need them to be save and close the file. I run the package when i open the file all columns are preceded with a ' e.g. 'WA or '1234. If i recreate the file again following the procedure above and i format the columns and enter data into the first row, save the file and rerun the package, the data in the excel file is inserted correctly without any leading '.
How do i remove the leading apostrophe without having to insert a template row?
What is the best practice solution for this? The data is going to be uploaded into another database at another company and they dont want the leading apostrophe's.
excel ssis
asked Nov 15 '18 at 0:06
lemlem
61
i cant believe i am struggling with this so much. you would think that since SSIS and Excel are both Mirosoft products they would interact seamlessly...
I have some data in a SQL Server table which i want to export to Excel. None of the data in any of the columns in SQL server have a leading apostrophe. So the solution is not use REPLACE.
Once I create an empty file through the Excel Destination task I open the file and format the columns to what I need them to be save and close the file. I run the package when i open the file all columns are preceded with a ' e.g. 'WA or '1234. If i recreate the file again following the procedure above and i format the columns and enter data into the first row, save the file and rerun the package, the data in the excel file is inserted correctly without any leading '.
How do i remove the leading apostrophe without having to insert a template row?
What is the best practice solution for this? The data is going to be uploaded into another database at another company and they dont want the leading apostrophe's.
excel ssis
excel ssis
asked Nov 15 '18 at 0:06
lemlem
61
asked Nov 15 '18 at 0:06
lemlem
61
edited Nov 15 '18 at 5:14
lem
asked Nov 15 '18 at 0:06
lemlem
61
asked Nov 15 '18 at 0:06
lemlem
61
asked Nov 15 '18 at 0:06
lemlem
61
61
1
Yes it's suprising how difficult integration is between the tools. It's due partially to the 'not quite a table, not quite a schema' nature of excel but also there are some long standing truly terrible bugs which should have been fixed long ago. My first suggestion, without knowng the background, is to export to tab separated text file instead and open that in excel. It will again perform it's own peculiar butchery on the data during this process but it might be closer to what you need.
– Nick.McDermaid
Nov 15 '18 at 1:05
Nick, we were supplying the data as .csv for a few months. They have changed the process on their side and now require the output to be excel.
– lem
Nov 15 '18 at 5:05
1
Then they're in for a world of pain because if they are importing their data, there are just as many gotchas on the import side. The only data transfer format worse than CSV is XLS.... anyway I"m sure there is a solution, I'll have a google
– Nick.McDermaid
Nov 15 '18 at 5:10
The behaviour you describe has something to so with the excel driver incorrectly guessing data types. (which will also be an issue on the import side). What happens if you pre-format all columns as "General"? Does this shed any light on it? stackoverflow.com/questions/16705342/…
– Nick.McDermaid
Nov 15 '18 at 5:13
1
If they are moving to excel because of problems with CSV then they are going the wrong way. They should be using tab seperated or somehting equally innocuous like a pipe ( | ) or squiggle ( ~ )
– Nick.McDermaid
Nov 15 '18 at 5:15
|
show 4 more comments
1
Yes it's suprising how difficult integration is between the tools. It's due partially to the 'not quite a table, not quite a schema' nature of excel but also there are some long standing truly terrible bugs which should have been fixed long ago. My first suggestion, without knowng the background, is to export to tab separated text file instead and open that in excel. It will again perform it's own peculiar butchery on the data during this process but it might be closer to what you need.
– Nick.McDermaid
Nov 15 '18 at 1:05
Nick, we were supplying the data as .csv for a few months. They have changed the process on their side and now require the output to be excel.
– lem
Nov 15 '18 at 5:05
1
Then they're in for a world of pain because if they are importing their data, there are just as many gotchas on the import side. The only data transfer format worse than CSV is XLS.... anyway I"m sure there is a solution, I'll have a google
– Nick.McDermaid
Nov 15 '18 at 5:10
The behaviour you describe has something to so with the excel driver incorrectly guessing data types. (which will also be an issue on the import side). What happens if you pre-format all columns as "General"? Does this shed any light on it? stackoverflow.com/questions/16705342/…
– Nick.McDermaid
Nov 15 '18 at 5:13
1
If they are moving to excel because of problems with CSV then they are going the wrong way. They should be using tab seperated or somehting equally innocuous like a pipe ( | ) or squiggle ( ~ )
– Nick.McDermaid
Nov 15 '18 at 5:15
1
1
Yes it's suprising how difficult integration is between the tools. It's due partially to the 'not quite a table, not quite a schema' nature of excel but also there are some long standing truly terrible bugs which should have been fixed long ago. My first suggestion, without knowng the background, is to export to tab separated text file instead and open that in excel. It will again perform it's own peculiar butchery on the data during this process but it might be closer to what you need.
– Nick.McDermaid
Nov 15 '18 at 1:05
Yes it's suprising how difficult integration is between the tools. It's due partially to the 'not quite a table, not quite a schema' nature of excel but also there are some long standing truly terrible bugs which should have been fixed long ago. My first suggestion, without knowng the background, is to export to tab separated text file instead and open that in excel. It will again perform it's own peculiar butchery on the data during this process but it might be closer to what you need.
– Nick.McDermaid
Nov 15 '18 at 1:05
Nick, we were supplying the data as .csv for a few months. They have changed the process on their side and now require the output to be excel.
– lem
Nov 15 '18 at 5:05
Nick, we were supplying the data as .csv for a few months. They have changed the process on their side and now require the output to be excel.
– lem
Nov 15 '18 at 5:05
1
1
Then they're in for a world of pain because if they are importing their data, there are just as many gotchas on the import side. The only data transfer format worse than CSV is XLS.... anyway I"m sure there is a solution, I'll have a google
– Nick.McDermaid
Nov 15 '18 at 5:10
Then they're in for a world of pain because if they are importing their data, there are just as many gotchas on the import side. The only data transfer format worse than CSV is XLS.... anyway I"m sure there is a solution, I'll have a google
– Nick.McDermaid
Nov 15 '18 at 5:10
The behaviour you describe has something to so with the excel driver incorrectly guessing data types. (which will also be an issue on the import side). What happens if you pre-format all columns as "General"? Does this shed any light on it? stackoverflow.com/questions/16705342/…
– Nick.McDermaid
Nov 15 '18 at 5:13
The behaviour you describe has something to so with the excel driver incorrectly guessing data types. (which will also be an issue on the import side). What happens if you pre-format all columns as "General"? Does this shed any light on it? stackoverflow.com/questions/16705342/…
– Nick.McDermaid
Nov 15 '18 at 5:13
1
1
If they are moving to excel because of problems with CSV then they are going the wrong way. They should be using tab seperated or somehting equally innocuous like a pipe ( | ) or squiggle ( ~ )
– Nick.McDermaid
Nov 15 '18 at 5:15
If they are moving to excel because of problems with CSV then they are going the wrong way. They should be using tab seperated or somehting equally innocuous like a pipe ( | ) or squiggle ( ~ )
– Nick.McDermaid
Nov 15 '18 at 5:15
|
show 4 more comments
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53310628%2fssis-excel-destination-exporting-data-with-leading-apostrophe%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53310628%2fssis-excel-destination-exporting-data-with-leading-apostrophe%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Yes it's suprising how difficult integration is between the tools. It's due partially to the 'not quite a table, not quite a schema' nature of excel but also there are some long standing truly terrible bugs which should have been fixed long ago. My first suggestion, without knowng the background, is to export to tab separated text file instead and open that in excel. It will again perform it's own peculiar butchery on the data during this process but it might be closer to what you need.
– Nick.McDermaid
Nov 15 '18 at 1:05
Nick, we were supplying the data as .csv for a few months. They have changed the process on their side and now require the output to be excel.
– lem
Nov 15 '18 at 5:05
1
Then they're in for a world of pain because if they are importing their data, there are just as many gotchas on the import side. The only data transfer format worse than CSV is XLS.... anyway I"m sure there is a solution, I'll have a google
– Nick.McDermaid
Nov 15 '18 at 5:10
The behaviour you describe has something to so with the excel driver incorrectly guessing data types. (which will also be an issue on the import side). What happens if you pre-format all columns as "General"? Does this shed any light on it? stackoverflow.com/questions/16705342/…
– Nick.McDermaid
Nov 15 '18 at 5:13
1
If they are moving to excel because of problems with CSV then they are going the wrong way. They should be using tab seperated or somehting equally innocuous like a pipe ( | ) or squiggle ( ~ )
– Nick.McDermaid
Nov 15 '18 at 5:15