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

    • Needs to happen quicker IMO. Pretty dang sick of the same articles reworded five different ways and reposted. I’m sure some people like reading the typical MS tells you why Windows 11 is better article over and over or the minute by minute countdown on the EOL of Windows 10, but I’m not one of them.
    • You must feel left out. I gave you an upvote to feel better.
    • Amarok 3.3 Beta 2 replaces Phonon API with GStreamer by David Uzondu Audio players come and go, but one of the long-standing pack leaders is Amarok, a KDE project that has been around since 2003. This player was an essential application for anyone who ran a KDE 3 system, mostly because its interface was simple and did not get in your way. After spending some time in hibernation following the 2.x series, development has picked up again. Now we have version 3.3 Beta 2, bringing some changes under the hood. The most important of which is a complete overhaul of the audio engine. The developers have ditched the old Phonon multimedia framework on Qt6 and reworked everything to use GStreamer instead. The developers have also partially restored CUE sheet support, which is fantastic news for anyone who keeps albums as single audio files. ReplayGain logic has been improved to apply a default fallback value for tracks without loudness data, helping to even out the volume. Some work was also done to prevent potential database issues that could happen during library scans. This release also finalizes some major changes that were introduced in the first beta, as outlined below: Dropping support for Qt5 and KDE Frameworks 5 entirely. It is Qt6 from here on out. Updating the database character set to allow full UTF-8 values, which is great for international music collections. Fixing a year 2038 bug for dates stored in the database. Removing TagLib extras support, which affects RealMedia and Audible files. Now, before you rush off to install it, there are a couple of things to know. Because the first beta updated the database schema, downgrading from this beta back to an older version is not really possible unless you manually backed up your music library first. Also, if you are a big Last.fm user, its official library has not caught up with the Qt6 world yet. You will need a specific build from a repository like this one to get those features working for now. If you are feeling adventurous and want to try the new beta, you will probably have to build it yourself. For that, you can check out the project on GitLab for the source code and guidance. Some Linux distributions might also package the beta in their testing or unstable repositories, so it is worth a look there, too. You can grab the latest stable version of Amarok on Linux via FlatHub.
    • Yeah, stable and consistent data-harvesting to secure you and your 801 partners bottom line...thanks but no thanks.
    • The ethical thing for MS to do (a foreign concept) would be to continue full support for WIndows-10; until WIndows-11 is stable and mature enough to be considered reliable. Give users the ability to get decent use out of hardware they have bought, that MS decided are no longer able to run WIndows-11.
  • Recent Achievements

    • Rising Star
      Phillip0web went up a rank
      Rising Star
    • One Month Later
      Epaminombas earned a badge
      One Month Later
    • One Year In
      Bert Fershner earned a badge
      One Year In
    • Reacting Well
      ChrisOdinUK earned a badge
      Reacting Well
    • One Year In
      Steviant earned a badge
      One Year In
  • Popular Contributors

    1. 1
      +primortal
      545
    2. 2
      ATLien_0
      205
    3. 3
      +FloatingFatMan
      170
    4. 4
      Michael Scrip
      150
    5. 5
      Som
      131
  • Tell a friend

    Love Neowin? Tell a friend!