user3220172 user3220172 - 7 months ago 26
Bash Question

dynamic csv file processing using shell script

I'm a new bee in shells scripting and wondering if anyone can help me out with following requirement .

I need to write a shell script code which will read the below tables from a csv file and make sql insert script.

below is the data extracted from csv file

Employee,Employee_no,Name,Depart_code,PF No,Salary
Start_employee,,,,,
,234,kasdjf,dev,27,394899
,233,ghasdjf,tes,25,294845
,235,dfsdjf,des,28,404899
end_employee,,,,,
,,,,,
Department,depart_code,depart_name,,,
Start_department,,,,,
,dev,development,,,
,tes,testing,,,
,des,design,,,
end_department,,,,,

Answer

Here is how you could solve it using awk commands in a for loop over the files where you split your csv:

#!/bin/bash
INPUTCSV="$1"
if [[ ! -f "${INPUTCSV}" ]] ; then
  echo error: no input file
  exit 10
fi
PID=$$
#remove everything before start:
rm -i file*txt
echo "" > insert.sql
# split data in ordered list of file
awk -v f=1 -v r=$PID '/^START_|^END_/{f;++f;next}
       {print $0 >> "file_"f"" r".txt";}' "${INPUTCSV}"
MODE="TABLE"
# ordered loop on the above files
for datafile in $(find . -type f -name "file_?${PID}.txt"|sort -n) ; do
  # we start with table definition:
  if [[ "${MODE}" == "TABLE" ]] ; then
    awk -F","  '{
                  {x=""; printf "insert into "$1" (\"" }
                  for(i=2 ; i<=NF; i++) {
                    if ($i != "") { printf x$i ; x="\",\"" }
                  }
                  print "\")"
                }' $datafile >> insert.sql
    # switch mode for each file
    MODE="DATA"
  else
    awk -F"," -v sel="      select "  '{
                  {x=""; printf sel "~" }
                  for(i=2 ; i<=NF; i++) {
                    if ($i != "") { printf x$i ; x="~,~" }
                  }
                  sel="union select "
                  print "~ from dual"
                }' $datafile  | tr "~" "'"  >> insert.sql
    echo ";" >> insert.sql
    # switch mode for each file
    MODE="TABLE"
  fi
done
# remove possible empty inserts:
cat insert.sql |sed '/^insert into  \(\)/d' > insert_final.sql

There are many tricks I had to add to this above code to produce inserts dynamically. It will be a little long to explain to you. The thing is you should learn to split your big problems into smaller parts.

Here is the result from your input:

insert into Employee ("Employee_no","Name","Depart_code","PF No","Salary")
      select '234','kasdjf','dev','27','394899' from dual
union select '233','ghasdjf','tes','25','294845' from dual
union select '235','dfsdjf','des','28','404899' from dual
;
insert into Department ("depart_code","depart_name")
      select 'dev','development' from dual
union select 'tes','testing' from dual
union select 'des','design' from dual
;