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.

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 Find\Replace within a .csv

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] , "'", ".")

Go to Top of Page

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] , "'", ".")



Go to Top of Page

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] , "'", ".")





Go to Top of Page

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.
Go to Top of Page

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 reports
2. 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 description




quote:
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.

Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -