Shell Programming and Scripting

Here is my input file "feed.txt" (first row is a header with column names. I have pasted dummy column values for 3 rows)

url;image;id;showcase_id;showcase_name;showcase_zip_code;vehicle_id;carmodel_id;make_id;external_mpn ;carmodel_name;make_name;colour;colour_alias;colour_secondary_name;colour_secondary_alias;trim;catal og_price;customer_bonus;reprise_bonus;legal_notice;rebate_legal_notice;eco_bonus;price;customer_bene fit_percent;options;code;name;matriculation_on;status;mileage;vehicle_acceleration_0_100kph;vehicle_ air_conditioning;vehicle_body_type;vehicle_co2_emission_level;vehicle_combined_fuel_economy;vehicle_ cylinders_count;vehicle_doors_count;vehicle_driven_wheels;vehicle_emission_standard;vehicle_engine_c apacity;vehicle_extra_urban_fuel_economy;vehicle_fiscal_horse_power;vehicle_height;vehicle_kerbweigh t;vehicle_length;vehicle_max_load_capacity;vehicle_name;vehicle_primary_fuel_tank_capacity;vehicle_p rimary_fuel_type;vehicle_published_hp_metric;vehicle_seats_count;vehicle_secondary_fuel_type;vehicle _type;vehicle_luggage_capacity;vehicle_tvs_tax;vehicle_transmission_shiftable;vehicle_trim_name;vehi cle_trim_code;vehicle_urban_fuel_economy;vehicle_vehicle_warranty_kms;vehicle_vehicle_warranty_month s;vehicle_width_excluding_mirrors;vehicle_transmission_type;vehicle_engine_name;vehicle_engine_code; vehicle_rsi_id;image_1;image_2;image_3;image_4;image_5
url_1;image_1;id_1;showcase_id_1;showcase_name_1;showcase_zip_code_1;vehicle_id_1;carmodel_id_1;make _id_1;external_mpn_1;carmodel_name_1;make_name_1;colour_1;colour_alias_1;colour_secondary_name_1;col our_secondary_alias_1;trim_1;catalog_price_1;customer_bonus_1;reprise_bonus_1;legal_notice_1;rebate_ legal_notice_1;eco_bonus_1;price_1;customer_benefit_percent_1;options_1;code_1;name_1;matriculation_ on_1;status_1;updated_at_1;created_at_1;mileage_1;vehicle_acceleration_0_100kph_1;vehicle_air_condit ioning_1;vehicle_body_type_1;vehicle_co2_emission_level_1;vehicle_combined_fuel_economy_1;vehicle_cy linders_count_1;vehicle_doors_count_1;vehicle_driven_wheels_1;vehicle_emission_standard_1;vehicle_en gine_capacity_1;vehicle_extra_urban_fuel_economy_1;vehicle_fiscal_horse_power_1;vehicle_height_1;veh icle_kerbweight_1;vehicle_length_1;vehicle_max_load_capacity_1;vehicle_name_1;vehicle_primary_fuel_t ank_capacity_1;vehicle_primary_fuel_type_1;vehicle_published_hp_metric_1;vehicle_seats_count_1;vehic le_secondary_fuel_type_1;vehicle_type_1;vehicle_luggage_capacity_1;vehicle_tvs_tax_1;vehicle_transmi ssion_shiftable_1;vehicle_trim_name_1;vehicle_trim_code_1;vehicle_urban_fuel_economy_1;vehicle_vehic le_warranty_kms_1;vehicle_vehicle_warranty_months_1;vehicle_width_excluding_mirrors_1;vehicle_transm ission_type_1;vehicle_engine_name_1;vehicle_engine_code_1;vehicle_rsi_id_1;image_1_1;image_2_1;image _3_1;image_4_1;image_5_1
url_2;image_2;id_2;showcase_id_2;showcase_name_2;showcase_zip_code_2;vehicle_id_2;carmodel_id_2;make _id_2;external_mpn_2;carmodel_name_2;make_name_2;colour_2;colour_alias_2;colour_secondary_name_2;col our_secondary_alias_2;trim_2;catalog_price_2;customer_bonus_2;reprise_bonus_2;legal_notice_2;rebate_ legal_notice_2;eco_bonus_2;price_2;customer_benefit_percent_2;options_2;code_2;name_2;matriculation_ on_2;status_2;updated_at_2;created_at_2;mileage_2;vehicle_acceleration_0_100kph_2;vehicle_air_condit ioning_2;vehicle_body_type_2;vehicle_co2_emission_level_2;vehicle_combined_fuel_economy_2;vehicle_cy linders_count_2;vehicle_doors_count_2;vehicle_driven_wheels_2;vehicle_emission_standard_2;vehicle_en gine_capacity_2;vehicle_extra_urban_fuel_economy_2;vehicle_fiscal_horse_power_2;vehicle_height_2;veh icle_kerbweight_2;vehicle_length_2;vehicle_max_load_capacity_2;vehicle_name_2;vehicle_primary_fuel_t ank_capacity_2;vehicle_primary_fuel_type_2;vehicle_published_hp_metric_2;vehicle_seats_count_2;vehic le_secondary_fuel_type_2;vehicle_type_2;vehicle_luggage_capacity_2;vehicle_tvs_tax_2;vehicle_transmi ssion_shiftable_2;vehicle_trim_name_2;vehicle_trim_code_2;vehicle_urban_fuel_economy_2;vehicle_vehic le_warranty_kms_2;vehicle_vehicle_warranty_months_2;vehicle_width_excluding_mirrors_2;vehicle_transm ission_type_2;vehicle_engine_name_2;vehicle_engine_code_2;vehicle_rsi_id_2;image_1_2;image_2_2;image _3_2;image_4_2;image_5_2
url_3;image_3;id_3;showcase_id_3;showcase_name_3;showcase_zip_code_3;vehicle_id_3;carmodel_id_3;make _id_3;external_mpn_3;carmodel_name_3;make_name_3;colour_3;colour_alias_3;colour_secondary_name_3;col our_secondary_alias_3;trim_3;catalog_price_3;customer_bonus_3;reprise_bonus_3;legal_notice_3;rebate_ legal_notice_3;eco_bonus_3;price_3;customer_benefit_percent_3;options_3;code_3;name_3;matriculation_ on_3;status_3;updated_at_3;created_at_3;mileage_3;vehicle_acceleration_0_100kph_3;vehicle_air_condit ioning_3;vehicle_body_type_3;vehicle_co2_emission_level_3;vehicle_combined_fuel_economy_3;vehicle_cy linders_count_3;vehicle_doors_count_3;vehicle_driven_wheels_3;vehicle_emission_standard_3;vehicle_en gine_capacity_3;vehicle_extra_urban_fuel_economy_3;vehicle_fiscal_horse_power_3;vehicle_height_3;veh icle_kerbweight_3;vehicle_length_3;vehicle_max_load_capacity_3;vehicle_name_3;vehicle_primary_fuel_t ank_capacity_3;vehicle_primary_fuel_type_3;vehicle_published_hp_metric_3;vehicle_seats_count_3;vehic le_secondary_fuel_type_3;vehicle_type_3;vehicle_luggage_capacity_3;vehicle_tvs_tax_3;vehicle_transmi ssion_shiftable_3;vehicle_trim_name_3;vehicle_trim_code_3;vehicle_urban_fuel_economy_3;vehicle_vehic le_warranty_kms_3;vehicle_vehicle_warranty_months_3;vehicle_width_excluding_mirrors_3;vehicle_transm ission_type_3;vehicle_engine_name_3;vehicle_engine_code_3;vehicle_rsi_id_3;image_1_3;image_2_3;image _3_3;image_4_3;image_5_3


Here is my declaration file containing the predefined header (columns)

url;image;id;showcase_id;showcase_name;showcase_zip_code;vehicle_id;carmodel_id;make_id;external_mpn ;carmodel_name;make_name;colour;colour_alias;colour_secondary_name;colour_secondary_alias;trim;catal og_price;customer_bonus;reprise_bonus;legal_notice;rebate_legal_notice;eco_bonus;price;customer_bene fit_percent;options;code;name;matriculation_on;status;mileage;vehicle_acceleration_0_100kph;vehicle_ air_conditioning;vehicle_body_type;vehicle_co2_emission_level;vehicle_combined_fuel_economy;vehicle_ cylinders_count;vehicle_doors_count;vehicle_driven_wheels;vehicle_emission_standard;vehicle_engine_c apacity;vehicle_extra_urban_fuel_economy;vehicle_fiscal_horse_power;vehicle_height;vehicle_kerbweigh t;vehicle_length;vehicle_max_load_capacity;vehicle_name;vehicle_primary_fuel_tank_capacity;vehicle_p rimary_fuel_type;vehicle_published_hp_metric;vehicle_seats_count;vehicle_secondary_fuel_type;vehicle _type;vehicle_luggage_capacity;vehicle_tvs_tax;vehicle_transmission_shiftable;vehicle_trim_name;vehi cle_trim_code;vehicle_urban_fuel_economy;vehicle_vehicle_warranty_kms;vehicle_vehicle_warranty_month s;vehicle_width_excluding_mirrors;vehicle_transmission_type;vehicle_engine_name;vehicle_engine_code; vehicle_rsi_id;image_1;image_2;image_3;image_4;image_5

between two files the difference was, "two new columns "updated_at" and "created_at" (updated_at;created_at) were added in between by the data provider, without giving any notice", due to which the data loading got messed up one day. Similar problem is happening frequently.


So i have planned to alert when the new columns get added and at the same time load the data without any issues by removing the newly added columns and it's relevant values in each row. So that we can add the new columns in the database and also redefine in the declaration file for considering the newly added columns in a planned manner.

Thanks & regards
Praveen

Code:

awk '
BEGIN { FS=OFS=";" }
NR==FNR {
  # 1st file: build hd[]
  for (i=1; i<=NF; i++) { hd[$i] }
  next
}
FNR==1 {
  # header of 2nd file: if in hd[] note that column in col[]
  for (i=1; i<=NF; i++) {
    if ($i in hd) { col[i] } else { print "Column " i ": " $i > "mismatch.txt" }
  }
}
{
  # print the columns that are in col[]
  sep=""
  for (i=1; i<=NF; i++) {
    if (i in col) { printf "%s%s", sep, $i; sep=OFS }
  }
  print ""
}
' declare.txt feed.txt

Hi MadeInGermany,

Thanks for your help. I tried your script as well.

It is doing the job like identifying the new column names and giving it in an output file "mismatch.txt" but it is not removing those new columns and its values from the feed.txt, which is still an issue.

Regards
Praveen

Hi,

To utilise the method that I've used you'll have to increase the number of column variables and loops to match your first input file. Which is more than the four or so that I was expecting, I have tested this and it will remove the data as required, so you can just increase the initial declarations and for loop count. You'll also have to change the field delimiter to a semicolon.

Not very elegant, but it will work.

Regards

Dave

Hi Dave,

Thanks for your help. Can you please let me know what is a & b ?

My input file is feed.txt and the columns I defined for comparison was in the file "declaration.txt" .

If I need to declare all the columns in the code itself then can I ignore "declaration.txt"?

Please advice.

Regards
Praveen

Hi Praveen,

The files "a" and "b" should be substituted with your file names, the file "a" is your target file - this file determines the number of declared variables (1 for each column) it also determines the number of "for" loops (1 for each column). The file "b" is the file that has the extra fields/columns.

Please remember to change the delimiter and let me know how you get on.

Regards

Dave

Hi Dave,

Thanks once again. I will try and let you know.

Regards
Praveen