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

    • How to change folder colors in OneDrive by Taras Buria Microsoft's OneDrive cloud storage has plenty of useful productivity features that help you get around your file libraries. Colored folders are a relatively new feature that not every OneDrive user is aware of. While the ability to have colorful folders might seem minor at first glance, it can be very helpful for sorting stuff in your OneDrive. Microsoft says the feature lets users "personalize and manage folder colors for better content visibility." Even Apple agrees with this—iOS, iPadOS, and macOS 26 let you change folder colors in the Files/Finder app. Change folder color in OneDrive on Windows The process is very simple, but it is not the same as changing a folder icon in File Explorer. While you can change the icon of any folder in OneDrive on Windows 11 (Right Click > Properties > Customize > Change Icon), these icons do not sync across platforms. At the same time, while OneDrive folder colors do sync, the feature is not available for personal accounts in the web version of OneDrive. As for mobile devices, it is coming soon to Android and iOS OneDrive clients. Despite the current limitations, here is how to change folder colors in OneDrive on Windows: Make sure OneDrive is set up and running and sync is not paused. Find the folder whose color you want to change, and right-click it. Select OneDrive > Folder Color. Pick one of the 16 colors available and click Ok. The OneDrive app will sync your changes so that they appear across all devices that support folder colors in OneDrive. If you have a business account, you can open OneDrive for the web, right-click any folder and select Folder Color. Changes will sync across your devices right away. Do you find colored folders in OneDrive useful? Share your thoughts in the comments.
    • Awesome. That's what I was thinking Jim. Yea from what I can tell you connect the AIC card to the AIC header on the motherboard, so it doesn't just run through the PCI lanes. That's fine guys. Thanks for clarifying 🙂 I was getting a little bit lost with all of this, but I need a minimum of TB3 and the connector on the MB has it, but not on the back of my PC chassis / MB IO shield. Anyway I found that the cheapest I can get it is Amazon at 70 quid, so I'm just gonna have to fork out for it. Thanks everyone. 🙂 👍  Don't worry about Mindovermaster. He's just his chirpy cheerful self lol. Thanks everyone 🙂
    • I wonder why you say that. As we speak, I'm using it to slash off several minutes of my life.
    • I think you may need to adjust your style of approach. I know you won't though. While some were affected by performance issues, and it's not a huge gap... you're acting as if Ryzen couldn't handle 11 at all. Performance issues are purely based on some facts in certain scenarios, while others are not. I see one link with a handful of people discussing the topic. I didn't join those topics or seek them out myself, as I didn't encounter noticeable drops in performance going from 10 to 11. When 10 came out, during that beta testing phase... I was able to continually crash my system simply by renaming files. It might also have to be because I don't have my nose stuck up the butt of single digit percentage points. I don't benchmark my PC every time something new comes out. Single percentage point differences in performance only ruffle the feathers of those that don't care about daily use. If you have a race car, do you compare that to your daily driver? Do you expect your Honda Accord to break the 9 second quarter mile like your 1000HP Pontiac Firebird? If you're so worried about FPS instead of enjoying your games... perhaps opening a curtain in your basement might provide a new perspective in life.
    • Currently updating my Win10 IoT Enterprise LTSC 2021 in a VM (QEMU/KVM) on Linux. but damn, updates take forever (makes me appreciate the lightness on Linux all the more). to give you a general idea... this update finished at 37 minutes into system uptime and I would estimate updates have been running roughly 20-30 minutes (some of this would be download time, but even subtracting that I would guess that 20-30min is close). granted, I only got two cores of my four core CPU (i5-3550) dedicated to the VM. but still, Linux wipes the floor with Windows in this regard. plus, that does not count the reboot which takes even more time.
  • Recent Achievements

    • Reacting Well
      rshit earned a badge
      Reacting Well
    • Reacting Well
      Alan- earned a badge
      Reacting Well
    • 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
  • Popular Contributors

    1. 1
      +primortal
      535
    2. 2
      ATLien_0
      269
    3. 3
      +FloatingFatMan
      211
    4. 4
      +Edouard
      204
    5. 5
      snowy owl
      140
  • Tell a friend

    Love Neowin? Tell a friend!