We are in need of developing a script/service to import several text files into a mysql database every day. The script will be running on an Ubuntu server every day, at 7am. A config file needs to be included with all variables. There are some formatting minor issues that need to be addressed. Samples of the text files and table structures will be provided upon request.
- The script will watch 2 different folders in a remote server accessible via FTP. Normally it will run at 7am, but there shall be a way of invoking it directly for the initial load and/or testing.
- On first folder location, the script will look for a file called [url removed, login to view] (in Sybase BCP format) and will download it over to the local server.
- The file will be transformed to a CSV textfile and renamed as: [url removed, login to view], where yyyymmdd will be the date the file was last modified, i.e. normally today's date.
- Once file transformed it will be imported to the BASE_RATE table located on the mysql database. Prior to the load, the table will be truncated.
- A log table called sysm_replicate will be created on the mysql database and will contain the following fields:
• source_file, i.e. [url removed, login to view],
• target_file, i.e. [url removed, login to view],
• file_imported (yes/no),
• notes, i.e. error msgs, last action performed
- The script will also monitor a second folder for filenames starting with cdr.*.dat, i.e. cdr.20110707.dat. These will be tab-delimited files, named as [url removed, login to view], where yyyymmdd is today's date or the last modified date.
- There will be one new file added to this folder every day. Once a file is detected by the script, it will copied to the server via FTP.
- Each cdr file found on the folder will be added as a record to the sysm_replicate table for further processing, i.e. load, transform & import.
- If nor any reason the contents of the sysm_replicate is to be emptied, the script will automatically re-insert all cdr file records as (found on the folder in remote server), and subsequently load, transform & import the data to the mysql table.
- On initial run, the script will probably detect around 200-300 cdr files but always 1 base_rate file. Then it will receive 1 cdr file per day. However, the base_rate will always be overwritten by a new one.
- The sysm_replicate will have one row per file to be imported. As new rows are added, the script will load, transform and import these files one-by one, and will mark the status as such.
- There shall be a command-line utility to schedule/start such job or stop it at any time.
- All variables including FTPs host address/user login/remote paths/local paths are to be included in a customizable configuration file.
- We will give you ssh / ftp access to the ubuntu server and mysql via console and/or phpmyadmin.
WORKING WITH US:
The above is a design spec, but we are open to ideas and alternative designs. However, we like to get this done ASAP since our client has requested this yesterday. We are busy doing other work and would like for you to be part of our team. If you have the time and energy to work closely with us, and be in constant communication, and provide us with quality work , we are interested in working with you. Don't bother to send us long lists of projects you worked on or websites. Such bids will be ignored.
We expect you to be available at some point for chat via Skype or some form of IM. This will help us answer questions, provide you technical guidance and review your work. We may have more similar projects and will award depending on outcome, performance & quality of this project. We will take a close look at your reviews, if you have any. Please contact me for more details. Once project is accepted by us and our customer, we will release funds. Please send me a message with your budget consideration and a quote.
22 freelancers are bidding on average $202 for this job
I have been working on such things in my day to day job since past 4 years. Should not be much problem.. Will be in touch with you via IM (Skype on Weekends).
Right now i am working on our product's Automation in bash, perl and C. your requirement meets to my day to day work. So, i can provide you much batter solution and get any time support from me.