-1

I have a csv file which has 85 fields. I want to replace column number 52nd values with the data from another file. This second file contains only 1 column and has same number of records as it is in 1st file.

e.g data.CSV file (1st csv file)

0,126,,2,0,904CEE,0,0,1,0,0,,7638.raw,0,0,20210515,111937,10,0,540,540,0,,,,,,,,,,,0,,,,,,,,,,,,,0,,,07822000655,,,**07822000656**,0,,,,0B020D,358605075357339 ,234307822000655,11,,01,00,0,,,0,2,1,0,1101,,1,0,23430,,,11,5,,0A03,,,0,
0,126,,2,0,904CEE,0,0,1,0,0,,7638.raw,0,0,20210515,111938,10,0,540,540,0,,,,,,,,,,,0,,,,,,,,,,,,,0,,,07822000655,,,**07822000656**,0,,,,0B020D,358605075357339 ,234307822000655,11,,01,00,0,,,0,2,1,0,1101,,1,0,23430,,,11,5,,0A03,,,0,
0,126,,2,0,904CEE,0,0,1,0,0,,7638.raw,0,0,20210515,111939,10,0,540,540,0,,,,,,,,,,,0,,,,,,,,,,,,,0,,,07822000655,,,**07822000656**,0,,,,0B020D,358605075357339 ,234307822000655,11,,01,00,0,,,0,2,1,0,1101,,1,0,23430,,,11,5,,0A03,,,0,
0,126,,2,0,904CEE,0,0,1,0,0,,7638.raw,0,0,20210515,111939,10,0,540,540,0,,,,,,,,,,,0,,,,,,,,,,,,,0,,,07822000655,,,**07822000656**,0,,,,0B020D,358605075357339 ,234307822000655,11,,01,00,0,,,0,2,1,0,1101,,1,0,23430,,,11,5,,0A03,,,0,
0,126,,2,0,904CEE,0,0,1,0,0,,7638.raw,0,0,20210515,111939,10,0,540,540,0,,,,,,,,,,,0,,,,,,,,,,,,,0,,,07822000655,,,**07822000656**,0,,,,0B020D,358605075357339 ,234307822000655,11,,01,00,0,,,0,2,1,0,1101,,1,0,23430,,,11,5,,0A03,,,0,
0,126,,2,0,904CEE,0,0,1,0,0,,7638.raw,0,0,20210515,111939,10,0,540,540,0,,,,,,,,,,,0,,,,,,,,,,,,,0,,,07822000655,,,**07822000656**,0,,,,0B020D,358605075357339 ,234307822000655,11,,01,00,0,,,0,2,1,0,1101,,1,0,23430,,,11,5,,0A03,,,0,

2nd file (contains only 1 column)

6228205
6225214
6225211
6225206
5206
87777

I want to replace

  • 52nd column value (07822000656) from 1st file (data.csv) with 6228205 for 1st row in data.csv file
  • 52nd column value (07822000656) with 6225214 for 2nd row in data.csv file
  • 52nd column value (07822000656) with 6225211 for 3rd row

...and so on...

so output should be

0,126,,2,0,904CEE,0,0,1,0,0,,7638.raw,0,0,20210515,111937,10,0,540,540,0,,,,,,,,,,,0,,,,,,,,,,,,,0,,,07822000655,,,**6228205**,0,,,,0B020D,358605075357339 ,234307822000655,11,,01,00,0,,,0,2,1,0,1101,,1,0,23430,,,11,5,,0A03,,,0,
0,126,,2,0,904CEE,0,0,1,0,0,,7638.raw,0,0,20210515,111938,10,0,540,540,0,,,,,,,,,,,0,,,,,,,,,,,,,0,,,07822000655,,,**6225214**,0,,,,0B020D,358605075357339 ,234307822000655,11,,01,00,0,,,0,2,1,0,1101,,1,0,23430,,,11,5,,0A03,,,0,
0,126,,2,0,904CEE,0,0,1,0,0,,7638.raw,0,0,20210515,111939,10,0,540,540,0,,,,,,,,,,,0,,,,,,,,,,,,,0,,,07822000655,,,**6225211**,0,,,,0B020D,358605075357339 ,234307822000655,11,,01,00,0,,,0,2,1,0,1101,,1,0,23430,,,11,5,,0A03,,,0,
0,126,,2,0,904CEE,0,0,1,0,0,,7638.raw,0,0,20210515,111939,10,0,540,540,0,,,,,,,,,,,0,,,,,,,,,,,,,0,,,07822000655,,,**6225206**,0,,,,0B020D,358605075357339 ,234307822000655,11,,01,00,0,,,0,2,1,0,1101,,1,0,23430,,,11,5,,0A03,,,0,
0,126,,2,0,904CEE,0,0,1,0,0,,7638.raw,0,0,20210515,111939,10,0,540,540,0,,,,,,,,,,,0,,,,,,,,,,,,,0,,,07822000655,,,**5206**,0,,,,0B020D,358605075357339 ,234307822000655,11,,01,00,0,,,0,2,1,0,1101,,1,0,23430,,,11,5,,0A03,,,0,
0,126,,2,0,904CEE,0,0,1,0,0,,7638.raw,0,0,20210515,111939,10,0,540,540,0,,,,,,,,,,,0,,,,,,,,,,,,,0,,,07822000655,,,**87777**,0,,,,0B020D,358605075357339 ,234307822000655,11,,01,00,0,,,0,2,1,0,1101,,1,0,23430,,,11,5,,0A03,,,0,

I managed to do it like below:

awk -F , '{$1, $2, $3, $4...$51}' data.csv >temp1.csv
awk -F , '{$53, $54, $55....$85}' data.csv >temp2.csv
paste -d "," temp1.csv 2nd_file temp2.csv

however, I am looking for a better way to handle this

Improve this question
4
  • 1
    I guess I understand what you want, but what is your question? You forgot to show your code and explain what went wrong with it. Commented Oct 20, 2021 at 14:40
  • Also, as a general rule, please only post manageable extracts of data - you could easily omit most of the columns in your example data.csv since they are completely irrelevant to the problem, but make reading the post and assessing the relevant change more difficult. Commented Oct 20, 2021 at 14:43
  • Does this answer your question? Replace column in one file with column from another using awk? Commented Oct 20, 2021 at 14:54
  • @AdminBee Yes...something like this but I am not getting expected output in my case 0,126,,2,0,904CEE,0,0,1,0,0,,RA2_TT00.TTFILE00.7638.raw,0,0,20210515,111937,10,0,540,540,0,,,,,,,,,,,0,,,,,,,,,,,,,0,,,07822000655,,,07822000656,0,,,,0B020D,358605075357339 ,234307822000655,11,,01,00,0,,,0,2,1,0,1101,,1,0,23430,,,11,5,,0A03,,,0,|85550 0,126,,2,0,904CEE,0,0,1,0,0,,RA2_TT00.TTFILE00.7638.raw,0,0,20210515,111938,10,0,540,540,0,,,,,,,,,,,0,,,,,,,,,,,,,0,,,07822000655,,,07822000656,0,,,,0B020D,358605075357339 ,234307822000655,11,,01,00,0,,,0,2,1,0,1101,,1,0,23430,,,11,5,,0A03,,,0,|8545501 Commented Oct 20, 2021 at 15:02

3 Answers 3

Reset to default
2

You could use awk to construct of map of entries on the second file, keyed by line number and replace the value on the first file,

awk -v FS=, -v OFS=, 'FNR==NR{hash[FNR]=$0; next}{$52 = hash[FNR]}1' file2 file1
Improve this answer
2
  • Thank you @Inian...this worked for me. :) Commented Oct 20, 2021 at 15:41
  • As this is a general Q&A site, used as archive for others searching for the solution, I think I should add that this works in this special case, but may fail for a lots of legal csv files. Commented Oct 22, 2021 at 6:46
1

You tagged this question /awk, but using tools like awk will fail on csv files as soon as they carry a field like "embed , in a string", so better use a tool designed for it, wich even makes it very simple:

csvtool pastecol 52 1 data.CSV value.CSV

This replaces column 52 of data.CSV with column 1 of value.CSV

Improve this answer
2
  • Which csvtool is this? I googled around and there are number of projects with that name that aren't the csvtool (with pastecol) that you show. I did find this, colin.maudry.fr/csvtool-manual-page, but there's no indication (out side of being a default package in Xubuntu) how to get it. Commented Oct 21, 2021 at 19:02
  • 1
    Yes, it's the one available as package for Debian, CentOS, Fedora, Ubuntu and others. The sources (it's written in OCaml) are available at github.com/Chris00/ocaml-csv/tree/master/csvtool Commented Oct 22, 2021 at 6:43
0

Here's how to do it using GoCSV, a tool designed for processing CSV.

# Break up starting-file about column 52
gocsv select --columns 1-51 start.csv > left.csv
gocsv select --columns 53-  start.csv > right.csv

# Combine both sides with replacement column/file in the "middle"
gocsv zip left.csv replacement.csv right.csv > my_final.csv

# Prove it worked
cmp my_final.csv op_final.csv

I did have to dress up op_final.csv before doing the compare, for anyone who wants to try:

  • remove OP's ** around the target values
  • add a newline, because GoCSV adds trailing newlines
Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.