Removing quotes from a CSV file with Perl

Grafbak · 07-21-2006, 03:51 AM

Dear all,

I am trying to remove some quotes from a specific string in a CSV file. (That came from the very helpfull M$ application Excel who loves to put quotes around anything that seems to be text).
The string is called $ArticleName and contains names like :

"Mounting bracket for 12"" wheel"
and
Mounting bracket 24"
and
"biba Kontiki 22"""
and
"montageplaat 12"" rechts"

This should be printed in the outcome as :
"Mounting bracket for 12" wheel"
and
"Mounting bracket 24""
and
"biba Kontiki 22""
and
"montageplaat 12" rechts"

i have written a perl script to convert the CSV file to a format that i can import in another system. All the values, like price, article_numbers etc are between quotes.

So i tried and thought about the rules for removing quotes and came up with the following :

1) If the first character of $ArticleName is a double-quote, it should be removed.
2) If [0-9]inside $ArticleName is followed directly by 2 double quotes, the last double quote in this sequence should be removed.
OR
[0-9]inside $ArticleName can only be followed by one double quote max.

This is what i have tried so far :

$_=$ItemName;
tr/([0-9]\")//d;
$ItemName=$_;
print " \"$ArticleName\" "; # I need the outer quotes

I tried different tricks with regular expressions, but i can't get the pattern that i want to define correctly and i need some help with that.

If anybody can tell me how to define a regex in Perl that matches 1 or 2 digits followed by one double quote but no more than that, you would make me a happy man. (And i can sleep this weekend).

Thank you in advance.

bigearsbilly · 07-21-2006, 04:08 AM

how about:

Code:

 s/(\d+")"/$1/g

Incidentally, with excel I have had more success with TAB delimited files.

Grafbak · 07-21-2006, 04:31 AM

Thank you for your suggestion, however it is still "doing it".
I still get a lot of quotes following one another.

I already found another, less elegant solution.
In the Excel file i replace all the quotes by the word inch.
Removing the problem instead of solving it is not my choice by heart, but im a bit under time pressure here.

I think i'll return back to this issue when the project is done.
Thank you for your help nonetheless.

bigearsbilly · 07-21-2006, 05:03 AM

how about?

Code:

billym.primadtpdev>perl -pe 's/(\d+")"(?!\s*$)/$1/' 1
"Mounting bracket for 12" wheel"
and
"Mounting bracket 24""
and
"biba Kontiki 22""
and
"montageplaat 12" rechts"

Grafbak · 07-24-2006, 03:35 PM

Thank you Billy,

sorry i couldnt reply sooner. Your tip was most helpfull, but it took me ages to figure out what it actually did.
I figured it out and it works correctly. Thanks again !

bigearsbilly · 07-25-2006, 03:23 AM

no probs