LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 02-18-2015, 02:20 AM   #1
burnhot
LQ Newbie
 
Registered: Feb 2015
Location: Switzerland
Posts: 1

Rep: Reputation: Disabled
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;"
 
Old 02-18-2015, 01:17 PM   #2
lambo69
Member
 
Registered: Sep 2012
Distribution: Slackware, Manjaro, Slackarm, Raspbian, Debian
Posts: 68

Rep: Reputation: Disabled
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).
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
PHP script to import CSV file into MySQL database JoseCuervo Linux - Server 3 08-01-2014 06:18 AM
trying to write shell snip to import CSV data into BASH array dstrout Programming 10 01-07-2014 02:49 PM
How to end the bash script using commands in bash not manually by pressing ctrl+c Sanpreet Singh Linux - Newbie 1 07-03-2013 01:04 PM
[SOLVED] How to import .txt file into sqlite3 database using C API? Newbie89 Linux - Newbie 3 05-06-2013 04:32 AM
bash script with spaces Quantum0726 Programming 2 11-14-2005 09:26 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 01:38 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration