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

    • The Google Home app gets video forwarding support and many more features by Aman Kumar Along with releasing the Android 16 update for supported Pixel devices, Google has also showcased a number of features coming to its Home app. First up is PiP, also known as picture-in-picture mode, which will be available for Nest Cams on any Google TV device you own. It’ll be similar to YouTube’s PiP, with which you must be familiar with. A small window will appear in a corner of the TV screen, allowing you to view your Nest Cams without interrupting your viewing experience. The feature is currently in public preview, and you can enroll to try it out before its public release. Another YouTube feature that Google is adding to its Home app is the ability to jump 10 seconds forward or backward in recorded videos. This feature ensures that you don't have to go through the entire footage to locate the moment you’re looking for. Google mentioned in its blog post that it is adding more controls to the Google Home web app. Currently, the web app offers limited functionality, such as setting automations and viewing cameras, but soon you’ll be able to manage more things through it, such as adjusting lighting, controlling temperature, and locking or unlocking the door. Google’s AI model, Gemini, is also getting more controls in the Home app. You can use natural language in the Gemini app to search for specific footage in the camera history. Furthermore, the fallback assistant experience that broadcast commands use is also being updated. You’ll now be able to use your voice to broadcast messages through the connected speakers in your home. The Google blog post also mentions that you are no longer required to use the standalone Nest app to receive smoke and other critical alerts. You can now view the Nest Protect smoke and carbon monoxide (CO) status directly in the Home app. You’ll also be able to run safety checkups and hush alarms through the Home app. In addition to all these features, Google is also making the automation creation process much quicker, will allow you to add more tiles to the Home app Favorites section, and will let you create different Favorites for any other device you use, such as your smartwatch. The Home app will now also support third-party Matter locks. Similar to the Nest x Yale lock, you’ll be able to control various settings of these third-party locks, like managing household access, creating guest profiles, and more.
    • Google Chrome 137.0.7151.104 (offline installer) by Razvan Serea The web browser is arguably the most important piece of software on your computer. You spend much of your time online inside a browser: when you search, chat, email, shop, bank, read the news, and watch videos online, you often do all this using a browser. Google Chrome is a browser that combines a minimal design with sophisticated technology to make the web faster, safer, and easier. Use one box for everything--type in the address bar and get suggestions for both search and Web pages. Thumbnails of your top sites let you access your favorite pages instantly with lightning speed from any new tab. Desktop shortcuts allow you to launch your favorite Web apps straight from your desktop. Chrome has many useful features built in, including automatic full-page translation and access to thousands of apps, extensions, and themes from the Chrome Web Store. Google Chrome is one of the best solutions for Internet browsing giving you high level of security, speed and great features. Important to know! The offline installer links do not include the automatic update feature. Google Chrome 137.0.7151.104 changelog: [$8000][420150619] High CVE-2025-5958: Use after free in Media. Reported by Huang Xilin of Ant Group Light-Year Security Lab on 2025-05-25 [NA][422313191] High CVE-2025-5959: Type Confusion in V8. Reported by Seunghyun Lee as part of TyphoonPWN 2025 on 2025-06-04 Download web installer: Google Chrome Web 32-bit | Google Chrome 64-bit | Freeware Download: Google Chrome Offline Installer 64-bit | 128.0 MB Download: Google Chrome Offline Installer 32-bit | 115.0 MB Download page: Google Chrome Portable Download: Google Chrome MSI Installers for Windows (automatic update) View: Chrome Website | Release Notes Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • I was a little disappointed with mine compared to the Lemokey P1, for example. Seemed cheap compared to all aluminum. But I liked the look to go with my Fractal North. Ended up getting a Keychron Q1 HE and a Lemokey L5 HE. Love the L5, which replaced my P1. The definitely aren't light boards.
    • Thunderbird 139.0.2 by Razvan Serea Thunderbird is a free, open-source, cross-platform application for managing email and news feeds. It is a local (rather than a web-based) email application that is powerful yet easy-to-use. Thunderbird is clean and elegant by default, but easily customizable to match your workflow and visual preferences. It is loaded with unique and powerful features. Thunderbird is developed, tested, translated and supported by the folks at Mozilla Corporation and by a group of dedicated volunteers. Thunderbird gives you control and ownership over your email. There are lots of add-ons available for Thunderbird that enable you to extend and customize your email experience. Thunderbird gives you IMAP/POP support, a built-in RSS reader, support for HTML mail, powerful quick search, saved search folders, advanced message filtering, message grouping, labels, return receipts, smart address book LDAP address completion, import tools, and the ability to manage multiple e-mail and newsgroup accounts. Thunderbird 139.0.2 fixes: Security fixes Download: Thunderbird 139.0.2 for Windows (EN/US) | 32-bit | ~70.0 MB (Open Source) Download: Thunderbird 139.0.2 for Linux (EN/US) | 74.7 MB Download: Thunderbird 139.0.2 for Mac OS (EN/US) | 127.0 MB Download: Thunderbird 139.0.2 in other languages View: Thunderbird Website | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
  • Recent Achievements

    • Week One Done
      elsa777 earned a badge
      Week One Done
    • One Month Later
      elsa777 earned a badge
      One Month Later
    • First Post
      K Dorman earned a badge
      First Post
    • Reacting Well
      rshit earned a badge
      Reacting Well
    • Reacting Well
      Alan- earned a badge
      Reacting Well
  • Popular Contributors

    1. 1
      +primortal
      544
    2. 2
      ATLien_0
      272
    3. 3
      +FloatingFatMan
      207
    4. 4
      +Edouard
      201
    5. 5
      snowy owl
      139
  • Tell a friend

    Love Neowin? Tell a friend!