Sponsored Content
Top Forums Shell Programming and Scripting awk to parse comma separated field and removing comma in between number and double quotes Post 303015823 by as7951 on Friday 13th of April 2018 03:35:38 AM
Old 04-13-2018
awk to parse comma separated field and removing comma in between number and double quotes

Hi Experts,

Please support
I have below data in file in comma seperated, but 4th column is containing comma in between numbers, bcz of which when i tried to parse the file the column 6th value(5049641141) is being removed from the file and value(222.82) in column 5 becoming value of column6.

Below is command used

Code:
awk -F"," -v OFS="|" '{gsub(/\,/,"",$4);gsub(/\"/,"");gsub(/^[[:space:]]+|[[:space:]]+$/,"",$4);gsub(/^[[:space:]]+|[[:space:]]+$/,"",$5);print $1, $2, $3, $4, $5, $6}' input.csv > output.csv

Input file
Code:
Bill.Doc.,Item,Exch.Rate,    Net value, Tax amount,Sales Doc.
6400392211,10,1," 1,271.19 ", 228.82 ,5049641141
6400392212,10,1, 635.59 , 114.40 ,5049641143
6400392213,10,1, 635.59 , 114.40 ,5049641145
6400392214,10,1," 1,271.19 ", 228.82 ,5049641147

output file(from command mentioned above)
Code:
Bill.Doc. |Item|Exch.Rate|Net value|Tax amount|Sales Doc.
6400392211|10|1|1|271.19|228.82
6400392212|10|1|635.59|114.40|5049641143
6400392213|10|1|635.59|114.40|5049641145
6400392214|10|1|1|271.19|228.82
6400392215|10|1|0.01|-|5049641149
6400392216|10|1|4.23|0.76|5049641151

Expected ouptut
Code:
Bill.Doc. |Item|Exch.Rate|Net value|Tax amount|Sales Doc.
6400392211|10|1|1271.19|228.82|5049641141
6400392212|10|1|635.59|114.40|5049641143
6400392213|10|1|635.59|114.40|5049641145
6400392214|10|1|1271.19|228.82|5049641147
6400392215|10|1|0.01|-|5049641149
6400392216|10|1|4.23|0.76|5049641151

 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Unix shell script to parse the contents of comma-separated file

Dear All, I have a comma-separated file. 1. The first line of the file(header) should have 4 commas(5 fields). 2. The last line of the file should have 1 comma(2 fields). Pls help me in checking this condition in a shell script. And the number of lines between the first line and last... (11 Replies)
Discussion started by: KrishnaSaran
11 Replies

2. Shell Programming and Scripting

sed removing comma inside double quotes

I have a csv file with lines like the followings 123456,"ABC CO., LTD","XXX" 789012,"DEF LIMITED", "XXX" before I bcp this file to database, the comma in "CO.," need to be removed first. My script is cat <filename> | sed 's/"CO.,"/"CO."/g' but it doesn't work. Can anyone here able to... (2 Replies)
Discussion started by: joanneho
2 Replies

3. Shell Programming and Scripting

Parse apart strings of comma separated data with varying number of fields

I have a situation where I am reading a text file line-by-line. Those lines of data contain comma separated fields of data. However, each line can vary in the number of fields it can contain. What I need to do is parse apart each line and write each field of data found (left to right) into a file.... (7 Replies)
Discussion started by: 2reperry
7 Replies

4. Shell Programming and Scripting

Its PERL + Comma separated seventh field

Hi Friends, I'm working on a perl script, which seems to be simpler. But I'm very new to PERL scripting. I have a comma separated data file, from which I need to extract only the seventh field data out of available twenty fields to an array using perl. Any help would be much appreciated. ... (17 Replies)
Discussion started by: ganapati
17 Replies

5. Shell Programming and Scripting

How to format file into comma separated field

Guys, Need you help, i have a a file content that look like this. Nokia 3330 <spaces><spaces><more spaces>+76451883874 Nokia 3610 +87467361615 so on and so forth, - there are so many spaces in between. - e.g.... (5 Replies)
Discussion started by: shtobias
5 Replies

6. Shell Programming and Scripting

Help parse comma separated list

I have a list of files with the same name, but they have a different date stamp in the name. I can find the first file, but I need to find the second file. I am using this information to create a variable I use later. Here is a example of how I find the first file. "ls -mr... (11 Replies)
Discussion started by: NoMadBanker
11 Replies

7. Shell Programming and Scripting

awk, comma as field separator and text inside double quotes as a field.

Hi, all I need to get fields in a line that are separated by commas, some of the fields are enclosed with double quotes, and they are supposed to be treated as a single field even if there are commas inside the quotes. sample input: for this line, 5 fields are supposed to be extracted, they... (8 Replies)
Discussion started by: kevintse
8 Replies

8. Shell Programming and Scripting

Perl script to parse output and print it comma separated

I need to arrange output of SQL query into a comma separated format and I'm struggling with processing the output... The output is something like this: <Attribute1 name><x amount of white spaces><Atribute value> <Attribute2 name><x amount of white spaces><Atribute value> <Attribute3... (2 Replies)
Discussion started by: Juha
2 Replies

9. Shell Programming and Scripting

Need Help - comma inside double quote in comma separated csv,

Hello there, I have a comma separated csv , and all the text field is wrapped by double quote. Issue is some text field contain comma as well inside double quote. so it is difficult to process. Input in the csv file is , 1,234,"abc,12,gh","GH234TY",34 I need output like below,... (8 Replies)
Discussion started by: Uttam Maji
8 Replies

10. Shell Programming and Scripting

awk print - fields separated with comma's need to ignore inbetween double quotes

I am trying to re-format a .csv file using awk. I have 6 fields in the .csv file. Some of the fields are enclosed in double quotes and contain comma's inside the quotes. awk is breaking this into multiple fields. Sample lines from the .csv file: Device Name,Personnel,Date,Solution... (1 Reply)
Discussion started by: jxrst
1 Replies
DOC(4)                                                       Kernel Interfaces Manual                                                       DOC(4)

NAME
DOC - (Pilot standard text document) file format SYNOPSIS
struct doc_record0 { /* 16 bytes total */ Word version; /* 1 = plain text, 2 = compressed text */ Word reserved1; DWord doc_size; /* uncompressed size in bytes */ Word num_recs; /* not counting itself */ Word rec_size; /* in bytes: usually 4096 (4K) */ DWord reserved2; }; DESCRIPTION
The Doc file format is the standard text document format used by all models of the Palm Pilot. A Doc file is a pdb(4) file: this manual page describes only those aspects specific to Doc files. A Doc file consists of 0 to num_recs records; record 0 is the header for the document. (This header is distinct from the pdb(4) header.) The remaining records contain text, either plain or compressed depending upon version. Word Sizes In the synopsis above, the types ``Word'' and ``DWord'' are used just as in the Pilot headers. The type ``Word'' is 16 bits; the type ``DWord'' is 32 bits. Both are in big-endian format. Compression Format A character ``c'' in a compressed record is in one of four classes: 01-08 Copy ``c'' bytes 00,09-7F Self 80-BF Sequence C0-FF A space plus the ASCII character ``c ^ 0x80'' SEE ALSO
txt2pdbdoc(1), html2pdbtxt(1), pdbtxt2html(1), pdb(4) Christopher Bey and Kathleen Dupre. Palm File Format Specification, Document Number 3008-003, Palm, Inc., May 16, 2000. AUTHOR
Paul J. Lucas <pauljlucas@mac.com> txt2pdbdoc January 21, 2005 DOC(4)
All times are GMT -4. The time now is 07:41 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy