Text qualifier issue causing data alignment problem


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Text qualifier issue causing data alignment problem
# 1  
Old 04-19-2013
Text qualifier issue causing data alignment problem

Hello Everyone,

I have a csv file with text qualifier as "" and data similar to below:

Code:
"1","abc","address1","US"
"2","def","address1 "characters in double-quote" address2","IND"
"3","ghi","address1","UK"

In above example, for record 2, we have an issue as in column3 contains double quotes within double quotes. Its causing issue in reading file and data is getting wrongly alligned for that particular record.

Is there a way to use awk or sed so that we can find the record with above issue and reject them to create a new file having only good records.

Moderator's Comments:
Mod Comment edit by bakunin: please use CODE-tags for data too. Thanks.

Last edited by bakunin; 04-19-2013 at 09:14 AM..
# 2  
Old 04-19-2013
why dont you use the , (comma) as delimiter ?

what you are trying to do ?
# 3  
Old 04-19-2013
The solution is quite simple: you search for records with more than 2 double-quotes between any two delimiter-characters (in your case ","). The following regexp should do this. Use grep to search for such lines and grep -v to search for valid lines:

Code:
/,[^,]*"[^,]*"[^,]*"/

Note that this will not find such lines if the problematic field is the first one. Search for such lines with the slightly altered regexp:

Code:
/^[^,]*"[^,]*"[^,]*"/


I hope this helps.

bakunin
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Add Blank Spaces in text, to perform beter alignment of the string

Hi Guru, I need some advice on how to add blank spaces to the code, rather than me just adding <space-bar spaces> which does not work. Current output of the code File System Backed Up - ALL_LOCAL_DRIVES Daily - Incremental Backup Schedule - 1 Month Retention • 7pm - PRD... (2 Replies)
Discussion started by: Junes
2 Replies

2. Solaris

Solaris 5.8 Upgrade is causing Date format issue.

Hi I have a JAVA based application in development and production environment. These two environments are exactly identical in terms of OS, hardware and application components. Initially the OS was Solaris 5.6 and the application was working fine in both environments. But later when the OS... (12 Replies)
Discussion started by: Jayant Tripathi
12 Replies

3. Shell Programming and Scripting

Awk script problem - Variables Causing Issue

can someone please explain to me what i'm doing wrong with this code: WELT=$(awk '(($1 ~ "^${caag}$") || ($2 ~ "^${caag}$"))' /tmp/Compare.TEXT) when run from the command line, it works. but it seems to be having a problem doing the comparison when variables are involved. i tested from... (1 Reply)
Discussion started by: SkySmart
1 Replies

4. HP-UX

HP-UX text alignment

HI all, I tried to edit my files using HP-UX but my output tends to not align when I add another character in the files to edit my files i used the command is as follow chmod +w filename vi filename Help, :eek: (1 Reply)
Discussion started by: jasonhpwong
1 Replies

5. Shell Programming and Scripting

Output alignment problem

Hi Gurus, This is my raw data. How would I able to format this output into a better alignment? /dev/vg00/lvol5 /home 0.12 GB 0.02 GB 0.10 GB 19% /dev/vg00/misc /misc 28.43 GB 4.92 GB 23.51 GB 17% /dev/vg00/lvol6 /opt 8.25 GB 5.43 GB 2.82 GB 65% /dev/vgsap/ora10264 ... (10 Replies)
Discussion started by: superHonda123
10 Replies

6. Shell Programming and Scripting

Text Alignment Problem

Dear Friends, I've one file "sample.log" with the below comma separated lines: BOND_FORWARD,0 succeeded,0 failed. EQUITY_FORWARD,0 succeeded,0 failed. FRA,12 succeeded,0 failed. OPTION_BAR,16 succeeded,0 failed. OPTION_VAN,76 succeeded,0 failed. RENTOPT_CAP_FLOOR,4775 succeeded,0 failed.... (2 Replies)
Discussion started by: ganapati
2 Replies

7. IP Networking

Could a tcp issue be causing a null pointer exception?

The client's app gets a 'suspend error' which they say is due to a null pointer exception. Application people say nothing's wrong with the app. Network people say the network's fine. I'm supposed to see what's wrong with the system to be causing this error. I checked the NIC card settings, which... (1 Reply)
Discussion started by: pmichner
1 Replies

8. UNIX for Dummies Questions & Answers

Variable for -name causing issue in Find command

Hi there, I'm trying to find files that are greater then 30 days old, zip them and move to a different directory. I'm encountering an issue passing a variable (FilesToFind) to name within the find command. Here's the code I'm running: #! /usr/bin/sh FileDir=/home/ariba... (2 Replies)
Discussion started by: ParNone
2 Replies

9. Solaris

Memory Alignment Problem on Sun Sparcs

Hi, I try to marshal a unsigned int and a char * into a buffer, and then unmarshal them later to get them out. I need to put the char * in the front and unsigned int at the end of the buffer. However, my system always give me "BUS ERROR". I am using Sun Sparcs Sloris 2.10. My code to marshal the... (1 Reply)
Discussion started by: nj302
1 Replies

10. UNIX for Dummies Questions & Answers

ls command alignment problem

With older Linux servers, the command: ls -al would output text that would have the filenames all lined up in the same column (the owner and group names were truncated to produce a uniform right column). In newer distros (i.e. RHEL 3), the command will push the right column out when owners... (0 Replies)
Discussion started by: robf
0 Replies
Login or Register to Ask a Question