Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jstikal
Starting Member
19 Posts |
 Posted - 2014-07-09 : 17:05:30
|
| I would like to execute a simple find and replace of a single character within the contents of a .csv file but I'm not sure how to execute this task via SSIS. Any assistance would be great appreciated. Thank you! |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-07-10 : 07:51:31
|
In your data flow, add a derived column transformation. Choose the input column you want to change. Under the "Derived Column" heading, indicate that you want to replace the column in the data flow. Add a REPLACE expression, e.g.REPLACE( [CSV_Data] , "'", ".") |
 |
|
|
jstikal
Starting Member
19 Posts |
Posted - 2014-07-10 : 09:33:20
|
Thank you for the information. Will this work if I want to change the column header itself? I'm following the steps but does derived column only update the contents within the column? quote:
Originally posted by gbritton
In your data flow, add a derived column transformation. Choose the input column you want to change. Under the "Derived Column" heading, indicate that you want to replace the column in the data flow. Add a REPLACE expression, e.g.REPLACE( [CSV_Data] , "'", ".")
|
|
|
|
jstikal
Starting Member
19 Posts |
Posted - 2014-07-10 : 10:24:47
|
More detail...In SSRS when a .csv is generated the columns are created with an "underscore" when there is a "space" in the header name. The column header must restore the "space" in the column name. quote:
Originally posted by jstikal
Thank you for the information. Will this work if I want to change the column header itself? I'm following the steps but does derived column only update the contents within the column? quote:
Originally posted by gbritton
In your data flow, add a derived column transformation. Choose the input column you want to change. Under the "Derived Column" heading, indicate that you want to replace the column in the data flow. Add a REPLACE expression, e.g.REPLACE( [CSV_Data] , "'", ".")
|
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-07-10 : 10:52:34
|
| yes the DC transform works on the contents. There's no way to change the metadata (the column names) at runtime. OTOH if you uncheck the "first row column headers" option then the column headers would be just another piece of data, so you could work on that. But since you bring up SSRS I'm curious as to why you say "The column header must restore the "space" in the column name". SSRS can work with column names with underscores just fine. |
|
|
|
jstikal
Starting Member
19 Posts |
Posted - 2014-07-10 : 14:14:55
|
Correct SSRS can work with underscores just fine. I need the report to output the columns with a "space" and not with an "underscore" but when the report is generated as a .csv the "spaces" are replaced with "underscores". I have implemented a solution via SSIS. 1. Generated two reports2. Created a source data flat file (temp report #1) and destination data flat file (final report #2)3. Updated both data sources via the advanced config to the column names replacing the "underscore" with a "space"4. The destination report is updated with the correct column descriptionquote:
Originally posted by gbritton
yes the DC transform works on the contents. There's no way to change the metadata (the column names) at runtime. OTOH if you uncheck the "first row column headers" option then the column headers would be just another piece of data, so you could work on that. But since you bring up SSRS I'm curious as to why you say "The column header must restore the "space" in the column name". SSRS can work with column names with underscores just fine.
|
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-07-11 : 09:18:52
|
| OIC, In that case, in SSRS, you can use Expressions for the column headers to change the underscores to spaces. |
|
|
|
|
|
|
|
|
Find\Replace within a .csv