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 |
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
Are you new to LinuxQuestions.org? Visit the following links:
Site Howto |
Site FAQ |
Sitemap |
Register Now
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
|
 |
02-18-2015, 02:20 AM
|
#1
|
LQ Newbie
Registered: Feb 2015
Location: Switzerland
Posts: 1
Rep: 
|
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;"
|
|
|
02-18-2015, 01:17 PM
|
#2
|
Member
Registered: Sep 2012
Distribution: Slackware, Manjaro, Slackarm, Raspbian, Debian
Posts: 68
Rep: 
|
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).
|
|
|
All times are GMT -5. The time now is 11:58 AM.
|
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.
|
Latest Threads
LQ News
|
|