Bash Script to manually import CSV into sqlite3 -> Spaces mess up everything

burnhot · 02-18-2015, 02:20 AM

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;"

lambo69 · 02-18-2015, 01:17 PM

The error messages tell different things:
-you are trying to inserting data into columns that don't exist
-you try to insert duplicate primary keys

Try to echo your inserts before sending them to db and analyze them. Have a look on quotation marks and apostrophes. Your clue with whitespaces points to the right direction, but leads you to the wrong conclusion (it doesn't seem to be an sqlite issue to me).