LinuxQuestions.org
Visit Jeremy's Blog.
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 01-12-2010, 05:31 PM   #1
xmrkite
Member
 
Registered: Oct 2006
Location: California, USA
Distribution: Mint 16, Lubuntu 14.04, Mythbuntu 14.04, Kubuntu 13.10, Xubuntu 10.04
Posts: 540

Rep: Reputation: 30
Update csv file into mysql db via bash script


Hello, I'm a total noobie at mysql. i really don't know too much.

But I have a very simple task that I need to accomplish. Hopefully you guys can help.

I have a csv file that has a upc code and a qty (inventory). I need to insert it into my mysql table, but the table has several columns including upc, qty, price, description, etc.

Can someone point me in the right direction to getting this done? Hopefully I can have a handy script when I'm done and all I do is run that script.

I just want to make sure we don't delete the description, price, etc stuff and only update qty, using the upc code as the identifier so that the right qty get's input into the table.

-Any ideas would be so awesome
-Thanks
 
Old 01-12-2010, 06:11 PM   #2
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.5, Centos 5.10
Posts: 16,225

Rep: Reputation: 2021Reputation: 2021Reputation: 2021Reputation: 2021Reputation: 2021Reputation: 2021Reputation: 2021Reputation: 2021Reputation: 2021Reputation: 2021Reputation: 2021
It sounds like you want to UPDATE an existing row's qty value, keyed on upc eg
Code:
UPDATE table1
SET qty = $qty
WHERE upc = $upc;
conceptually.
In bash you could write a loop to read the upc & qty from the csv (line-by-line) then replace the values in an SQL script like the above, then run the SQL script.
Alternatively, you could write eg a Perl program to just read the csv file and connect directly to the DB.
The bash mysql cmd is eg

mysql -u user -ppasswd <update.sql

assuming a local DB. See http://dev.mysql.com/doc/refman/5.0/en/mysql.html & so on.

Last edited by chrism01; 01-12-2010 at 10:36 PM. Reason: typo
 
Old 01-12-2010, 06:42 PM   #3
jlinkels
Senior Member
 
Registered: Oct 2003
Location: Bonaire
Distribution: Debian Lenny/Squeeze/Wheezy/Sid
Posts: 4,052

Rep: Reputation: 484Reputation: 484Reputation: 484Reputation: 484Reputation: 484
Usually I would write an awk script which produces a .sql file like chrism01 proposes. Imagine you have such a file:

Code:
upc, qty, price, description, bar
8769876, 76, 23.16, funny foo bars, 98
If you make this statement:
Code:
cat csv_file | awk -F, '{print "update table set qty=" $2 " where upc= "$1}' > update.sql
you'll get lines in that update.sql like:
update table set qty= 76 where upc= 8769876

and you feed that in MySQL
Code:
mysql -u user -ppasswd <update.sql
However, if your CSV file contains quoted fields like:
Code:
8769876, 76, 23.16, "funny foo bars", 98
you should run that file thru a files which inserts proper field separators. With quotes it is next to impossible to do that properly in awk. You can you this program: http://pdis.rnw.nl/~hansl/links/csv.c

jlinkels
 
Old 01-12-2010, 09:02 PM   #4
GooseYArd
Member
 
Registered: Jul 2009
Location: Reston, VA
Distribution: Slackware, Ubuntu, RHEL
Posts: 183

Rep: Reputation: 46
mysqlimport
 
Old 01-13-2010, 12:43 AM   #5
xmrkite
Member
 
Registered: Oct 2006
Location: California, USA
Distribution: Mint 16, Lubuntu 14.04, Mythbuntu 14.04, Kubuntu 13.10, Xubuntu 10.04
Posts: 540

Original Poster
Rep: Reputation: 30
Man, now that's exactly what i needed. Each of your methods works, but i'll probably use jlinkels cause i can run this from one command line entry pretty easily.

-Thanks again for the help.
 
  


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
CSV file - Duplicate records need merging | BASH? lmedland Programming 21 12-10-2010 05:01 AM
Importing a csv file into a mysql database kaplan71 Linux - Server 13 09-18-2009 06:13 PM
[SOLVED] Need help create a bash script to edit CSV File imkornhulio Programming 13 02-05-2009 10:23 AM
Sed doesn't update file in bash script chilebiker Programming 4 06-14-2007 08:25 AM
bash script - remove header row from csv file pljvaldez Programming 5 08-30-2006 11:05 AM


All times are GMT -5. The time now is 04:56 PM.

Main Menu
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
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration