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

    • Apple introduces macOS Tahoe with a new Phone app, revamped Spotlight, and more by David Uzondu The speculation can finally end, as the rumors about the name turned out to be completely true. At its Worldwide Developers Conference today, Apple officially christened the next version of its desktop operating system macOS Tahoe. This release, part of the new "26" family of operating systems like iOS 26, is bringing more than just a new name to the table. The most significant and immediate change is a system-wide visual redesign Apple is calling "Liquid Glass." Let's talk about this new design, because it is the first thing you will notice. Apple is taking the translucent, layered look from its visionOS and bringing it everywhere. It is a fundamental change to how macOS looks and feels, which some Neowin readers are definitely not a fan of. Sidebars, toolbars, and menus all have this frosted glass effect, where their color and texture shift based on the window or wallpaper behind them. The menu bar is now completely transparent, and Apple is also adding more customization. You can now change the color of folders, which is a feature people have wanted for nearly forever, and add little symbols or emojis to them. The bigger story for day-to-day use might be how much tighter the Mac and iPhone are becoming. Last year, macOS Sequoia saw the release of apps like the dedicated Passwords app, and this year, with Tahoe, we get a full-blown Phone app on the Mac. It is not just for getting call notifications anymore. You can see your recent calls, check your voicemail, and access your contacts list just like you would on your iPhone. New features like Call Screening, which can figure out who is calling before you answer, are also included. Live Activities from your iPhone, like the status of an Uber ride or a food delivery, will now show up right in your Mac's menu bar. Spotlight search is also getting a massive update. For years, Spotlight has been a simple tool for finding files and launching apps. Now, Apple is trying to turn it into an action center. You will be able to perform tasks directly from the search results, like creating a new note or sending an email, without ever opening the corresponding application. The search results themselves are supposed to be smarter and are no longer separated into rigid categories. Everything just shows up in one big list, ranked by what the system thinks is most relevant to you. Despite Apple's ongoing AI challenges, Apple Intelligence is getting new abilities. The AI features introduced last year are being expanded. A new Live Translation feature can translate text in Messages or audio during a phone call or FaceTime, all on the device, to maintain privacy. The Shortcuts app is also getting more powerful. You can now build automations that tap directly into Apple's AI models to do more complex tasks, like summarizing an audio recording of a lecture and comparing it to your typed notes. And yes, Apple is still trying to make gaming on the Mac a serious thing. A new dedicated app called Apple Games is being introduced. It acts as a central library for all your games, similar to launchers you might see on a PC. It also includes a new Game Overlay, which lets you mess with system settings or chat with friends without leaving your game. Apple announced that titles like Cyberpunk 2077, Crimson Desert, and Lies of P: Overture are on their way to the platform. Other mainstays are getting refreshed as well. Safari has a redesigned tab layout, Messages is getting polls, and the Journal app is finally making its way from the iPhone to the Mac. The first developer beta for macOS Tahoe is available today, June 9, 2025. A public beta is expected to follow next month, with the final version being released for free to everyone with a compatible Mac this fall. You can check out all the details in Apple's official macOS announcement on the Newsroom.
    • Google introduces new analytics tools in Classroom by David Uzondu Google has started the rollout of new analytics tools for Google Classroom, adding a bunch of ways for educators to monitor the activity of their students. This latest addition puts a new "Analytics" tab on class pages, which, according to Google, is meant to help teachers "see relevant insights on the class analytics page that alert them on how students are progressing and where they may need additional support." For example, on the new page, there might pop up a notification saying "3 students' grades increased over 25% since last month," or, on the flip side, "1 student turned in over half their assignments late in the last month." On the Classwork page, teachers can now see a number next to an assignment showing how many students have not even opened the attached files in Google Drive. For any teacher who has stared at a blank submission list, wondering if a student is struggling or just forgot. It shows who has not even started, letting teachers poke a student privately or nudge the whole class to get going. Google says insights are triggered by factors like approaching deadlines or performance issues, such as a student scoring below 70%. But here is the catch: these new tools are not for everyone. This is a premium feature, locked behind the paid Google Workspace for Education Plus license, so schools using the free version are completely out of luck. For those with a subscription, the student engagement metric that shows unopened Drive files is available right now. The main "Analytics" tab and its associated alerts, however, are on a slower schedule. Their extended rollout begins today. The company says the tab itself should show up by June 30, while the full set of insights will continue to appear, with everything expected to be in place by August 1, 2025. Super admins get access to this automatically and will have to decide which other education leaders and staff get to see all the new data.
    • I welcome thee! Here here!
    • I noticed that macOS STILL does not have a Menubar Icon manager. Top right hand corner is already getting cluttered and they've needlessly added two extra things. We need a better way to manage it and all the third-party tools out there are awful. Don't offer me alternatives, I've tried them more than they are rubbish! Apple, fix this!
  • Recent Achievements

    • Rookie
      CHUNWEI went up a rank
      Rookie
    • Enthusiast
      the420kid went up a rank
      Enthusiast
    • Conversation Starter
      NeoToad777 earned a badge
      Conversation Starter
    • Week One Done
      VicByrd earned a badge
      Week One Done
    • Reacting Well
      NeoToad777 earned a badge
      Reacting Well
  • Popular Contributors

    1. 1
      +primortal
      484
    2. 2
      +FloatingFatMan
      277
    3. 3
      ATLien_0
      257
    4. 4
      Edouard
      206
    5. 5
      snowy owl
      199
  • Tell a friend

    Love Neowin? Tell a friend!