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

    • Maybe, just maybe... and it isn't you... there are some people who like the Windows 11 UI (for whatever reason) and want a better backend.
    • Gundams to arrive in Call of Duty: Mobile with new mech mode and unique third-person combat by Paul Hill Activision has announced that Call of Duty: Mobile will see the launch of Season 6 “Gundams Arrive” on July 2 at 5PM Pacific Time. This major collaboration between the world’s most popular FPS franchise and the Gundam franchise will introduce a new, limited-time mode called Gundam Team Deathmatch where 8 players will face off 4v4 and pilot Gundam-themed operators such as Ethan - Freedom Gundam, Reaper - Sazabi Gundam, Proton - v Gundamo, Deathscythe Gundam (EW). When playing in this game mode, players will notice a switch from the usual first-person view to the third-person perspective. The new game mode will also feature specific abilities and weapons that are unique to Gundam suits rather than player loadouts. The new Gundam Team Deathmatch mode will be played on a new map called interstellar space station, which has been designed for this mode. When playing, you’ll discover that the mech suits offer specialized mobility such as dodge, sprint, and vertical jets. In the post-match, players will be able to watch Gundam operator animations with unlockable rewards for viewing. You can unlock more animations by participating in Gundam Team Deathmatch, normal Multiplayer, and Battle Royale modes. There will also be Gundam-themed in-game events, such as Survival of the Fittest, which will give players free rewards like the new legendary weapon J358 — Fin Funnel v Gundam, Urban Tracker — Defense Force, Cyro Bomb — Haro (reskin), Emote — Haro Team, new camos, and more. Players will also be able to obtain a variety of items through Season 6 Battle Pass free and premium tiers, including sci-fi-themed Operators and Weapon Blueprints. Players on the free tier will get access to the bolt-action 3-Line Rifle based on a World War II design and is capable of inflicting high damage with high accuracy. Free tier players will also have the chance to earn other rewards such as Skins, Weapons Blueprints, Vault Coins, and more. Players looking to spend money can get the Premium Pass. These players will have a chance to get all of the content from Season 6 including tactical warriors like Silver — Chrome Dome Reskin, Misty — Science Pilot, Atlas — Dust Ranger, and The Marshal — Rock Hound; and Weapon Blueprints like the BP50 — Pathripper, Oden — Maevwat Technical, PDW-57 — Rocket Re-Entry, BY15 — Dark Moon, and the 3-Line Rifle — Geo Thermal Line, based on the new Season 6 weapon. There’s also Battle Pass Subscription which gives you additional monthly rewards along with a 10% boost to Player and Weapon XP, discount coupons, and limited discounts on 10x crate pulls. Activision also stated that Mythic Drops are returning to the Mythic store and that Battle Pass Vault is getting Season 9 — Zombies Are Back (2022) and Season 6 — Templar's Oath (2023).
    • I managed to buy the original Hellblade: Senua’s Sacrifice through the ps app on my phone even though it was not showing up in the ps store when browsing from my ps5 it was buggy but more or less O.K.ish to play… I wonder if this original added version is funny fixed up for ps5 play
    • Download old Windows Startup Sounds @ https://www.winhistory.de/more/winstart/winstart_en.htm
  • Recent Achievements

    • Week One Done
      pcdoctorsnet earned a badge
      Week One Done
    • Rising Star
      Phillip0web went up a rank
      Rising Star
    • One Month Later
      Epaminombas earned a badge
      One Month Later
    • One Year In
      Bert Fershner earned a badge
      One Year In
    • Reacting Well
      ChrisOdinUK earned a badge
      Reacting Well
  • Popular Contributors

    1. 1
      +primortal
      549
    2. 2
      ATLien_0
      205
    3. 3
      +FloatingFatMan
      171
    4. 4
      Michael Scrip
      151
    5. 5
      Som
      131
  • Tell a friend

    Love Neowin? Tell a friend!