3

An external vendor has provided me a data dump of 20+ tables as *.csv files. Their documentation is lacking so I'm having to manually go through the files to find out which files are 'related' in an RDBMS sense. Is there a way for me to find out which files have same string pattern and neatly print it out?

Currently I'm doing this and manually linking them together:

$> head -n 1 *.csv

This gives me an output like

==> EVO_ANGLE.csv <==
"evo_ang_id","angle_description"

==> EVOP_IMAGE.csv <==
"evop_image_id","evop_id","evo_ang_id","evo_collection","file_format","image_name","image_path", "image_type"

==> IMAGE_TYPE.csv <==
"id","image_type","group","description"

As you can see files EVO_ANGLE and EVOP_IMAGE are related via evo_ang_id and EVOP_IMAGE and IMAGE_TYPE have the image_type in common.

Is there a better way for me to print this information: Where for each file I can see which other files have the field?

My best crack at this is writing a shell script that does the following in order:

  1. Get the first line of each file and store in a map in an array
  2. For each word in each line, find where in array it appears
  3. Print the output after collating this information.

This is a chore and will require a fair deal of debugging to get right and eyeballing the console output could be faster. Is there a better way? Any tricks with cut/join/grep combo?

2

If you're looking on which file a certain attribute is belonging to, you could use awk.

Provided that your csv files looks like:

$ for i in *.csv; do echo $i; head -n1 $i; echo; done
EVO_ANGLE.csv
"evo_ang_id","angle_description"

EVOP_IMAGE.csv
"evop_image_id","evop_id","evo_ang_id","evo_collection","file_format","image_name","image_path", "image_type"

IMAGE_TYPE.csv
"id","image_type","group","description"

The following awk command will reverse the attributes and filename:

$ awk -F', *' '                  # field separator = comma and optional spaces
      FNR==1{                    # Parse only the first line of each file.
         for(i=1;i<=NF;i++)             # Loop through all fields, and store them
            a[$i]=a[$i] " " FILENAME    # in an array together with the filename.
      }
      END{                          # When all files parsed, 
         for(i in a) print i,a[i]   # print the content of the array
      }' *.csv
"image_name"  EVOP_IMAGE.csv
"evo_collection"  EVOP_IMAGE.csv
"image_path"  EVOP_IMAGE.csv
"file_format"  EVOP_IMAGE.csv
"image_type"  EVOP_IMAGE.csv IMAGE_TYPE.csv
"evop_id"  EVOP_IMAGE.csv
"evop_image_id"  EVOP_IMAGE.csv
"id"  IMAGE_TYPE.csv
"evo_ang_id"  EVO_ANGLE.csv EVOP_IMAGE.csv
"description"  IMAGE_TYPE.csv
"group"  IMAGE_TYPE.csv
"angle_description"  EVO_ANGLE.csv

If you need to filter the attributes that belongs to several files, just use the following:

$ awk -F', *' 'FNR==1{for(i=1;i<=NF;i++) a[$i]=a[$i] " " FILENAME}END{for(i in a) print i,a[i]}' *.csv | awk 'NF>2'
"image_type"  EVOP_IMAGE.csv IMAGE_TYPE.csv
"evo_ang_id"  EVO_ANGLE.csv EVOP_IMAGE.csv
  • Time to learn some awk :) – PhD Feb 24 '17 at 18:12
2

Here's a bash-centric version; appears very similar to oliv's awk version

unset fileheads fields
declare -A fileheads
declare -A fields
for f in *.csv
do 
  IFS=, fileheads[$f]=$(head -n1 "$f");
  set -f
  for field in ${fileheads[$f]}
  do
    fields[$field]+=x
  done
  set +f
done

for field in ${!fields[*]}
do
  [[ ${#fields[$field]} -gt 1 ]] || continue 
  for file in ${!fileheads[*]}
  do
    [[ ${fileheads[$file]} =~ $field ]] && echo "$file has $field"
  done
  echo
done

This gathers the fields (line 1) of each file into the fileheads associative array, indexed by the filename. It also gathers a list of how many times it's seen each field name. We assume here that commas do not appear in the field names themselves.

We then loop through all of the known fields; if any of them were seen more than once, we loop through the files (the indices in the fileheads array) to see if any of them contain that field. At least two files should match this criteria; their filenames and linked field are echo'd out, followed by a blank line, for readability.

Sample run:

input

$ head -n1 *.csv
==> EVOP_IMAGE.csv <==
"evop_image_id","evop_id","evo_ang_id","evo_collection","file_format","image_name","image_path","image_type"

==> EVO_ANGLE.csv <==
"evo_ang_id","angle_description"

==> IMAGE_TYPE.csv <==
"id","image_type","group","description"

output

EVOP_IMAGE.csv has "evo_ang_id"
EVO_ANGLE.csv has "evo_ang_id"

EVOP_IMAGE.csv has "image_type"
IMAGE_TYPE.csv has "image_type"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.