Cron Task to Download CSV and Dump Into MySQL


Recommended Posts

Hey,
Ok..Trying to wrap my head around this..
Basically, what I'm trying to do is every thirty minutes, download a csv file, and then dump it into MySQL database, so that I can view the results from my PHP website...
I haven't tested this yet (Never used a cron before), but from my searching, to setup the cron, I would use:
 

0,30 * * * * wget -O - http://example.com/test.csv

But..Once I have the csv file..I can't seem to figure out what to do with it...Maybe I'm just tired, but I have no idea how to get it into the database...A push in the right direction would help a lot..

 

(In case it matters, using Debian, everything up-to-date)

 

Thanks!

You would then run a sql query via MySQL command line. So you would set up another cron to run a minute or an hour later. Depending on how long it takes the csv to download. So you figure out the time it takes to download the csv and then run a bash script at that time interval. I Think it's cleaner to point cron to a bash script that runs the sql query instead of inside the cron file.

Look up MySQL queries view command line.

Example

$ mysql -u vivek -p -e 'SELECT COUNT(*) FROM quotes' cbzquotes

Source: http://www.cyberciti.biz/faq/run-sql-query-directly-on-the-command-line/

So you would just run an import into the table your php script parses for the data.

A bash script..That's a good idea...Never used one of those either, I'll have to read up on those, thank for the suggestion!

And I should have figured out MySQL from command line...Think I've been staring at lines of code for too long today..Think I was partly trying to over-complicate things...lol Never liked it too much though...I still need to get more comfortable with command line.

But I'll look into the bash script idea, thanks!

Bash scripts are easy and wonderful. Throw a quick file together and name it .sh. Make it executable by running chmod +x filename.sh

Example:

#!/bin/sh

MySQL -u user -p password -h localhost -e 'query'

Put the above 2 lines in a file, save it and have cron run the file.

Same cron entry like above with obviously different interval. The command will be /path/to/script.sh

Cron run it as root so you don't have to worry about permissions just make sure it's executable.

Thanks :)

I had thought about learning about Bash scripts when I was trying to learn Linux, but could never think of a use for it, so never got around to it lol Looks fairly straight forward..Hopefully this weekend I'll have time to play around with it, I'll let you know how it goes!

Ok, finally had a chance to work on this..Had to work on something else for the project quickly.

Anyways, I'm getting commands not found...

#!/bin/sh

wget "http://www.example.com/test.csv"

sleep 40

MySQL -u user -p password

use databseName

load data local infile 'table.csv' into table tableName
IGNORE 1 LINES
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(column1, column2, column3, column4, column5, column6, column7)

And I get this returned:

/var/www/html/scripts/test.sh: line 7: MySQL: command not found
/var/www/html/scripts/test.sh: line 9: use: command not found
/var/www/html/scripts/test.sh: line 11: load: command not found
/var/www/html/scripts/test.sh: line 12: IGNORE: command not found
/var/www/html/scripts/test.sh: line 13: fields: command not found
/var/www/html/scripts/test.sh: line 14: enclosed: command not found
/var/www/html/scripts/test.sh: line 15: lines: command not found
/var/www/html/scripts/test.sh: line 16: column1,: command not found

It seemed to have downloaded the file..However when I check /var/www/html/scripts/, the csv file isn't there..

But still not sure what to do about the commands not found..What did I do wrong..?

Thanks! Using something similar..(I had quotes in there, so changed it to QUERY_INPUT)

#!/bin/sh

wget -O table.csv "http://www.example.com/table.csv"

sleep 40

mysql -u<user> -p<password> dataBase <<QUERY_INPUT

load data local infile 'table.csv' into table testing
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
IGNORE 1 ROWS
(column1, column2, column3, column4, column5, column6, column7)
QUERY_INPUT

the 'mysql' part needed to be lowercase as well.

That's the final file for anyone who needs it.

The 'sleep 40' is there to make sure the file downloads before loading MySQL.

'Ignore 1 Rows' is to ignore the first row

 

Thanks for the help!

 

Marking Sikh's as the answer, however thank you Haggis as well!

Didn't get notifications for this, gurrrr. Thanks haggis for helping out.

 

Glad you figured it out. I would make the sleep a minute or two just incase mysql gets tied up for some reason. Its always good to overestimate then it is to underestimate because if you don't get email notifications that your script is running or failed to run, the next time you look at it, it might have not been working for 2 weeks or 2 days.

 

So I recommend up'ing the sleep

This topic is now closed to further replies.
  • Posts

    • Wrong again. Electric vehicles are more reliable than gasoline vehicles because they have fewer points of failure. On average, they last 50% longer. Its your right to be incorrect and my right to correct you.
    • Hello, I am using a Hitron CODA56 cable modem with Comcast Xfinity's 1200 Mbps service.  No issues noted.  I had looked at the Motorola and Netgear options for a cable modem, but neither was available for purchase at the time I upgraded my cable connection. There are multiple models in the Netgear Nighthawk X10 line-up.  I am guessing you have either the the Netgear Nighthawk X10 AD7000 model (R8900) or the Netgear Nighthawk X10 AD7200 model (R9000) model, is that correct?  Both of these only have one gigabit WAN (internet) ports for connection to the modem, all of the remaining ports for the LAN side of things are gigabit Ethernet ports.  The 10GbE port on the devices is meant for connection to local NAS storage.  I suppose you could connect the desktop computer directly to it, although it would still be limited by the gigabit connection between the cable modem and the Netgear residential gateway broadband router. I would suggest looking for a residential gateway broadband router from a company like Asus, Netgear, TP-Link or maybe even Ubiquiti, depending upon budget, that has 2.5Gbps (or faster) WAN and LAN ports.  That would allow you to make full use of the 1.2Gbps connection from your ISP as well as have some room for future growth, speed-wise. Regards, Aryeh Goretsky      
    • Firefox 140.0.1 by Razvan Serea Firefox is a fast, full-featured Web browser. It offers great security, privacy, and protection against viruses, spyware, malware, and it can also easily block pop-up windows. The key features that have made Firefox so popular are the simple and effective UI, browser speed and strong security capabilities. Firefox has complete features for browsing the Internet. It is very reliable and flexible due to its implemented security features, along with customization options. Firefox includes pop-up blocking, tab-browsing, integrated Google search, simplified privacy controls, a streamlined browser window that shows you more of the page than any other browser and a number of additional features that work with you to help you get the most out of your time online. Firefox 140.0.1 fixes: Fixed text contrast issues in the sidebar with some dark themes. (Bug 1971487) Fixed a startup crash experienced by some users caused by DLL injection. (Bug 1973947) Download: Firefox 64-bit | Firefox 32-bit | ARM64 | ~60.0 MB (Freeware) Download: Firefox 140.0.1 for Linux | 64-bit | ~90.0 MB Download: Firefox for MacOS | 127.0 MB View: Firefox Home Page | Release Notes Get alerted to all of our Software updates on Twitter at @NeowinSoftware
  • Recent Achievements

    • Week One Done
      Marites earned a badge
      Week One Done
    • One Year In
      runge100 earned a badge
      One Year In
    • One Month Later
      runge100 earned a badge
      One Month Later
    • One Month Later
      jfam earned a badge
      One Month Later
    • First Post
      TheRingmaster earned a badge
      First Post
  • Popular Contributors

    1. 1
      +primortal
      575
    2. 2
      ATLien_0
      184
    3. 3
      +FloatingFatMan
      178
    4. 4
      Michael Scrip
      136
    5. 5
      Xenon
      119
  • Tell a friend

    Love Neowin? Tell a friend!