Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Help Manipulating Large Csv File

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Help Manipulating Large Csv File
# 1  
Old 09-01-2010
Question Help Manipulating Large Csv File
Hello everyone,

I am trying to manipulate a large .csv file where I have output similar to the following -

Image

The result that I am looking for would be to consolidate the first column, but combine the second and third column so it still relates to the first. I need the output to be a csv delimited by semi-colons.

So the result I am looking for would be
Code:
Key1;FNAME1;LNAME1;
Key2;FNAME2 FNAME2.1 FNAME2.2;LNAME2 LNAME2.1 LNAME2.2;
Key3;FNAME3 FNAME3.1;LNAME3 LNAME3.1;

What would be the best way of going about this? I would like to use sed/awk in cygwin. Any help would be very much appreciated.
Last edited by radoulov; 09-01-2010 at 04:17 PM.. Reason: Please use code tags!
# 2  
Old 09-01-2010
Code:
awk 'END {
  print p, c2, c3, x
  }
NF == 3 && p && p != $1 {
  print p, c2, c3, x
  c2 = c3 = x
  }
{ 
  r2 = $(NF - 1); r3 = $NF
  c2 = c2 ? c2 FS r2 : r2
  c3 = c3 ? c3 FS r3 : r3
  NF == 3 && p = $1 
  }' OFS=\; infile

This User Gave Thanks to radoulov For This Post:
xxwohxx
# 3  
Old 09-01-2010
Quote:
Originally Posted by radoulov
Code:
awk 'END {
  print p, c2, c3, x
  }
NF == 3 && p && p != $1 {
  print p, c2, c3, x
  c2 = c3 = x
  }
{ 
  r2 = $(NF - 1); r3 = $NF
  c2 = c2 ? c2 FS r2 : r2
  c3 = c3 ? c3 FS r3 : r3
  NF == 3 && p = $1 
  }' OFS=\; infile

Very nice, thanks, this seems to work.... the output looks like.

Key1, FNAME1, LNAME1, Key2, FNAME2, LNAME2, FNAME2.1, LNAME2.1, FNAME2.2, LNAME2.2, Key3, FNAME3, LNAME3, FNAME3.1, LNAME3.1..........

How can I add line breaks towards the end of each key so I get this output?

Key1, FNAME1, LNAME1;
Key2, FNAME2, FNAME2.1, FNAME2.2, LNAME2, LNAME2.1, LNAME2.2;
Key3, FNAME3, FNAME3.1, LNAME3, LNAME3.1;

Thanks again!
# 4  
Old 09-01-2010
This is strange. Could you post a sample from your real input data?
# 5  
Old 09-01-2010
Sure, Here is a sample of the dataset.

Image
# 6  
Old 09-01-2010
Could you please post it in text format using code tags, so I can copy/paste?
# 7  
Old 09-01-2010
yeah, sorry, just realized thats probably what you wanted.

Code:
000C9E6539BD420B55BB25237D92AEA2B5D2E3B4,5273609,YA30XU023-00346353
000DE4C7502B5095936B5E5065C0425D9A2AEA2A,7055345,YA30XU022-00437332
,7305733,YA30XU022-00440572
,7432535,YA30XU023-00000537
,7654704,YA30XU023-00333259
,5370095,YA30XU023-00365535
,5375307,YA30XU023-00370620
,9577947,YA30XU023-00333245
,9575033,YA30XU023-00333355
,33337065,YA30XU023-00435452
0005A3339563EDC337B07535C0CD53C5C5C3B65B,6662073,YA30XU004-00333530
003555A345550E55B5656AB55A2345625D2A2A4D,5595959,YA30XU003-00693575
,30774530,YA30XU036-00260539
,30943296,YA30XU003-00765072
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Output large volume of data to CSV file

I have a program that output the ownership and permission on each directory and file on the server to a csv file. I am getting error message when I run the program. The program is not outputting to the csv file. Error: the file access permissions do not allow the specified action cannot... (2 Replies)
Discussion started by: dellanicholson
2 Replies

2. Shell Programming and Scripting

Compare 2 files of csv file and match column data and create a new csv file of them

Hi, I am newbie in shell script. I need your help to solve my problem. Firstly, I have 2 files of csv and i want to compare of the contents then the output will be written in a new csv file. File1: SourceFile,DateTimeOriginal /home/intannf/foto/IMG_0713.JPG,2015:02:17 11:14:07... (8 Replies)
Discussion started by: refrain
8 Replies

3. Shell Programming and Scripting

Comparing two large unsorted csv files

Hi All, My requirement is to write a shell script to compare two large csv files. I've created sample files for explaining my problem i.e., a.csv and b.csv contents of files: ----------------- a.csv ------ Type,Memory (Kb),Location HD,Size (Mb),Serial # XT,640,D402,0,MG0010... (2 Replies)
Discussion started by: vasavi
2 Replies

4. Shell Programming and Scripting

Merging Very large CSV files in Unix

Hi, I have two very large CSV files, which I want to merge (equi-join) based on a key (column). One of the file (say F1) would have ~30 MM records and 700 columns. The other file (~f2) would have same # of records and lesser columns (say 50). I want to create an output file joining on a... (3 Replies)
Discussion started by: student_007
3 Replies

5. SCO

Need advice: Copying large CSV report files off SCO system

I have a SCO Unix server from 1999 running SCO 5.0.5 and some ancient accounting software called Real World A report writer program on the system is used to generate CSV files from accounting that we write with DOSCOPY commands to 3.5" floppies In the next 60 days we will be decommissioning... (11 Replies)
Discussion started by: magnetman
11 Replies

6. Shell Programming and Scripting

Manipulating csv file

We need to convert a field in a csv file which is in cents to dollars.(divide by 100) in our shell script. Can some body help me? (3 Replies)
Discussion started by: Deepthz
3 Replies

7. Shell Programming and Scripting

Updating a line in a large csv file, with sed/awk?

I have an extremely large csv file that I need to search the second field, and upon matches update the last field... I can pull the line with awk.. but apparently you cant use awk to directly update the file? So im curious if I can use sed to do this... The good news is the field I want to... (5 Replies)
Discussion started by: trey85stang
5 Replies

8. Shell Programming and Scripting

Sed or awk script to remove text / or perform calculations from large CSV files

I have a large CSV files (e.g. 2 million records) and am hoping to do one of two things. I have been trying to use awk and sed but am a newbie and can't figure out how to get it to work. Any help you could offer would be greatly appreciated - I'm stuck trying to remove the colon and wildcards in... (6 Replies)
Discussion started by: metronomadic
6 Replies

9. Shell Programming and Scripting

manipulating csv to add extra row

hi how do i manipulate .csv file to add an extra row after each row using shell script? I need a blank line added for each 1000 records in my file? I will then need to copy and paste some data in the blank row created. thanks 4 ur support neil (3 Replies)
Discussion started by: neil546
3 Replies
Login or Register to Ask a Question