Cron Job

[QbertsBrother]

i have a server that i am trying to set up a cron job on.

i dont know if it is possible. what i need to do is load an XML file into a database.

i have an XML file that i need to load into a database everyday and i dont want to do it manually.

if anyone has any info on how to do this it would be great.

thanks

[bigtomrodney]

Do you have a method for loading the xml file already or are you looking to solve that part too?

[QbertsBrother]

i am doing the following to load the data.

i have a test server that mirrors my live server.

i load the XML file into the MySQL database using Navicat software. then i use the Navicat data transfer tool to transfer the data from my test server to my live server.

i dont want to do that everyday anymore so i am looking for a way to automate it.

i do also have the option of getting a csv instead of XML

thanks

[bigtomrodney]

I work with stuff like this all day (though not in Linux/MySQL unfortunately for both of us). I'd say take csv if you can, it's more straightforward to deal with and results in smaller files.

Have a look at this article for some of the syntax to load into MySQL. Once you're happy this works setting up a cronjob will be the easy part

Maybe something like this in your crontab for a file loaded at 11.30pm every night, Monday to Friday.

30 23 * * 1-5  /usr/bin/mysql -h hostname -u username -ppassword -e "LOAD DATA LOCAL INFILE '/path/to/file.csv' INTO TABLE destination_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1, filed2, field3);"

Note if the source file is coming from a Windows source the line termination would be '\r\n'

[QbertsBrother]

awesome

that looks like exactly what i need.

i will look into that article you mentioned and give it a try.

thanks

[QbertsBrother]

i am working with a 3rd party on this project and they are going to upload a zip file everyday that is titled in this format YYMMDD.zip.

is there a way that i can unzip the file and load the csv file that is in the zip?

i am not sure what the csv file is named. i am guessing that it is also named YYMMDD.csv or it could be YYMMDD.txt.

thanks

[masinick]

On Linux systems there is a command called unzip. If the format of the file is definitely zip then use that program to unzip the contents. As an alternative you may also be able to use either GNU tar or one of its derivative commands. In particular, I had the gunzip command in mind. Use apropos unzip to find documentation that relates to unzipping files, then try reading the documentation using the man command on unzip, gunzip, and whatever else pops up. It should take only a few minutes and it may help you to see what is out there so you can make the best choices.

As far as cron, there are similar documents available for cron, crontab, and so forth. You will most certainly want to learn them in order to understand the breadth of the capabilities that are available. There are commercial job management systems available too. But unless you want to spend money and have to learn yet another thing, learning about the Linux gzip family for extracting archive data and learning about cron and crontab, reading and then asking questions, that is the best way to go. You will understand things better and you will be confident supporting it.

Not all of the documentation is easily understandable to someone unfamiliar with it, but it is accurate, and it gives you solid information once you grasp how to use it. If you do some reading, that ought to help you out so you can ask more specific questions.

I hope that is useful. Nothing quite like digging in with reading and research. I would be glad to help you interpret that reading and research.

[QbertsBrother]

thanks

i am going to get my first file that i would need to load on monday so i will know more then.

i am pretty confident that i can figure out what to do by doing what you mentioned above. reading and research.

thanks for your help. i am sure that i will post more questions as i get into it.

[masinick]

thanks

i am going to get my first file that i would need to load on monday so i will know more then.

i am pretty confident that i can figure out what to do by doing what you mentioned above. reading and research.

thanks for your help. i am sure that i will post more questions as i get into it.

Definitely, do ask. All I ask in return is that you do a bit of up front reading and preparation. That way you can ask good questions and learn something in the process. But if the documentation is confusing or you are not quite sure of some subtle point, don't be afraid to ask. Or perhaps you have already been hunting, but because some command has an unusual name, you can't find it, ask anyway. I'm not going to say no, even if you don't read up on something, but I may suggest that you read this or that document, then come back with more questions. Fair enough?

Always glad to help, but my goal is also to educate people, so they in turn may one day educate someone else. That is how we build communities in software. Good luck with this project!