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

    • Damn, I thought I posted the comment. Thanks for the clarification! That's pretty sweet! I didn't know about Cerebras. Is there anything interesting that you'd like to share? I'd love to keep learning
    • Wait, they're training their models in the cloud? I thought they had in-house Nvidia Blackwell powered data centers for training purposes.
    • I find it shocking you would claim that Tesla's self-driving is as good as Mercedes given the actual facts with fatalities (Tesla being involved in 53% of all fatalities between 2019-24) https://www.craftlawfirm.com/a...data/#accidents-per-company Part of the problem is the CEO too making wild claims about FSD when the system actually requires human supervision at all times, which then can be blamed on the ignorance of the driver. In my opinion both Tesla and the driver are at fault. I've only been in a Tesla once (as a passenger) where "FSD" was activated, and the driver only did it for a few miles and was ever ready to take back control. The Tesla owner was also fully aware that FSD was in his opinion not safe enough to be trusted without human intervention and supervision. But I guess this is "fake news" right? 🙄
    • iOS 26 Photos app: What's good and what's bad in the latest update by Aditya Tiwari Apple just announced the iOS and iPadOS 26 update for various supported iPhone and iPad models. After you get past the limelight of Liquid Glass and the new naming, you'll realize the update has several features and changes to offer. Let's talk about the redesigned Photos app on iOS 26 and what the hands-on experience feels like based on the first developer beta release. It's the second year in a row when Apple has made considerable changes to the Photos app. Last year, Apple killed its tabbed interface and replaced it with a single scrollable view that displays the Library at the top, followed by various Collections. The idea of giving too much too soon didn't work for the users, and the Photos app possibly attracted more haters than lovers. Apple realized that users weren't happy with the layout and took a U-turn this time. The revamped Photos app on iOS 26 brings the tabbed interface back. You will find two tabs this time in the main view: Library and Collections. The Library tab displays photos and videos in the same old grid layout you have been seeing for years now. As you scroll to view more photos, the Library and Collection buttons morph to reveal the "Years," "Months," and "All" tabs, which you can use accordingly. All of the buttons and options have received the glass treatment, and Apple has also rearranged some elements. For instance, the "View Options" button has moved from the bottom left to the top right corner near the "Select" button. The "Search" button now sits in the bottom right corner of the Photos app for easy access, particularly useful when using the iPhone with one hand. For comparison, this is how options in the Photos app looked on iOS 18. A highlight of the updated photos app is a feature called Spatial Scene. When you open an image in the app, you can tap on the Spatial Scene button at the top-right to transform the image into its 3D self. Once the processing is done, you can tilt your iPhone in different directions to experience the 3D effect. The feature works for new and old images alike, even the ones you clicked years ago. It doesn't work for videos and screenshots captured on the device. Moving on to the Collections tab, its default view displays different categories, such as Memories, Pinned, Albums, People, Shared Albums, and more, in a list that you can reorder according to your preference. You can tap on any category name to reveal the collections inside or tap the arrow next to it to display the same in a drop-down interface. You can customize the look and feel of the Collections tab using the three-dot menu at the top. It reveals two other layout options, as well as options to show, collapse, or reorder all the Collections categories in a single tap. You can also reorder the categories using the button present at the bottom of the Collections tab. Thanks to the collapsible nature of the Collections categories, the iOS 26 Photos app doesn't feel cluttered like its previous version. One useful thing to note is that the Photos app remembers the categories you expand in the Collections tab. In other words, if you expand just one (or more) category and collapse the rest of them, the Collections tab will stay that way even if you re-open the Photos app. Speaking of memory, the Photos app can also remember whether you were in the Library tab or the Collections tab when you last used the app. What's wrong with the new Photos app Apple's Liquid Glass-inspired Photos app has brought back the old familiar interface for users. However, it says goodbye to some of the good things Apple introduced in the last version. The single-scroll interface of the Photos app on iOS 18 made it easier to access collections like Screenshots and Videos. You could hide screenshots from the main Library view but quickly access them through a collection pinned just below it. Previous versions of the Photos app displayed the sync progress at the bottom of the Library. That information was hidden behind the profile picture icon on iOS 18. Now, with the latest update, you need several clicks to check whether your latest photos and videos are synced to your iCloud account because the profile picture icon is now a part of the Collections tab. Perhaps Apple chose to hide it away, as few people were bothered by its existence. However, the small feature was quite handy for users who had unstable internet connections. There is some indication, though, that the profile icon displays a progress bar and a yellow dot to inform about an unfinished sync process. On a side note, an early beta version of the iOS 18 Photos app allowed users to swipe right or left on the Library view to access different collections. The feature would have been quite helpful if it hadn't been cut, at least for users who want to access their Utilities, Media Types, Screenshots, and other collections quickly. It could have been made optional in the Photos app. That said, iOS 26 is still in its development phase, and the Photos app might add or remove features as more beta releases arrive. Apple is expected to push the first public beta sometime in July. You can try out the app now by installing the developer beta; however, keep in mind that the build may be unstable and surface unexpected bugs.
  • Recent Achievements

    • Posting Machine
      David Uzondu earned a badge
      Posting Machine
    • One Month Later
      Stokenking earned a badge
      One Month Later
    • One Month Later
      Kevin Jones earned a badge
      One Month Later
    • Week One Done
      Kevin Jones earned a badge
      Week One Done
    • One Month Later
      henryj earned a badge
      One Month Later
  • Popular Contributors

    1. 1
      +primortal
      533
    2. 2
      ATLien_0
      265
    3. 3
      +Edouard
      198
    4. 4
      +FloatingFatMan
      188
    5. 5
      snowy owl
      138
  • Tell a friend

    Love Neowin? Tell a friend!