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

    • Hi guys. Having some trouble with an MSI MAG Tomahawk B760 WiFi DDR5 motherboard. I want Thunderbolt and my motherboard has a TB Header on the base of the board. The USBC on the back is just a USB C port but I really want a Thunderbolt port because of a piece of hardware I've just bought. I'm looking for either a header I can connect direct to the board or a PCI Card I can connect. Does anyone know what I need or where I can get help? Thanks
    • I do not want every single browser to be based on Chromium. The main reason I use Firefox is because it's not based on Chromium.
    • Insert "but Lundluke is xyz" like he is lying. Yes, people on Neowin don't like him. I don't actively follow him either but in that particular one he is correct, he is not lying.
    • That guy is an idiot. Why can't you plainly answer my question?
    • Last chance: Solutions Architect's Handbook, Third Edition (worth $42.99) download by Steven Parker Claim your complimentary eBook worth $42.99 for free, before the offer ends today, June 10. Build a strong foundation in solution architecture and excel in your career with the Solutions Architect’s Handbook. Authored by seasoned AWS technology leaders Saurabh Shrivastav and Neelanjali Srivastav, this book goes beyond traditional certification guides, offering in-depth insights and advanced techniques to meet the specific needs and challenges of solutions architects today. This edition introduces exciting new features that keep you at the forefront of this evolving field. From large language models and generative AI to deep learning innovations, these cutting-edge advancements are shaping the future of technology. Key topics such as cloud-native architecture, data engineering architecture, cloud optimization, mainframe modernization, and building cost-efficient, secure architectures remain essential today. This book covers both emerging and foundational technologies, guiding you through solution architecture design with key principles and providing the knowledge you need to succeed as a Solutions Architect. It also sharpens your soft skills, providing career-accelerating techniques to stay ahead. By the end of this book, you will be able to harness cutting-edge technologies, apply practical insights from real-world scenarios, and enhance your solution architecture skills with the Solutions Architect's Handbook. This free offer expires today, June 10. How to get it Please ensure you read the terms and conditions to claim this offer. Complete and verifiable information is required in order to receive this free offer. If you have previously made use of these free offers, you will not need to re-register. While supplies last! Download Solutions Architect's Handbook, Third Edition (worth $42.99) for free Offered by Packt, view other free resources The below offers are also available for free in exchange for your (work) email: Solutions Architect's Handbook, Third Edition ($42.99 Value) FREE – Expires 6/10 AI and Innovation ($21 Value) FREE – Expires 6/11 Unruly: Fighting Back when Politics, AI, and Law Upend [...] ($18 Value) FREE - Expires 6/17 SQL Essentials For Dummies ($10 Value) FREE – Expires 6/17 Continuous Testing, Quality, Security, and Feedback ($27.99 Value) FREE – Expires 6/18 VideoProc Converter AI v7.5 for FREE (worth $78.90) – Expires 6/18 Macxvideo AI ($39.95 Value) Free for a Limited Time – Expires 6/22 The Ultimate Linux Newbie Guide – Featured Free content Python Notes for Professionals – Featured Free content Learn Linux in 5 Days – Featured Free content Quick Reference Guide for Cybersecurity – Featured Free content We post these because we earn commission on each lead so as not to rely solely on advertising, which many of our readers block. It all helps toward paying staff reporters, servers and hosting costs. Other ways to support Neowin The above deal not doing it for you, but still want to help? Check out the links below. Check out our partner software in the Neowin Store Buy a T-shirt at Neowin's Threadsquad Subscribe to Neowin - for $14 a year, or $28 a year for an ad-free experience Disclosure: An account at Neowin Deals is required to participate in any deals powered by our affiliate, StackCommerce. For a full description of StackCommerce's privacy guidelines, go here. Neowin benefits from shared revenue of each sale made through the branded deals site.
  • 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
      499
    2. 2
      ATLien_0
      267
    3. 3
      +FloatingFatMan
      227
    4. 4
      +Edouard
      199
    5. 5
      snowy owl
      151
  • Tell a friend

    Love Neowin? Tell a friend!