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

    • Vivetool also has a GUI. Literally took me three clicks to enable this from there.
    • Microsoft Weekly: OneDrive horror stories, ramblings about Start menu, and more by Taras Buria This week's news recap is here, delivering you a roundup of the most important Microsoft stories, including a bunch of odd stuff and bugs in Windows, OneDrive horror stories, ramblings about the Start menu, a couple of new Windows 11 preview builds, important news from AMD, and a lot more. Quick links: Windows 10 and 11 Windows Insider Program Updates are available Reviews are in Gaming news Windows 11 and Windows 10 Here, we talk about everything happening around Microsoft's latest operating system in the Stable channel and preview builds: new features, removed features, controversies, bugs, interesting findings, and more. And, of course, you may find a word or two about older versions. Microsoft released a new out-of-band update to fix boot issues on certain Surface devices. The company announced certain Windows 365 updates, such as VBS and HVCI support (by default) and app provisioning in Windows 365 instead of entire cloud PCs. Microsoft is also removing legacy drivers from Windows Update in a new "strategic move." This is the default Windows 365 wallpaper On the negative side, we have the latest Patch Tuesday updates breaking the DHCP Server in all Windows Server editions. Also, there is some bad news for PC users with Windows Hello cameras: after the April 2025 Patch Tuesday updates, Windows Hello does not work in the dark. The change was quietly introduced to address security issues. Windows Goodbye That is not all, though. As it turned out, solid-state drives from WD could still block your computer from installing Windows 11 version 24H2, which was released in October 2024. Also, Microsoft's Family Safety feature is now blocking Chrome, for some reason. Here is an editorial from Usama Jawad (welcome back) about how, four years after the initial release, Windows 11 still does not offer strong enough reasons to upgrade from the outgoing Windows 10. Also, Usama shared his thoughts about the Start menu and why he had stopped caring about its changes altogether. Speaking of the Start menu, check out our overview of what users wanted from Microsoft and what the company delivered in the redesigned Start menu, which was recently announced. Windows Insider Program Here is what Microsoft released for Windows Insiders this week: Builds Canary Channel Build 27881 This week's Canary build introduced context menu improvements, new accessibility features, Settings app tweaks, and more. Dev Channel Nothing in the Dev Channel this week Beta Channel Nothing in the Beta Channel this week Release Preview Channel Build 26100.4482 (KB5060829) This build improves File Explorer and search performance, adds some changes to default browser settings, and fixes multiple bugs. Some hidden stuff in the recent Windows 11 preview builds includes a new adaptive battery saver. This feature dynamically adapts battery saver mode according to your workflow, but in its current form, it is not fully operational. Even though Microsoft acknowledged its existence, the adaptive part still needs improvements. Another useful change in the recent builds is the return of a clock in the notification center. This time, however, Microsoft makes it more customizable, and you can toggle it on or off. Also, the company is moving more Control Panel bits to the Settings app and adding a rather unexpected customization feature that will let you select where system indicators (flyouts and sliders) appear on the screen. Microsoft started rolling out a new update for the Snipping Tool app. The latest release lets you save screen recordings as GIFs. Shortly after that, we posted a guide with a bit more detail about the feature. Updates are available This section covers software, firmware, and other notable updates (released and coming soon) from Microsoft and third parties, delivering new features, security fixes, improvements, patches, and more. Microsoft is working on a Dashboard redesign for PowerToys. Developers published an early look at what is coming to the app in future updates, revealing a better-organized page with quick links, a shortcut overview, and a list of available modules. This week's Office updates are rather mixed. OneDrive, for one, is having problems finding files. Microsoft acknowledged the issue, which affects users on Windows, iOS, Android, and the web. Unfortunately, that is not the only negative story about OneDrive. A new report from a frustrated user revealed a scary tale of Microsoft locking them out of an account full of invaluable content. Outlook also has some issues, this time, with opening emails, and Microsoft 365 will soon disable outdated protocols for file access. Finally, Copilot in Excel received a major update for context awareness, which made the assistant more useful when answering questions about data. This week's browser updates include several releases. Firefox announced a new method for pinning and unpinning tabs. It is now available for testing in the Nightly channel. Microsoft Edge was updated with fixes for two security vulnerabilities (high severity) originating from Chromium. Finally, here is this week's Microsoft 365 Roadmap with an overview of all the new stuff that Microsoft added to the website. Here are other updates and releases you may find interesting: Microsoft 365 security in the spotlight after Washington Post hack. Microsoft expands European sovereign cloud offerings with new data and key controls. Microsoft Defender XDR received TITAN-powered Security Copilot recommendations. Microsoft reportedly plans more layoffs. Watchdog found Microsoft guilty of confusing advertising when it comes to Copilot. Here are the latest drivers and firmware updates released this week: AMD released a new chipset driver for Ryzen processors under version 7.06.02.123, which followed a security update for TPM-Pluton. Nvidia 576.80 WHQL with fixes for the RTX 5090 FE, new game support, and a long list of fixes. You can get some extra performance on certain AMD Ryzen chips with a simple system tweak. Surface Pro 11 and Surface Laptop 7 received big firmware updates with multiple fixes and improvements. Reviews are in Here is the hardware and software we reviewed this week This week, Steven Parker reviewed the TerraMaster D4 SSD, a palm-sized DAS with up to 32TB of storage that you can connect over USB4. This thing is rather impressive, and for a modest price tag, it delivers a tiny footprint, great looks, full RAID support in TOS 6, quick connection, and more. On the gaming side Learn about upcoming game releases, Xbox rumors, new hardware, software updates, freebies, deals, discounts, and more. AMD and Microsoft announced some big news this week. The two companies revealed a new multi-year partnership, which secures AMD as the future maker of chips for Xbox consoles and other hardware. Sarah Bond announced the partnership in a new video on the official Xbox media channels. Turn 10 Studios announced a new Forza Motorsport update. Update 21 brings IndyCar content, Career mode expansion, Featured Tours, new reward cars, and more. It is now available on Xbox and PC via the Microsoft Store and Steam. Minecraft is another Microsoft-owned game that received a big update this week. The long-anticipated graphics overhaul is finally here with directional lighting, volumetric fog, improved shadows, reflections, godrays, and a lot more. In addition, Mojang released Chase the Skies, the latest content drop, which adds happy ghasts, new music disks, a locator bar for players, environmental fog in the overworld, new background music, and all sorts of small gameplay changes. Microsoft announced new games for Game Pass. The latest additions include FBC: Firebreak, Crash Bandicoot 4: It's About Time, Start Trucker, Wildfrost, Rematch, Call of Duty: WWII, Rise of the Tomb Raider, and more. As usual, some games are leaving the subscription. Valve released a big update for the Steam overlay. The latest version introduced major upgrades to CPU and VRAM usage, temperatures, and other important metrics that you might want to track when playing games on your gaming rig. Deals and freebies Also, be sure to check out this week's Weekend PC Game Deals article, which features rhythm bundles, fishing festivals, DRM-free summer sales, and more. Other gaming news includes the following: Take-Two confirmed Borderlands 4 will not cost $80 for the base game. The Coalition expanded the Gears of War: Reloaded beta after its rocky start. Ara: History Untold 1.4 update delivered overhauls to AI, map generation, combat, and more. Star Citizen Alpha 4.2 update lands with radiation hazards, dynamic rain, and more. This link will take you to other issues of the Microsoft Weekly series. You can also support Neowin by registering a free member account or subscribing for extra member benefits, along with an ad-free tier option. Microsoft Weekly image background by steve_a_johnson on Pixabay
    • I'm afraid not, Microsoft does release updated installation images for Windows through MVS every month, but they do not include any update to Defender's components or signatures. That's what the package talked about in the article is for, it includes a PowerShell script for the update.
  • Recent Achievements

    • First Post
      Johnny Mrkvička earned a badge
      First Post
    • Week One Done
      viraltui earned a badge
      Week One Done
    • One Month Later
      serfegyed earned a badge
      One Month Later
    • Dedicated
      firey earned a badge
      Dedicated
    • Dedicated
      fettermanj earned a badge
      Dedicated
  • Popular Contributors

    1. 1
      +primortal
      635
    2. 2
      ATLien_0
      230
    3. 3
      Michael Scrip
      217
    4. 4
      Xenon
      149
    5. 5
      Steven P.
      141
  • Tell a friend

    Love Neowin? Tell a friend!