Merging fields in CSV

Shell Programming and Scripting
1

Hi experts,
I have a csv file which has one field (ID) repeated multiple times with corresponding other field values.
I need to convert this file in a format where for a ID all other values has to be present in single field.

For Eg : Here in below file ID 1 is repeated 3 times with different city values(blr,chn.dlh). All these are in separate lines.
All this values has to be brought into one single field with | as a separator. This should also retain the headers.

cat file1.csv

ID	city	acct_no
1	blr	500
1	chn	600
1	dlh	700
2	hyd	888
2	blr	999
3	dlh	111
3	blr	222
3	hyd	333


Required output

ID	city	acct_no
1	blr|chn|dlh	500|600|700
2	hyd|blr	888|999
3	blr|hyd	111|222|333
2

I would have thought you would want:

3	dlh|blr|hyd	111|222|333

instead of:

3	blr|hyd	111|222|333

as the last line of output for your sample input file. Assuming this is what you want, try:

awk '
BEGIN { OFS="\t" }
$1 != last_id {
        if(NR > 1) print last_id, cities, accounts
        last_id = $1
        cities = $2
        accounts = $3
        next
}
{       cities = cities "|" $2
        accounts = accounts "|" $3
}
END {   print last_id, cities, accounts }' file1.csv

If you want to run this on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk , /usr/xpg6/bin/awk , or nawk .

1 Like
3

Try this:

awk '
 NR==1{
  h=$0
 } 
 NR>1{
  c[$1]=c[$1]?c[$1]"|"$2:$2
  a[$1]=a[$1]?a[$1]"|"$3:$3
 } 
 END{
  print h
  for (i in c) print i,c,a
 }' file
4

Thanks Don, It works good for 3 columns. But what if we have multiple columns say around 100.

---------- Post updated at 04:12 AM ---------- Previous update was at 04:09 AM ----------

Thanks Subbeh ,
It works good for 3 columns. But what if we have multiple columns say around 100. More over the fields are coma separated

5

This should get you into the right direction:

awk '
 NR==1{
  print
  next
 }
 {
  for (i=2; i<=NF; i++) {
   a[$1,i]=a[$1,i]?a[$1,i]"|"$i:$i
  }
  l[$1]
 }
 END {
  for (i in l) {
   printf("%s ", i)
   for (j=2; j<=NF; j++) {
    printf("%s ",a[i,j])
   }
   printf "\n"
  }
 }' file
6

Hi bharathbangalor,
The sample input file you showed us and the output you said you wanted have tabs as the field separators. But you told Subbeh "More over the fields are coma (sic) separated".

Are you saying your input file has commas instead of tabs as field separators?

Are you saying you want the output to use commas instead of tabs as field separators?

The sample input file you showed us is sorted by key, city, and account. The awk script I provided assumes that all entries in your input file with the same key are on contiguous lines and prints output that is in the same order as the input. The awk script Subbeh provided will work no matter what order the input is in, but (other than the header) prints output lines in random order.

To be sure we're coming up with code that will work for you:

  1. Do all of the input lines for a given id in your real data appear on adjacent lines?
  2. Do you care about the order of the output lines?
  3. What operating system and version are you using? (I.e., what is the output from uname -a ?
  4. What is the output from the command getconf LINE_MAX ?
  5. Will the length in bytes of any input line (including field separators and the trailing newline character) in your real data exceed the number printed by getconf?
  6. Will the length in bytes of the longest output line you want to produce from your real data exceed the number printed by getconf? If it will, will the number of bytes in the longest output field you want to produce from your real data exceed the number printed by getconf?
  7. And, does every line in your input file have the same number of fields?
7

Hi Don,

PFA my actual data and required output sample.
PFB the answers for your question

  1. Yes
  2. No
    3.2.6.32-279.5.1.el6.x86_64 #1 SMP Tue Jul 24 13:57:35 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux
    4.2048
    5.NO
    6.NO.
8

OK. So your comma separated input file actually is a comma separated input file instead of the tab separated input you showed us.

You never said anything about wanting the output to be an extended format Microsoft Office Open XML format Excel spreadsheet file.

I'm sorry, but although it would be fairly easy to produce a .csv file with your new requirements and you could then load the .csv file into Excel, the fact that you change your requirements with every post in this thread and seem to be unable to clearly describe what you are trying to do with matching sample input and output files makes it seem like those of us who have been trying to help you are just wasting our time.

1 Like