Latest LQ Deal: Complete CCNA, CCNP & Red Hat Certification Training Bundle
Go Back > Forums > Linux Forums > Linux - Software
User Name
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.


  Search this Thread
Old 01-12-2010, 06:31 PM   #1
Registered: Oct 2006
Location: California, USA
Distribution: Mint 16, Lubuntu 14.04, Mythbuntu 14.04, Kubuntu 13.10, Xubuntu 10.04
Posts: 554

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
Old 01-12-2010, 07:11 PM   #2
LQ Guru
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.9, Centos 7.3
Posts: 17,411

Rep: Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397Reputation: 2397
It sounds like you want to UPDATE an existing row's qty value, keyed on upc eg
UPDATE table1
SET qty = $qty
WHERE upc = $upc;
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 & so on.

Last edited by chrism01; 01-12-2010 at 11:36 PM. Reason: typo
Old 01-12-2010, 07:42 PM   #3
Senior Member
Registered: Oct 2003
Location: Bonaire, Leeuwarden
Distribution: Debian /Jessie/Stretch/Sid, Linux Mint DE
Posts: 4,703

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

upc, qty, price, description, bar
8769876, 76, 23.16, funny foo bars, 98
If you make this statement:
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
mysql -u user -ppasswd <update.sql
However, if your CSV file contains quoted fields like:
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:

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

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

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.


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

All times are GMT -5. The time now is 07:07 AM.

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