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

    • It has the same switch to allow whitelisting or not as the regular AdBlock extension.
    • These 20 crypto phishing applications are scamming Play Store users by Hamid Ganji Google Play Store is the main venue for Android users to download applications. While Google has strict rules and policies for verifying apps, some malicious apps somehow slip through anyway. Meanwhile, when it comes to crypto wallet apps, both Google app auditors and Play Store users need to be even more cautious. Cyble Research and Intelligence Labs has identified at least 20 crypto phishing applications on the Google Play Store that impersonate legitimate and popular crypto wallet apps and try to steal users' crypto credentials. By impersonation, these malicious apps trick users into downloading them and then start to capture the user's actual login data. "What makes this campaign particularly dangerous is the use of seemingly legitimate applications, hosted under previously benign or compromised developer accounts, combined with a large-scale phishing infrastructure linked to over 50 domains. This extends the campaign's reach and lowers the likelihood of immediate detection by traditional defenses." Cyble writes. Some of these malicious apps have the same name but come with a different package name. After removing duplicate names, here's the list of 9 newly discovered crypto phishing applications on the Play Store: Pancake Swap Suite Wallet Hyperliquid Raydium BullX Crypto OpenOcean Exchange Meteora Exchange SushiSwap Harvest Finance Blog According to Cyble, these apps prompt users to enter their 12-word mnemonic phrase to access the fake crypto wallet. Also, scammers use accounts that were previously used to distribute legitimate apps to minimize the risk of getting caught by Google. These accounts are more likely to be compromised and then taken over by scammers. If you've downloaded any of these fake crypto wallet apps from the Play Store, make sure to delete them as soon as possible. In 2024, revenue from crypto scams was estimated to be around $9.9 billion. This billion-dollar crypto scam business is expected to grow massively in 2025 thanks to AI.
    • Again...just because Microsoft never managed to be as successful as Google in that business that doesn't mean they are not exactly what Google is. An online advertising company. Edge's built in ad block in case you don't know whitelists their own advertising platform. According to Microsoft... Google ads and tracking bad, Microsoft ads and tracking good:)
  • Recent Achievements

    • Reacting Well
      viraltui earned a badge
      Reacting Well
    • Week One Done
      LunaFerret earned a badge
      Week One Done
    • Week One Done
      Ricky Chan earned a badge
      Week One Done
    • Week One Done
      maimutza earned a badge
      Week One Done
    • Week One Done
      abortretryfail earned a badge
      Week One Done
  • Popular Contributors

    1. 1
      +primortal
      483
    2. 2
      +FloatingFatMan
      264
    3. 3
      snowy owl
      239
    4. 4
      ATLien_0
      229
    5. 5
      Edouard
      179
  • Tell a friend

    Love Neowin? Tell a friend!