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 think they mean a phone like the s6 edge where it breaks on first drop guarantee
    • This high-end GEEKOM Mini IT12 (2025 Edition) PC has been slashed by $200 by Steven Parker GEEKOM reached out to let us know of a discount it is running on its site in the U.S., where you can save $200 off the i7 model of Mini IT12 2025 Edition. That brings the already discounted price of $699 down to just $499; buying link below. Below are the full specifications of the variant on offer GEEKOM Mini IT12 (2025 Edition) Dimensions Size 117 x 112 x 45.6mm Weight 652g CPU Intel Core i7-1280P (14 Cores, 20 Threads, 24MB Cache, up to 4.80 GHz) Graphics Intel® Iris® Xe Graphics Memory 32 GB Dual-channel DDR4-3200 SODIMM; expandable up to 64GB Storage 1 TB x M.2 2280 PCIe Gen 4 x4 SSD, expandable up to 2TB 1 x M.2 2242 SATA SSD slot, expandable up to 1TB Operating System Windows 11 Pro Bluetooth Bluetooth® v5.2 Ethernet Intel® 10/100/1000/2500 Mbps RJ45 Ethernet Wireless LAN Intel® Wi-Fi 6E AX211 Kensington Lock Yes Adapter 19V power adapter, 90W, with geo-specific AC cord (IEC C5) I/O Ports 3 x USB 3.2 Gen 2 ports 1 x USB 2.0 port 2 x USB4 ports 1 x SD card reader 1 x 3.5 mm headphone jack 1 x 2.5GbE LAN port 2 x HDMI 2.0 ports 1 x DC jack 1 x Power button MSRP $699 (see below for discount price) You may remember that we reviewed the i7-1260P variant in 2023. Here are our initial impressions of the Mini IT12 at the time. Once you have the PC out of the cushioning inside the box and the foam removed, you are greeted with a Thank You envelope. Below that, after removing the cardboard "shelf," you can find the other components, such as the power lead, HDMI cable, VESA mount plate with a bag of screws, and the instruction manual. What’s In The Box 1 x Mini IT12 Mini PC 1 x VESA Mount 1 x Power Adapter 1 x HDMI Cable 1 x User Guide 1 x Thank You Card As you can see, one HDMI cable is included in the box. Since the port is not HDMI 2.1, you will need to consider purchasing a mini DisplayPort cable or a USB4 (Type-C) to DisplayPort cable to maximize the potential of the Iris Xe Graphics display options. In addition, GEEKOM offers a one-year full warranty on its products, and if needed, you can RMA or return them locally relative to your region (the U.S. has a U.S. warehouse, and the E.U. has a Germany warehouse). Buy the i7-1280P Mini IT12 (2025 Edition) for $499 (was $699) at GEEKOM U.S. Buy the i7-1280P Mini IT12 (2025 Edition) for $499 (was $699) at Amazon U.S. When checking out, use the $30 in-page coupon or NEOIT122025 coupon code. Best of all, the shipping is quick and free.
    • That's ######ing hilarious! And it sure works when you look at both of their faces.
    • When it comes to games specifically, sure, but until now the main focus has been on doing work. All you have to do is look at how hard they're pushing AI in the productivity space to see that they've got their enterprise users in mind 1st with gamers lower on the list. Now that should all change, at least for custom gaming devices like handhelds and even, I expect, custom mini-PCs that are like consoles you can put under your TV. The whole "Xbox PC" branding they had around the show says a lot IMO.
    • I'm excited to check this out. I never played the first version, but I did just finish playing through Smalland, and while I liked it, I found myself wishing for more engaging content.
  • Recent Achievements

    • Enthusiast
      the420kid went up a rank
      Enthusiast
    • Conversation Starter
      NeoToad777 earned a badge
      Conversation Starter
    • Week One Done
      VicByrd earned a badge
      Week One Done
    • Reacting Well
      NeoToad777 earned a badge
      Reacting Well
    • Reacting Well
      eric79XXL earned a badge
      Reacting Well
  • Popular Contributors

    1. 1
      +primortal
      470
    2. 2
      +FloatingFatMan
      283
    3. 3
      ATLien_0
      251
    4. 4
      snowy owl
      202
    5. 5
      Edouard
      197
  • Tell a friend

    Love Neowin? Tell a friend!