Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

CSV joining and checking multiple files

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting CSV joining and checking multiple files
# 1  
Old 08-22-2016
Hammer & Screwdriver CSV joining and checking multiple files
Hello,

For our work we use several scripts to gather/combine data for use in our webshop. Untill now we did not had any problems but since a couple days we noticed some mismatches between imports.

It happened that several barcodes where matched even though it was a complete other product. Of course the scripts arent checking on this yet so we need to upgrade the scripts to check for this and give us a list to check or update the listing.

The supplier sends us a CSV file with data as shown below:

supplier_clean.csv
Code:
ean;pps_reference;stock;price;sku;mpn;manufacturer
4260010852693;1043154;84;743.42;P00000172;70100118555;Fujifilm
4960999575285;273189;9400;141.80;P00009067;2768B016;Canon
0013803092899,4960999575292;27433196;44;44.94;P00022338;2768B017;Canon
8715946388540;2944686;1030;47.76;P00000878;C13S042167;Epson
0088698115763,3141725001174;3654125;20;54.80;P00004251;C1825A;Hewlett Packard

This file is being joined to another file with the following code, more on this here:

joining.sh
Code:
#!/bin/sh

awk  '
BEGIN           {FS = OFS = ";"
                 print "ean;sku;pps_reference;mpn;stock;price;manufacturer;supplier_code"
                }
                {gsub (/ /, "", $1)
                }
NR == FNR       {for (n = split($1, T, ","); n > 0; n--) S[T[n]]=$2
                 next
                }
                {for (n = split($1, T, ","); n > 0; n--) if (T[n] in S) {$2 = S[T[n]] OFS $2
                 print
                 next
                 }
                }
' $1 $2 > $3

This script get called as follows.
Code:
join_prijslijst.sh website_clean.csv supplier_clean.csv results.csv

The website_clean has the following data (short example)

website_clean.csv
Code:
Barcode;Sku;Manufacturer
0696720480781,4000567150589;P00002801;Braun Photo Technik
4000461043031;P00002800;Dörr
4000461034213,4000461037818;P00002799;Dörr
0891257001526,8912570015266;P00002634;Gary Fong
0891257001106;P00002633;Gary Fong
0887111646026;P00002632;HP
0887111515629;P00002631;HP

The problem is that the checking if the manufacturer has to happen during the joining together and to make matters worse some suppliers have different names for some suppliers (For example HP, Hewlet packard etc etc).

My idea is to have another file where first the website_clean checks for all the possible names of that manufacturer (see below for example) and this then compares against the supplier_clean csv. If the correct name is in there it continues as normal and if not it writes this line to a seperate file which we then can manual check for the names. In this seperate file i need both lines though so we can check which would be the correct name/product.

manufacturer_check.csv
Code:
manufacturer
HP,Hewlett Packard, HP INC.
Canon
Fujifilm
Epson
WD,Western Digital

I hope this is clear in what needs to happen to make it work. If not let me know and i will try to explain it better.
# 2  
Old 08-22-2016
If I remember correctly, EAN are unique except for a certain range set aside for any shop's internal coding, so what do you mean by "several barcodes where matched"? Please give us examples of data sets where the identification went wrong. And, your path forward is not too clear to me. Please explain in more detail. Why should the supplier's name help if barcodes are falsely read?
# 3  
Old 08-22-2016
They should be indeed but too bad that different manufacturers can use the same code somehow. For example EAN 7636490074196 which is a Seagate 2TB SSD but also a Lacie external HDD.

The problem is not in the joining itself but due too the problem that it is used multiple times.

I am not sure if the explaination will be sufficient but i will try.

The script we use for joining on barcodes needs to be adapted/changed so that it will check the manufacturer of both files against a third file where all the different names are written. This third file is pure to have manufacturers like HP, WD etc caught without them getting ignored each time.

To describe it in steps:
  1. Each line from the supplier file gets matched against the website file. With this is the complete line
  2. Now the manufacturerfrom the website file gets checked against the manufacturer file so it can check how the manufacturer can be written by different suppliers.
  3. The line that matches from those 2 has to be checked against the manufacturer from the supplier.
  4. If this are the same it continues with the normal loop. If there is a difference it needs to be written to a new file which will be picked up for manual checking.
  5. This manual file needs to have the full line from website and supplier in them so we can check if its a spelling error or a ean error.

I hope this clarifies it a bit.
Last edited by rbatte1; 09-14-2016 at 05:53 AM.. Reason: Converted text numbered list to formatted number-list
# 4  
Old 08-22-2016
No, it doesn't. Please try again, using input data for demonstration.
# 5  
Old 08-23-2016
Sorry for that but it is a very confusing bit indeed.

website_clean.csv
Code:
Barcode;Sku;Manufacturer
4260010852693,4000567150589;P00002801;Fujifilm
4960999575285;P00002800;Canon
4000461034213,4000461037818;P00002799;Dörr
0891257001526,8912570015266;P00002634;Gary Fong
0891257001106;P00002633;Gary Fong
0887111646026;P00002632;HP
0088698115763;P00002631;HP

supplier_clean.csv
Code:
ean;pps_reference;stock;price;mpn;manufacturer
4260010852693;1043154;84;743.42;70100118555;Fujifilm
4960999575285;273189;9400;141.80;2768B016;Canon
4000461034213,4960999575292;27433196;44;44.94;2768B017;Canon
8715946388540;2944686;1030;47.76;C13S042167;Epson
0088698115763,3141725001174;3654125;20;54.80;C1825A;Hewlett Packard

manufacturer_compare.csv
Code:
manufacturer
HP,Hewlett Packard, HP INC.
Canon
Fujifilm
Epson
WD,Western Digital

Above are 3 files in which the first 2 are the important ones for the joining part.

When i run the script with ./join_prijslijst.sh website_clean.csv supplier_clean.csv output.csv, i get the following output
Code:
ean;sku;pps_reference;mpn;stock;price;manufacturer;supplier_code
4260010852693;P00002801;1043154;84;743.42;70100118555;Fujifilm
4960999575285;P00002800;273189;9400;141.80;2768B016;Canon
4000461034213,4960999575292;P00002799;27433196;44;44.94;2768B017;Canon
0088698115763,3141725001174;P00002631;3654125;20;54.80;C1825A;Hewlett Packard

You would say that this is correct and also fine but the problem is the following line:
Code:
4000461034213,4000461037818;P00002799;Dörr

In the website file the manufacturer is Dorr but in the supplier file it is Canon thus making the wrong join and most likely also add a wrong price etc to this product.

What we want is to have this split up in 2 files in which 1 is as follows:
New_output.csv
Code:
ean;sku;pps_reference;mpn;stock;price;manufacturer;supplier_code
4260010852693;P00002801;1043154;84;743.42;70100118555;Fujifilm
4960999575285;P00002800;273189;9400;141.80;2768B016;Canon
0088698115763,3141725001174;P00002631;3654125;20;54.80;C1825A;Hewlett Packard

and another file with the following contents:
wrong_match.csv
Code:
4000461034213,4000461037818;P00002799;Dörr;4000461034213,4960999575292;27433196;44;44.94;2768B017;Canon

Then we can check that it is the same manufacturer or not. If for example the manufacturer would be HP inc instead of HP in our system we can just add that to the manufacturer_compare.csv file so it will get recognized the next time.

I hope this clears it up a bit.
# 6  
Old 08-23-2016
Let me paraphrase this: when creating the output file, compare the supplier field from website_clean.csv with the supplier from supplier_clean.csv. If identical, fine, print the record. If can be reconciled via manufacturer_compare.csv, fine, print it, BUT: which supplier?
If it can't be reconciled, print to wrong_match.csv for later evaluation.
# 7  
Old 08-23-2016
Instead of the supplier field use the Manufacturer field and you are right on the spot.

The supplier field is something we add after the joining etc has been done. It can be ignored.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Export Oracle multiple tables to multiple csv files using UNIX shell scripting

Hello All, just wanted to export multiple tables from oracle sql using unix shell script to csv file and the below code is exporting only the first table. Can you please suggest why? or any better idea? export FILE="/abc/autom/file/geo_JOB.csv" Export= `sqlplus -s dev01/password@dEV3... (16 Replies)
Discussion started by: Hope
16 Replies

2. UNIX for Dummies Questions & Answers

Joining different columns from multiple files

Hello again, I am trying to join 3rd column of 3 files into the end on one file and save it separately... my data looks like this file 1 Bob, Green, 80 Mark, Brown, 70 Tina, Smith, 60 file 2 Bob, Green, 70 Mark, Brown, 60 Tina, Smith, 50 file 3 Bob, Green, 50 Mark, Brown,60 Tina,... (6 Replies)
Discussion started by: A-V
6 Replies

3. Shell Programming and Scripting

Other alternative for joining together columns from multiple files

Hi again, I have monthly one-column files of roughly around 10 years. Is there a more efficient way to concatenate these files column-wise other than using paste command? For instance: file1.txt 12 13 15 12 file2.txt 14 15 18 19 file3.txt 20 21 (8 Replies)
Discussion started by: ida1215
8 Replies

4. Shell Programming and Scripting

checking csv files with empty fields..!

Hi! I need to learn that how a shell script can transverse a csv file n check if any field is empty or not. means its contains two comma or space b/w commas i.e., "" or " ". can anyone help me out how I can do that.... (10 Replies)
Discussion started by: sukhdip
10 Replies

5. Shell Programming and Scripting

Joining multiple files based on one column with different and similar values (shell or perl)

Hi, I have nine files looking similar to file1 & file2 below. File1: 1 ABCA1 1 ABCC8 1 ABR:N 1 ACACB 1 ACAP2 1 ACOT1 1 ACSBG 1 ACTR1 1 ACTRT 1 ADAMT 1 AEN:N 1 AKAP1File2: 1 A4GAL 1 ACTBL 1 ACTL7 (4 Replies)
Discussion started by: seqbiologist
4 Replies

6. Shell Programming and Scripting

Checking the existance of multiple files

I am trying to execute the following command to check the existance of a file (which has a date timestamp on it). If there are more than one file, then also it should give me 'success' result. if then <do some work> else <no files> fi Since there are more than one... (18 Replies)
Discussion started by: vivek_damodaran
18 Replies

7. UNIX for Dummies Questions & Answers

Joining string on multiple files

Hi guys, I am a forum (and a bit of a unix) newbie, and I currently have a tricky problem lying ahead of me. I have multiple files, and I am looking to join the files on the first column. Example: File 1 andy b 100 amy c 200 amy d 300 File 2 andy c 200 amy c 100 clyde o 50 ... (3 Replies)
Discussion started by: jdr0317
3 Replies

8. Shell Programming and Scripting

joining multiple files into one while putting the filename in the file

Hello, I know how to join multiple files using the cat function. I want to do something a little more advanced. Basically I want to put the filename in the first column... One thing to note is that the file is tab delimited. e.g. file1.txt joe 1 4 5 6 7 3 manny 2 3 4 5 6 7 ... (4 Replies)
Discussion started by: phil_heath
4 Replies

9. UNIX for Dummies Questions & Answers

Joining files based on multiple keys

I need a script (perl or awk..anything is fine) to join 3 files based on three key columns. The no of non-key columns can vary in each file. The columns are delimited by semicolon. For example, File1 Dim1;Dim2;Dim3;Fact1;Fact2;Fact3;Fact4;Fact5 ---- data delimited by semicolon --- ... (1 Reply)
Discussion started by: Sebben
1 Replies

10. UNIX for Advanced & Expert Users

Joining 2 CSV files together

I need a little help as I am a complete novice at scripting in unix. However, i am posed with an issue... i have two csv files in the following format@ FILE1.CSV: HEADER HEADER Header , , HEADER 001X ,,200 002X ,,300 003X ,,300 004X ,,300 FILE2.CSV: HEADER HEADER Header , ,... (3 Replies)
Discussion started by: chachabronson
3 Replies
Login or Register to Ask a Question