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

    • 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
    • Thanks Nik and thanks Mind. Nik: Just on the off chance, you don't know where I could get this part at cheaper than normal price do you? I tried I few places but the I can find it at is 70GBP? Just gonna have to suck it up and buy the add-on for my PC. Thank you guys.
    • Underwhelming update. Same as 12,13,14,15. Then M3E ain't showing up until Q3
  • 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!