burnhot |
02-18-2015 02:20 AM |
Bash Script to manually import CSV into sqlite3 -> Spaces mess up everything
Hi guys
For a school project we have to manually import a CSV-file into a sqlite3 table. I've now got this code and it is almost done but I habe the following problem: While we are allowed to remove the unnecessay rows at the beginning and the end of the file, we are not allowed to alter the data itself. Meaning I can replace all <'> with <''> so that sqlite3 is able to read the single quote within a string but not replace the spaces with <_>.
The code works as it is now but as soon as I do not replace the spaces I get the following errors:
http://imgur.com/aDZXufm
"Nationalbank" and "Bundesplatz" are both parts of a string that contains a space. e.g. "Nationalbank Schweiz". Does anyone have a solution for this problem? I know it must have something to do with sqlite3 and that it uses spaces as seperator, but I thought I solved that problem with the quotes around the strings (e.g. '$hauptsitz').
Thank you
Code:
database="$PWD/SIX_BankenstammCH.db"
table="SIX_Banken"
i=1
sqlite3 $database <<EOF
drop table $table;
create table $table(SortNr INTEGER PRIMARY KEY, BCNr INTEGER, IBAN INTEGER, PZValidierung INTEGER, BCNrIID INTEGER, LandCode TEXT, PostIn TEXT, PostOut TEXT, SWIFT TEXT, Email TEXT, MutationsDatum TEXT, Hauptsatz INTEGER, BCArt INTEGER, BankInstitution TEXT, SIC INTEGER, euro_sic INTEGER, Sprache TEXT, Kurzbezeichnung TEXT, Domizil TEXT, Postadresse TEXT, PLZ TEXT, Ort TEXT, Telefon TEXT, Fax TEXT, Vorwahl TEXT);
EOF
sed -n '2,2561 p' SIX_BankenstammCH.csv > temp
sed 's/,/ /g' temp > temp2
sed 's/\// /g' temp2 > temp3
sed "s/'/''/g" temp3 > temp4
sed 's/ /_/g' temp4 > temp5
for row in $(cat temp5);
do
sortNr=$(echo $row | cut -d ";" -f 1)
bCNr=$(echo $row | cut -d ";" -f 2)
teilnIBAN=$(echo $row | cut -d ";" -f 3)
pzVal=$(echo $row | cut -d ";" -f 4)
bcNrIID=$(echo $row | cut -d ";" -f 5)
landCode=$(echo $row | cut -d ";" -f 6)
postInp=$(echo $row | cut -d ";" -f 7)
postOut=$(echo $row | cut -d ";" -f 8)
swift=$(echo $row | cut -d ";" -f 9)
email=$(echo $row | cut -d ";" -f 10)
mutDat=$(echo $row | cut -d ";" -f 11)
hauptsitz=$(echo $row | cut -d ";" -f 12)
bcArt=$(echo $row | cut -d ";" -f 13)
bank=$(echo $row | cut -d ";" -f 14)
sic=$(echo $row | cut -d ";" -f 15)
eurosic=$(echo $row | cut -d ";" -f 16)
sprache=$(echo $row | cut -d ";" -f 17)
kurzbez=$(echo $row | cut -d ";" -f 18)
domizil=$(echo $row | cut -d ";" -f 19)
postadr=$(echo $row | cut -d ";" -f 20)
plz=$(echo $row | cut -d ";" -f 21)
ort=$(echo $row | cut -d ";" -f 22)
tel=$(echo $row | cut -d ";" -f 23)
fax=$(echo $row | cut -d ";" -f 24)
vorwahl=$(echo $row | cut -d ";" -f 25)
sqlite3 $database "insert into $table values($sortNr,'$bcNr','$teilnIBAN','$pzVal','$bcNrIID','$landCode','$postInp','$postOut','$swift','$email','$mutDat','$hauptsitz','$bcArt','$bank','$sic','$eurosic','$sprache','$kurzbez','$domizil','$postadr','$plz','$ort','$tel','$fax','$vorwahl');"
#echo "row $i $row: "
#i=$((i+1))
done
sqlite3 SIX_BankenstammCH.db "select * from $table;"
|