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 guess they never learned from the U2 album fiasco
    • Ocenaudio 3.15.1 by Razvan Serea  Ocenaudio is a full featured, fast and easy to use audio and music editor. It is the ideal software for people who need to edit and analyze audio files without complications. Ocenaudio also has powerful features that will please more advanced users. To assist ocenaudio development, a powerful toolset of audio editing, analysis and manipulation called Ocen Framework was created. ocenaudio is also based on Qt framework, a well known library for cross-platform development. Cross-platform support ocenaudio is available for all major operating systems: Microsoft Windows, Mac OS X and Linux. Native applications are generated for each platform from a common source, in order to achieve excelent performance and seamless integration with the operating system. All versions of ocenaudio have a uniform set of features and the same graphical interface, so the skills you learn in one platform can be used in the others. VST plugins support Ocenaudio supports VST (Virtual Studio Technology) plugins, giving its users access to numerous effects. Like the native effects, VST effects can use real-time preview to aide configuration. Real-time preview of effects Applying effects such as EQ, gain and filtering is an important part of audio editing. However, it is very tricky to get the desired result by adjusting the controls configuration alone: you must listen the processed audio. To ease the configuration of audio effects, ocenaudio has a real time preview feature: you hear the processed signal while adjusting the controls. The effect configuration window also includes a miniature view of the selected audio signal. You can navigate on this miniature view in the same way as you do on the main interface, selecting parts that interest you and listening to the effect result in real time. Multiselection for delicate editions To speed up complex audio files editing, ocenaudio includes multi-selection. With this amazing tool, you can simultaneously select different portions of an audio file and listen, edit or even apply an effect to them. For example, if you want to normalize only the excerpts of an interview where the interviewee is talking, just select them and apply the effect. Eficient edition of large files With ocenaudio, there is no limit to the length or the quantity of the audio files you can edit. Using an advanced memory management system, the application keeps your files open without wasting any of your computer's memory. Even in files several hours long, common editing operations such as copy, cut or paste happen almost instantly. Fully featured spectrogram Besides offering an incredible waveform view of your audio files, ocenaudio has a powerful and complete spectrogram view. In this view, you can analyze the spectral content of your audio signal with maximum clarity. Advanced users will be surprised to find that the spectrogram settings are applied in real time. The display is updated immediately when altering features such as the number of frequency bands, window type and size and dynamic range of the display. Ocenaudio 3.15.1 changelog: Add scale type selector/setter Add support to text metadata in CAF files Add volume control for recording Fix issue with saving metadata Fix OGG/VORBIS Modes Selection Other bug fixes and improvements Download: Ocenaudio 64-bit | Portable | ~40.0 MB (Freeware) Download: Ocenaudio for Linux and Mac OS View: Ocenaudio Homepage | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • Malwarebytes 5.3.3.198 by Razvan Serea Malwarebytes is a high performance anti-malware application that thoroughly removes even the most advanced malware and spyware. Malwarebytes version 5.xx brings comprehensive protection against today’s threat landscape so that you can finally replace your traditional antivirus. You can finally replace your traditional antivirus, thanks to a innovative and layered approach to prevent malware infections using a healthy combination of proactive and signature-less technologies. While signatures are still effective against threats like potentially unwanted programs, the majority of malware detection events already come from signature-less technologies like Malwarebytes Anti-Exploit and Malwarebytes Anti-Ransomware; that trend will only continue to grow. For many of you, this is something you already know, since over 50% of the users already run Malwarebytes as their sole security software, without any third-party antivirus. What's new in Malwarebytes 5.xx: Unified user experience - For the first time, Malwarebytes now provides a consistent experience across all of our desktop and mobile products courtesy of an all new and reimagined user experience powered by a faster and more responsive UI all managed through an intuitive dashboard. Modern security and privacy integrations - Antivirus and ultra-fast VPN come together seamlessly in one easy-to-use solution. Whether you’re looking for a next-gen VPN to secure your online activity, or harnessing the power of Browser Guard to block ad trackers and scam sites, taking charge of your privacy is simple. Trusted Advisor - Empowers you with real-time insights, easy-to-read protection score and expert guidance that puts you in control over your security and privacy. Malwarebytes 5.3.3.198 component package 135.0.5296 is now available: You can get the update right away by clicking "Check for updates" in Settings > General. Or, it will be automatically installed if you have automatic updates enabled. Features and improvements Brand new Malwarebytes-managed VPN backend (see details here) Restructured Notifications Settings page and introduced more granular controls Slightly revisited onboarding experience for new installations Refined Dashboard layout for free and licensed users Improved SSO activation flow for a smoother login experience Aligned Dashboard and Tray Menu behavior for VPN connect/disconnect actions Enhanced proxy settings validation to prevent misconfigurations Issues fixed Fixed an issue where Exploit Protection failed to load on some Windows 7 and Windows 8 machines Resolved a crash when using the new Tray Menu with Tamper Protection enabled Other minor usability improvements across the interface Download: Malwarebytes 5.3.3.198 | 391.0 MB (Free, paid upgrade available) Links: Malwarebytes Website | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • Apple faces backlash for promoting F1 movie via Wallet app notification by David Uzondu iOS users right now iPhone users have taken to the internet to voice their anger over an unsolicited ad for Apple's upcoming F1 movie. The promotion popped up as a push notification directly from a place some may consider off-limits for marketing: the Wallet app. The notification offered a $10 discount on two tickets when bought through Fandango using Apple Pay. Apple has poured a lot of money into this film, so it is no wonder Apple is marketing it so aggressively. The movie, which stars Brad Pitt, reportedly cost more than $200 million to produce and was filmed using some innovative techniques. Apple created custom cameras using parts from the iPhone 15 Pro to capture in-car footage and even embedded its fictional APXGP team into actual F1 race weekends. The company also pushed a unique "haptic trailer" and featured the movie at WWDC. The marketing has been everywhere. The main complaint from users online is that a device that costs "over $1000" should not serve ads in its core, first-party applications. For those users, the discount did not matter; the problem was the principle of getting ads in a utility app on a premium phone. People mostly rely on Wallet for financial transactions and passes, not for movie coupons. This kind of push notification also seems to fly in the face of Apple's own developer guidelines, which state that push notifications should not be used for direct marketing unless users have explicitly opted in. Currently, there is no simple way to opt out. That might be changing, however. A new toggle to disable promotions was spotted in the Wallet app in a beta for iOS 26, with TechCrunch suggesting that Apple plans to push more marketing messages in the future.
    • Tor Browser 14.5.4 by Razvan Serea Protect your privacy. Defend yourself against network surveillance and traffic analysis. Tor is a network of virtual tunnels that allows people and groups to improve their privacy and security on the Internet. The Tor software protects you by bouncing your communications around a distributed network of relays run by volunteers all around the world: it prevents somebody from watching your Internet connection and learning what sites you visit, it prevents the sites you visit from learning your physical location, and it lets you access sites which are blocked. The Tor Browser Bundle lets you use Tor on Windows, Mac OS X, or Linux without needing to install any software. It can run off a USB flash drive, comes with a pre-configured web browser to protect your anonymity, and is self-contained. Tor Browser 14.5.4 changelog: All Platforms Updated NoScript to 13.0.8 Bug 43783: Tighten up the SecurityLevel module to enforce new UX flow [tor-browser] Bug 43784: Get confirmation from NoScript that settings are applied [tor-browser] Bug 43885: Rebase stable onto 128.12.0esr [tor-browser] Bug 43911: Backport security fixes from Firefox 140 [tor-browser] Windows + macOS + Linux Updated Firefox to 128.12.0esr Bug 43782: Add new UX flow for changing security level (Desktop) [tor-browser] Android Updated GeckoView to 128.12.0esr Bug 43786: Add new UX flow for changing security level (Android) [tor-browser] Build System / All Platforms Bug 41477: Update keyring/boklm.gpg for new subkeys (2025) [tor-browser-build] Bug 41498: Update keyring/morgan.gpg with updated public key [tor-browser-build] Windows + Linux + Android Updated Go to 1.23.10 Download: Tor Browser (64-bit) | 106.0 MB (Open Source) Download: Tor Browser (32-bit) View: Tor Browser Website | Other Operating Systems Get alerted to all of our Software updates on Twitter at @NeowinSoftware
  • Recent Achievements

    • Week One Done
      DrRonSr earned a badge
      Week One Done
    • Week One Done
      Sharon dixon earned a badge
      Week One Done
    • Dedicated
      Parallax Abstraction earned a badge
      Dedicated
    • First Post
      956400 earned a badge
      First Post
    • Week One Done
      davidfegan earned a badge
      Week One Done
  • Popular Contributors

    1. 1
      +primortal
      618
    2. 2
      ATLien_0
      228
    3. 3
      +FloatingFatMan
      169
    4. 4
      Michael Scrip
      166
    5. 5
      Som
      146
  • Tell a friend

    Love Neowin? Tell a friend!