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 am afraid to ask but what specific crowd are you talking about? Most people wanted Mozilla to concentrate on the browser but of course are still complaining when they do that. Anyway Firefox works just fine for me. We all deserve the Chrome only future that is coming.
    • Such a missed opportunity for a company with arena sized buckets of cash. The first on device AI and they never really improved it all those years. They could have been leading this charge.
    • Glow 25.06 by Razvan Serea Glow provides detailed reporting on every hardware component in your computer, saving you valuable time typically spent searching for CPU, motherboard, RAM, graphics card, and other stats. With Glow, all the information is conveniently presented in one clean interface, allowing you to easily access and review the comprehensive hardware details of your system. Glow provides detailed information on various system aspects, including OS, motherboard, processor, memory, graphics card, storage, network, battery, drivers, and services. The well-organized format ensures easy access to the required information. You can export all the gathered data to a plain text file, facilitating sharing with others for troubleshooting purposes. No installation needed. Just decompress the archive, launch the executable, and access computer-related information. Glow runs on Windows 11 and Windows 10 64-bit versions. Glow 25.06 release notes: What's new Support provided for Windows 11 24H2 May 2025 update. Support provided for Intel Arrow Lake-H series processors. Support for AMD Ryzen AI series processors. Support for NVIDIA RTX 50 series graphics cards. Support for AMD RX 9000 series graphics cards. Support for processors and graphics cards from Intel, AMD and NVIDIA until May 2025. Support for GDDR7 graphics memory. .NET Framework June 2025 security update has been integrated. Fixed Bugs Fixed DPI bug in the Memory Test Tool that caused buttons to be nested with the table at 150% DPI and above. Fixed a translation bug in the Cache Cleanup Tool that caused an incorrect character encoding set in the Russian language. Fixed various DPI bug and character encoding bug fixes. Note: Always unzip the program before using it. Otherwise you may get an error. Download: Glow 25.06 | 2.0 MB (Open Source) View: Glow Homepage | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • How to enable the redesigned Windows 11 Start menu by Taras Buria This week, Microsoft started testing a big redesign for Windows 11's Start menu. It is now rolling out to Windows Insiders in the Dev and Beta Channels, addressing quite a few pain points in the original Start menu, which was shipped in late 2021. Nearly four years later, we finally have the long-anticipated redesign (announced earlier this year). The updated menu now features a single-page view, with all your pins and apps on one scrollable page. You can change the list of all apps between three variants: list, grid, and category. More importantly, you can now hide the recommended section in the Start menu settings (one of the most requested Start menu-related changes). The new Start menu is available in this week's Dev and Beta update. However, like always, Microsoft is rolling out changes gradually, which means you might not have the lucky ticket even on the latest build. If you do not want to wait, you can force-enable the new Start menu and its Phone Link button using a few commands in the ViVeTool app. Here is how to do that: Download ViveTool from GitHub and unpack the files in a convenient and easy-to-find folder. Run Command Prompt as Administrator and navigate to the folder containing the ViveTool files with the CD command. For example, if you have placed ViveTool in C:\Vive, type CD C:\Vive. Type vivetool /enable /id:47205210,49221331,49381526,49402389,49820095,55495322,48433719 and press Enter. Restart your computer. As usual, keep in mind that stuff in preview builds is less stable, so be aware of the risks of running preview builds. While Microsoft is not saying when the new Start menu will be available to all users, it will probably not take too long before it shows up in Release Preview and non-security updates. By the way, if you are curious, check out some of the prototypes that Microsoft considered when designing the new Start menu. Credit for the IDs goes to @phantomofearth on X.
    • reddit's "bottom of the barrel" > 99% of neowin front page "news"
  • Recent Achievements

    • Week One Done
      IAMFLUXX earned a badge
      Week One Done
    • One Month Later
      Æhund earned a badge
      One Month Later
    • One Month Later
      CoolRaoul earned a badge
      One Month Later
    • First Post
      Kurotama earned a badge
      First Post
    • Collaborator
      Carltonbar earned a badge
      Collaborator
  • Popular Contributors

    1. 1
      +primortal
      505
    2. 2
      ATLien_0
      268
    3. 3
      +FloatingFatMan
      234
    4. 4
      +Edouard
      201
    5. 5
      snowy owl
      162
  • Tell a friend

    Love Neowin? Tell a friend!