Avatar of ayoZen
ayoZen

asked on 

Replace Function for Commas

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
Microsoft SQL Server 2005

Avatar of undefined
Last Comment
ayoZen
Avatar of anillucky31
anillucky31
Flag of India image

can u share ur csv file?
'
Avatar of G Trurab Khan
G Trurab Khan
Flag of Pakistan image

use single quotes instead of double quotes in

 Replace(title, ',', ' ')
Avatar of ayoZen
ayoZen

ASKER

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
Avatar of ayoZen
ayoZen

ASKER

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)".

Avatar of anillucky31
anillucky31
Flag of India image

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.
Avatar of ayoZen
ayoZen

ASKER

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.  
ASKER CERTIFIED SOLUTION
Avatar of anillucky31
anillucky31
Flag of India image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of ayoZen
ayoZen

ASKER

Thank you!
Microsoft SQL Server 2005
Microsoft SQL Server 2005

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

72K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo