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

    • I don't think even with these options, will people enroll in this program. I mean, it's certainly a welcome change for us power users, and I think many will take advantage of it. But like the article says, Windows 10 remains the dominant OS, and I don't think this is going to change in 4 months time. Having mainstream Windows 10 users have to jump through hoops just to continue to receive updates is going to be an unnecessary hassle. The only remedy is for Microsoft to extend support for 4 more years, giving users enough time to just upgrade their PCs as the hardware naturally ages out.
    • Windows 11 gets fixes for stuck Windows Update by Taras Buria Earlier this week, Microsoft started a gradual rollout of KB5062324, a small configuration update for Windows 11 version 24H2. The goal is to address an issue with a stuck Windows Update, which is unable to finish scanning for available updates. Microsoft is rolling out KB5062324 gradually. You can get it by heading to Settings > Windows Update and toggling "Get the latest updates as soon as they're available." After that, restart your computer and check for available updates. Windows Update should properly detect KB5062324 and download it to fix issues with update detection. In the support document for KB5062324, Microsoft added that the bug could be mitigated by restarting Windows. This will get Windows Update unstuck and allow the system to finish update scanning. Users can also get a permanent solution by downloading KB5058499 (the May 2025 non-security update) or a newer release. Note that the bug only affects computers with Windows 11 version 24H2. Older releases are unaffected. Windows 11 version 24H2 still has a few known issues awaiting their fixes. For example, there are compatibility issues with the sprotect.sys driver, which causes apps to stop responding, audio output losses on PCs with Dirac Audio, apps hanging when using cameras, and more. You can find the complete list of known issues in Windows 11 version 24H2 here. Speaking of updates and fixes, Microsoft today released a non-security update for Windows 10. KB5061087 (build number 19045.6036) is now available for download, and it includes various fixes for the Start menu, USB printers, incorrect Windows version reporting, and more. The update arrived minutes after Microsoft announced that the Extended Security Update program would be free for Windows 10 users. Also, the company recently revealed its plans to pull legacy drivers from Windows Update.
    • Well this will provide me time to save funds for 2 Replacement Windows 11 Compatible Machines, tried to save since my birthday in February, but savings not much at moment--guess ordered way too much Doordash. So will get this with either WIndows backup with the settings to the Cloud or 1000 MS Reward points, Then work on saving for 2 Windows 11 Compatible machines. Get sisters Windows 10 Laptop squared away for a year, then will be up to her to afford replacement of that one, but my systems i'm getting replaced somehow, even if i have to goto with a Mini PC for PC 2, someway somehow all systems gonna be Windows 11 by October 2026.
    • Corporate America at its finest. As if the long history of unreliable vehicles coming from American manufacturers wasn't bad enough already, we're given yet another major reason to never purchase ANY America-made vehicle...
    • If I were to guess...the hardware division is going the chop block.
  • Recent Achievements

    • Week One Done
      DrRonSr earned a badge
      Week One Done
    • Week One Done
      Sharon dixon earned a badge
      Week One Done
    • Dedicated
      Parallax Abstraction earned a badge
      Dedicated
    • First Post
      956400 earned a badge
      First Post
    • Week One Done
      davidfegan earned a badge
      Week One Done
  • Popular Contributors

    1. 1
      +primortal
      603
    2. 2
      ATLien_0
      224
    3. 3
      Michael Scrip
      168
    4. 4
      +FloatingFatMan
      153
    5. 5
      Xenon
      137
  • Tell a friend

    Love Neowin? Tell a friend!