Solved

awk, sed or regex to process csv file

Posted on 1998-01-02
4
419 Views
Last Modified: 2013-12-26
perl is not available to me and i have an excel-generated csv file that i need to process as follows:
  1.  remove all commas within double-quotes
      (but retain comma delimiters)
  2.  remove all double-quotes
  3.  replace all spaces in each field with @!%

sample record:
1,22222222,333,"company, Inc.",,444,USPS,company name,,123 elm st,Ste 800,Miami,FL,33131,


can anyone help with this?
0
Comment
Question by:luke_airig
  • 2
4 Comments
 

Author Comment

by:luke_airig
ID: 1295888
Edited text of question
0
 
LVL 84

Expert Comment

by:ozo
ID: 1295889
If your sed is POSIX conforming,
 s/\(.*\)/"\1"/; s/\("\([^"]*\)"\)*,*/\2/g; s/ /@!%/g
should produce
1,22222222,333,company@!%Inc.,,444,USPS,company@!%name,,123@!%elm@!%st,Ste@!%800,Miami,FL,33131,
from your sample record.
0
 

Accepted Solution

by:
ashishkh earned 50 total points
ID: 1295890
Try this script:
sed -e "s/\("\([^",]*\),\([^",]*\)"\)/\2\3/g; s/ /@!%/g" infile

0
 
LVL 84

Expert Comment

by:ozo
ID: 1295891
ashishkh, that works with a single quoted comma,
(assuming your shell can handle the -e quoting,
sed -e 's/\("\([^",]*\),\([^",]*\)"\)/\2\3/g; s/ /@!%/g'
or
sed -e 's/\("\([^",]*\),\([^",]*\)"\)/\2\3/g; s/ /@\!%/g'
may work better if not)
but not with multiple commas, as in
"a,b,c","d,e,f"

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Running shell command from Access 3 69
matchUp  challenge 9 94
Fibonacci challenge 11 110
Problem to event 3 78
Introduction: Dynamic window placements and drawing on a form, simple usage of windows registry as a storage place for information. Continuing from the first article about sudoku.  There we have designed the application and put a lot of user int…
Introduction: The undo support, implementing a stack. Continuing from the eigth article about sudoku.   We need a mechanism to keep track of the digits entered so as to implement an undo mechanism.  This should be a ‘Last In First Out’ collec…
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now