Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

CSV Split field to check multiple codes

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting CSV Split field to check multiple codes
# 1  
Old 01-04-2016
CSV Split field to check multiple codes
Hello,

For work i am trying to generate a combined csv file excisting out of 2 other csv files.

The problem i am facing is that the first field on both files have multiple values in there which arent always the same. This first field is also the joining part.

The layout of the files is as follows:
File1: EAN, Articlenr(our own)
File2: EAN, Articlenr(Supplier), SKU, Stock, Price

The first field can contain more then 1 EAN code in the following format:
Code:
Barcode;Sku
4960999865300;testnderp
1230000000009, 1240000000009;ND009
1230000000010, 1240000000010;ND010

Same goes for file 2.

Does anyone know a way how i can split the EAN codes so i can search them in the other file to combine both lines into 1 in another csv file?
# 2  
Old 01-04-2016
HUGE ASSUMPTION WARNING
I am assuming that the EAN code is unique across both files for any one product...

ALSO UNTESTED CODE WARNING, but the germ of an idea
Code:
perl -ne '
BEGIN{
  open (my $our_file,"<","/path/to/file1/with/just/ean/and/sku");
  while (<$our_file>){
    my@r=split/;/,$_;
    for my $ean (split/,/,$r[0]){
      $rec1{$ean}=$_;
    }
  }
}
@r=split/;/,$_;
@ean=split/,/,$r[0];
for $ean (@ean){
  if ($rec1{$ean}){
    @p=split/;/,$rec1{$ean};
    @our_eans=split/,/,$p[0];
    my %eans;
    for (@ean,@our_ean){
      $eans{$_}++;
    }
    print join(";",join(",",keys %eans),$p[1],@r[1,2,3]),"\n";
  }
}' path/to/file2 >merged_file.csv

# 3  
Old 01-04-2016
You are correct to assume that the 1 of the EAN codes in both files are the same in which case the 2 lines need to be joined together.

I will test your code asap and let you know if it works. Thank for for helping in advance.
# 4  
Old 01-04-2016
How about supplying samples of all input files and the desired output?
# 5  
Old 01-05-2016
Sorry i totally forgot to add those.

Here are the examples and the output like i would want it.

File 1
Code:
Barcode;Sku
4960999865300;testnderp
1230000000001;ND001
1230000000002;ND002
1230000000003;ND003
1230000000008;ND008
1230000000009, 1240000000012;ND009
1230000000010, 1240000000011;ND010

File2
Code:
4260236270233,4260236270677,4260236271766;8654951;;10;555.28;Pyr
4960999865300;8935214;0023030102;555;342.70;REV
4006341672544;7345028;0023490;2;555.98;REV
0688334022518;11123553;99263999;555;.01;Pac
0688334022457;123502;;555;.01;Pac
1230000555010, 1240000000010;12345;99263999;555;33.01;Pac

Output file 3:
Code:
Barcode;Art_ours;Art_Sup;SKU;stock;price;manufacturer
4960999865300;testnderp;8935214;0023030102;555;342.70;REV
1230000000010, 1240000000010;ND010;12345;99263999;555;33.01;Pac

I hope this clarifies the combining i am trying to achieve.



Quote:
Originally Posted by Skrynesaver
HUGE ASSUMPTION WARNING
I am assuming that the EAN code is unique across both files for any one product...

ALSO UNTESTED CODE WARNING, but the germ of an idea
Code:
perl -ne '
BEGIN{
  open (my $our_file,"<","/path/to/file1/with/just/ean/and/sku");
  while (<$our_file>){
    my@r=split/;/,$_;
    for my $ean (split/,/,$r[0]){
      $rec1{$ean}=$_;
    }
  }
}
@r=split/;/,$_;
@ean=split/,/,$r[0];
for $ean (@ean){
  if ($rec1{$ean}){
    @p=split/;/,$rec1{$ean};
    @our_eans=split/,/,$p[0];
    my %eans;
    for (@ean,@our_ean){
      $eans{$_}++;
    }
    print join(";",join(",",keys %eans),$p[1],@r[1,2,3]),"\n";
  }
}' path/to/file2 >merged_file.csv

I just tested this code and below is the output it creates:

Code:
4960999865300;testnderp
;YVVSZN23;21355379;10
1230000000009;ND009
;YVVGZNDG;72051;2
1230000000010;ND010
;YVUVZN01;IOLO-SR-BOX;1

The following was in file 1 and file 2

Code:
Barcode;Sku
4960999865300;testnderp
1230000000001;ND001
1230000000009, 1240000000012;ND009
1230000000010, 1240000000011;ND010




4960999865300;YVVSZN23;21355379;10;41,31;Symantec
1230000000009;YVVGZNDG;72051;2;43,10;TEST
1230000000010;YVUVZN01;IOLO-SR-BOX;1;24,79;Diverse

It seems to add the lines from file 2 on a new line instead of behind the original lines but i am not sure how to fix that. Could you check that out?
Last edited by SDohmen; 01-05-2016 at 05:55 AM.. Reason: Adding some smaller details
# 6  
Old 01-05-2016
Why does the second data line appear in the output? There's no match between the files.

---------- Post updated at 11:01 ---------- Previous update was at 10:57 ----------

Try
Code:
awk -F";" '
BEGIN           {print "Barcode;Art_ours;Art_Sup;SKU;stock;price;manufacturer"
                }
NR == FNR       {T[$1] = $2
                 next
                }
$1 in T         {$2 = T[$1] OFS $2
                 print
                }
' OFS=";" file[12]

# 7  
Old 01-05-2016
Hello SDohmen,

I am confuse here by seeing your sample output, as follows are the comments on same.
Code:
Barcode;Art_ours;Art_Sup;SKU;stock;price;manufacturer
4960999865300;testnderp;8935214;0023030102;555;342.70;REV
### Above line you are comparing the first columns of File1 and File2.
1230000000010, 1240000000010;ND010;12345;99263999;555;33.01;Pac
### Above line you are NOT comparing the first column of File1 and File2?

If you want to compare always the first columns of both the Input_files then following may help you.
Code:
awk -F"[,|;]" 'FNR==NR{A[$1]=$0;next} ($1 in A){q=$1;sub($1,X);print A[q] $0}' Input_file1 Input_file2

Output will be as follows for above command.
Code:
 4960999865300;testnderp;8935214;0023030102;555;11.70;REV

If you have some other conditions with your query, I request you to please let us know the complete details on same, it will be helpful for us to help you. Hope this helps.

Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
SDohmen
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk - CSV file - field with single or multiple spaces

Hi, In a csv file, I want to select records where first column has zero or multiple spaces. Eg: abc.csv ,123,a ,22,b ,11,c a,11,d So output should be: ,123,a ,22,b ,11,c Please advise (5 Replies)
Discussion started by: vegasluxor
5 Replies

2. Shell Programming and Scripting

Match columns from two csv files and update field in one of the csv file

Hi, I have a file of csv data, which looks like this: file1: 1AA,LGV_PONCEY_LES_ATHEE,1,\N,1,00020460E1,0,\N,\N,\N,\N,2,00.22335321,0.00466628 2BB,LES_POUGES_ASF,\N,200,200,00006298G1,0,\N,\N,\N,\N,1,00.30887539,0.00050312... (10 Replies)
Discussion started by: djoseph
10 Replies

3. Shell Programming and Scripting

Split a .csv File into Multiple Files

Hi guys, I have a requirement where i need to split a .csv file into multiple files. Say for example i have data.csv file and i have splitted that into multiple files based on some conditions i.e first file should have 100, last file 50 and other files 1000 each. Am passing the values in... (2 Replies)
Discussion started by: azherkn3
2 Replies

4. Linux

How do I format a Date field of a .CSV file with multiple commas in a string field?

I have a .CSV file (file.csv) whose data are all enclosed in double quotes. Sample format of the file is as below: column1,column2,column3,column4,column5,column6, column7, Column8, Column9, Column10 "12","B000QRIGJ4","4432","string with quotes, and with a comma, and colon: in... (3 Replies)
Discussion started by: dhruuv369
3 Replies

5. Shell Programming and Scripting

How to split file into multiple files using awk based on 1 field in the file?

Good day all I need some helps, say that I have data like below, each field separated by a tab DATE NAME ADDRESS 15/7/2012 LX a.b.c 15/7/2012 LX1 a.b.c 16/7/2012 AB a.b.c 16/7/2012 AB2 a.b.c 15/7/2012 LX2 a.b.c... (2 Replies)
Discussion started by: alexyyw
2 Replies

6. Shell Programming and Scripting

Split a file into multiple files based on field value

Hi, I've one requirement. I have to split one comma delimited file into multiple files based on one of the column values. How can I achieve this Unix Here is the sample data. In this case I have split the files based on date column(c4) Input file c1,c2,c3,c4,c5... (1 Reply)
Discussion started by: manasvi24
1 Replies

7. Shell Programming and Scripting

awk to split one field and print the last two fields within the split part.

Hello; I have a file consists of 4 columns separated by tab. The problem is the third fields. Some of the them are very long but can be split by the vertical bar "|". Also some of them do not contain the string "UniProt", but I could ignore it at this moment, and sort the file afterwards. Here is... (5 Replies)
Discussion started by: yifangt
5 Replies

8. Shell Programming and Scripting

Field validations in multiple files CSV

Hi, I am regular reader of this forum. My advanced thanks to everyone. Below given are the sample files INDATA (Main data) Fild1Çfld2Çfld3….. Fild1Çfld2Çfld3….. Fild1Çfld2Çfld3….. Fild1Çfld2Çfld3….. Fild1Çfld2Çfld3….. . . N records (140000) eg GRPDATA (Reference file) (2 Replies)
Discussion started by: hyperion.krish
2 Replies

9. Shell Programming and Scripting

Matching lines across multiple csv files and merging a particular field

I have about 20 CSV's that all look like this: "","","","","","","","","","","","","","","",""What I've been told I need to produce is the exact same thing, but with each file now containing the start_code from every other file where the email matches. It doesn't matter if any of the other... (1 Reply)
Discussion started by: Demosthenes
1 Replies
Login or Register to Ask a Question