Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Modify comma delimited file columns.

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Modify comma delimited file columns.
# 1  
Old 01-26-2017
Lightbulb Modify comma delimited file columns.
Please help me to update a file which contains date values as below:-

From:-
Code:
"1912108",20161130,"2016-12-01-00.00.00.000000","2016-12-01-08.37.12.000000"
"1912108",20161201,"2016-12-02-00.00.00.000000","2016-12-02-08.28.22.000000"

To:-
Code:
"1912108",2016-11-30,"2016-12-01 00.00.00.000000","2016-12-01 08.37.12.000000"
"1912108",2016-12-01,"2016-12-02 00.00.00.000000","2016-12-02 08.28.22.000000"

Moderator's Comments:
Mod Comment Please use CODE tags (as required by forum rules) when displaying sample input, sample output, and code segments.
Last edited by Don Cragun; 01-26-2017 at 12:41 AM.. Reason: Add CODE tags.
# 2  
Old 01-26-2017
Quote:
Originally Posted by KrishnaVM
Please help me to update a file which contains date values as below:-

From:-
Code:
"1912108",20161130,"2016-12-01-00.00.00.000000","2016-12-01-08.37.12.000000"
"1912108",20161201,"2016-12-02-00.00.00.000000","2016-12-02-08.28.22.000000"

To:-
Code:
"1912108",2016-11-30,"2016-12-01 00.00.00.000000","2016-12-01 08.37.12.000000"
"1912108",2016-12-01,"2016-12-02 00.00.00.000000","2016-12-02 08.28.22.000000"

Moderator's Comments:
Mod Comment Please use CODE tags (as required by forum rules) when displaying sample input, sample output, and code segments.
Is this a homework assignment? Homework and coursework questions can only be posted in the Homework & Coursework forum under special homework rules.

If this is not homework, please explain the company you work for and the nature of the problem you are working on. And please show us (in CODE tags) what you have tried to do to solve this problem on your own and tell us what operating system and shell you're using.

If you did post homework in the main forums, please review the guidelines for posting homework and repost.
This User Gave Thanks to Don Cragun For This Post:
rbatte1
# 3  
Old 01-26-2017
Modify comma delimited file columns
Hi Admin,

This isn't a home work.
I am dealing with a flat file on RedHat Linux Server, which has millions of records (rows) and about 65 columns. Out of which only 3 columns needs this correction as below:-

From:-
Code:
"1912108",20161130,"2016-12-01-00.00.00.000000","2016-12-01-08.37.12.000000"
"1912108",20161201,"2016-12-02-00.00.00.000000","2016-12-02-08.28.22.000000"

To:-
Code:
"1912108",2016-11-30,"2016-12-01 00.00.00.000000","2016-12-01 08.37.12.000000"
"1912108",2016-12-01,"2016-12-02 00.00.00.000000","2016-12-02 08.28.22.000000"

I tried to format this as below, which works fine with the files that has few columns:-

Code:
awk -F "," '{print $1"|"substr($2,1,4)"-"substr($2,5,2)"-"substr($2,7,2)"|"substr($3,1,11)" "substr($3,13,26)"|"substr($4,1,11)" "substr($4,13,26)}' filename

Seeking help from this forum for a better solution, to deal with the file that has many columns in which case it is not a good idea to include all the columns under AWK print statement.

Thanks.
Last edited by rbatte1; 01-26-2017 at 09:34 AM.. Reason: Added code tags
# 4  
Old 01-26-2017
Hi, try using a regular expression instead..:
Code:
sed 's/\(-[^-]*-[^-]*\)-/\1 /g' file

or:
Code:
perl -pe 's/(-.*?-.*?)-/$1 /g' file


or since it is a date and it is always two character between the dashes this may suffice:
Code:
sed 's/\(-..-..\)-/\1 /g' file

or:
Code:
perl -pe 's/(-..-..)-/$1 /g' file

or:
Code:
perl -pe 's/(?<=-..-..)-/ /g' file

Last edited by Scrutinizer; 01-26-2017 at 10:10 AM..
This User Gave Thanks to Scrutinizer For This Post:
KrishnaVM
# 5  
Old 01-26-2017
To allow for the correction of field 2, try
Code:
sed  's/\(-[^-]*-[^-]*\)-/\1 /g; s/^\([^,]*,[0-9]\{4\}\)\([0-9]\{2\}\)\([0-9]\{2\}\)/\1-\2-\3/' file
"1912108",2016-11-30,"2016-12-01 00.00.00.000000","2016-12-01 08.37.12.000000"
"1912108",2016-12-01,"2016-12-02 00.00.00.000000","2016-12-02 08.28.22.000000"

This User Gave Thanks to RudiC For This Post:
KrishnaVM
# 6  
Old 01-26-2017
It worked. Thanks a lot.
Code:
$sed  's/\(-[^-]*-[^-]*\)-/\1 /g; s/^\([^,]*,[0-9]\{4\}\)\([0-9]\{2\}\)\([0-9]\{2\}\)/\1-\2-\3/' test1
"1912108",2016-11-30,"2016-12-01 00.00.00.000000","2016-12-01 08.37.12.000000"
"1912108",2016-12-01,"2016-12-02 00.00.00.000000","2016-12-02 08.28.22.000000"

---------- Post updated at 10:59 AM ---------- Previous update was at 09:22 AM ----------

This worked well with timestamp columns (3 and 4).
In my flat file, even though I have them numbered as 64 and 65 th columns.

I am trying to tweak this logic to make it work (without success) - with other flat file that has the date column (field 2) as a field 3:-

Code:
"1080909","ABC",20161031,"2016-09-02-00.00.00.000000","2016-09-02-08.30.05.000000"
"1080091","XYZ",20160830,"2016-11-02-00.00.00.000000","2016-11-02-08.27.48.000000"

I am very new to AWK and SED. Can you please help.


Moderator's Comments:
Mod Comment Seriously, for the fourth time: PLEASE use CODE tags as required by forum rules!
Last edited by RudiC; 01-26-2017 at 12:05 PM.. Reason: Added CODE tags for the fourth time.
# 7  
Old 01-26-2017
That's becoming nasty, see:
Code:
sed  -r 's/^(([^,]*,){2})([0-9]{4})([0-9]{2})([0-9]{2})/\1\3-\4-\5/; s/(-..-..)-/\1 /g' file
"1080909","ABC",2016-10-31,"2016-09-02 00.00.00.000000","2016-09-02 08.30.05.000000"
"1080091","XYZ",2016-08-30,"2016-11-02 00.00.00.000000","2016-11-02 08.27.48.000000

This User Gave Thanks to RudiC For This Post:
KrishnaVM
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Awkscript to reduce words delimited with comma on right hand to columns

I have a large database with the following structure: Indicword,Indicword,Indicword=English on a line. Not all lines will have this structure. Some might have a single word mapping to a single word in Indic. An example will make this clear ... (4 Replies)
Discussion started by: gimley
4 Replies

2. Shell Programming and Scripting

Help/Advise please for converting space delimited string variable to comma delimited with quote

Hi, I am wanting to create a script that will construct a SQL statement based on a a space delimited string that it read from a config file. Example of the SQL will be For example, it will read a string like "AAA BBB CCC" and assign to a variable named IN_STRING. I then concatenate... (2 Replies)
Discussion started by: newbie_01
2 Replies

3. Shell Programming and Scripting

Transpose comma delimited data in rows to columns

Hello, I have a bilingual database with the following structure a,b,c=d,e,f The right half is in a Left to right script and the second is in a Right to left script as the examples below show What I need is to separate out the database such that the first word on the left hand matches the first... (4 Replies)
Discussion started by: gimley
4 Replies

4. Shell Programming and Scripting

Need a script to convert comma delimited files to semi colon delimited

Hi All, I need a unix script to convert .csv files to .skv files (changing a comma delimited file to a semi colon delimited file). I am a unix newbie and so don't know where to start. The script will be scheduled using cron and needs to convert each .csv file in a particular folder to a .skv... (4 Replies)
Discussion started by: CarpKing
4 Replies

5. Shell Programming and Scripting

Comma delimited file manipulation

Question about how to change the first & last name in column one & two so that the names have a capital letter for just the first letter. Example: asdf@asdf.com,asdf,asdfasdf,176.23.22.345,4/12/2012 changed to: asdf@asdf.com,Asdf,Asdfasdf,176.23.22.345,4/12/2012 Thank you kindly, Nick (2 Replies)
Discussion started by: nickytcom
2 Replies

6. UNIX for Dummies Questions & Answers

Flat File - Comma Delimited

I have a flat file whose contents are comma delimited and there are 84 columns in total, so everytime I try to view the contents, things get over lapped it becomes diffcult to read through the result set. Is there a command / what would be the best way...if I want to view the results alligned... (4 Replies)
Discussion started by: priya33184
4 Replies

7. Shell Programming and Scripting

Urgent - Comma delimited file comparision.

Query: There are two files as below – /home/rgupta/input/file.txt.arch (source file) /home/rgupta/output/file.txt (destination file) Files details are attached for the reference. Scenario: File /home/rgupta/input/file.txt.arch picked up by an application xyz. Application xyz does... (1 Reply)
Discussion started by: ravigupta2u
1 Replies

8. UNIX for Dummies Questions & Answers

Comma delimited file

Hi All, I have output of sql saved in comma separated file. Now i need to read line by line this file and assign word to a unix variable for further processing Eg: Test file world, 1, 3, 4 earth,2,3,4,5 moon,1,2,3,4 Output should be word1= world word2=1 echo " first word... (7 Replies)
Discussion started by: gwrm
7 Replies

9. Shell Programming and Scripting

Converting Tab delimited file to Comma delimited file in Unix

Hi, Can anyone let me know on how to convert a Tab delimited file to Comma delimited file in Unix Thanks!! (22 Replies)
Discussion started by: charan81
22 Replies

10. Shell Programming and Scripting

Comma Delimited file

I have a comma delimited file that sometimes has addresses details in. The problem is that the address detail can be seen as: "Sample House, Sample Road". When I run a script specifying the file is comma delimited I would like it to ignore comma's that are in between speech marks. Is this... (2 Replies)
Discussion started by: dbrundrett
2 Replies
Login or Register to Ask a Question