Replace Function for Commas

ayoZen
ayoZen used Ask the Experts™
on
I have a small dtsx pacakge that parses data from looped csv files. There are three columns that I am focusing on in the CSV file (which is attached). 05-13-11-Data-Flow-for-the-ASRUN.pdf

I noticed that some of the data in the CSV files are not parsing correctly.

I believe that the commas in the production field are causing the problems.  Which is why I added a derived column where I am using Replace, but this does not seem to be doing the job.  05-13-11-Data-Flow-for-the-ASRUN.pdf

Please advise on what could be causing the error.  
05-13-11-parsing-of-data.txt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
can u share ur csv file?
'
G Trurab KhanSnr. Development Manager

Commented:
use single quotes instead of double quotes in

 Replace(title, ',', ' ')

Author

Commented:
I tried a few combinations with the replace function, but nothing still seems to work.

I am also attaching a sample of one of the many csv files that my package processes.  

Thanks.  sample-csv-asrun.csv
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
Some of the media  may have several commas which creates a problem, since the file is a csv and the comma usually denotes a break in the field.  How can I by pass this error?

My current replace function is:

REPLACE(title,","," ")

And when I try the following replace function:

Replace(title, ',', ' ')

I get the following error message:

Error at Asrun Data Flow Task [Replace commas with space [230]]: Parsing the expression "Replace(title, ',', ' ')" failed. The single quotation mark at line number "1", character number "16", was not expected.

Error at Asrun Data Flow Task [Replace commas with space [230]]: Cannot parse the expression "Replace(title, ',', ' ')". The expression was not valid, or there is an out-of-memory error.

Error at Asrun Data Flow Task [Replace commas with space [230]]: The expression "Replace(title, ',', ' ')" on "input column "title" (245)" is not valid.

Error at Asrun Data Flow Task [Replace commas with space [230]]: Failed to set property "Expression" on "input column "title" (245)".

you can do it manually. Open your csv with microsoft excel.  When you will view this csv in excel you will not find comma (,) in file except for those columns which has multiple comma. In excel search for comma (,) and replace it with some other character.  then save this as new file and hen import this file.

It will work. I tried with your sample file and it worked.

Pragmatically how to do this i m not able to figure out yet.

For time being you can proceed like this until some expert gives you programmatic solution.

Author

Commented:
I understand that this can be done manually, but I have a client that won't do this, so I have to find a better solution to compensate for the problem.  I also have thousands of files that the client pulls for processing.  
ayoZen,

I am able to solve this problem pragmatically. While doing wizard you have to select double quote " as Text Qualifier .

See the attached file. Remember put double quote only once in Text Qualifier. (do not put "")

Let me know if it helps. I think then you need not to have derived columns.
TextQualifier.GIF

Author

Commented:
Thank you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial