Please send me a relevant project you have completed with the bids.. thank you. You can find each detail below:
Project is shortly finding, reading and transferring data from remote excel file available publicly in FTP. This excel file is stored in a public FTP and updated daily and the data cell sturcture of the excel file is always same. All excel files are systematicly updated and same.
REQUIRED STEPS FOR SOFTWARE:
1- Search for daily updated excel file in a public ftp.
2- Transfer excel file to our server.
3- Read data from excel file (for example: 15 variable cells needs to be read) from the table inside the excel file.
4- Store the data from exel files to mysql database.
DETAILS OF THE STEPS:
1- FIND THE EXCEL FİLE: There are excel files in a public remote ftp. These files are updated daily in weekdays. Software will search the public FTP for the updated excel file. Updated are data are stored into new excel files each day. The name of the excel files follows a systematic date/month/year pattern according to the day's date. The files are only updated in weekdays, therefore, the software should only do the search in the weekdays. For the weekend files, software should search these in every Mondays. The daily excel files of the weekends are updated in Mondays again with the relevenant day files seperately.
Instead of searching every day, software should do some logic for weekend files. There is an additional thing on this, the public days. Public days data are also not being updated in public days and these also updated in the following workday. Therefore, software should have more logic on searching daily files.
I think that to solve this software can search excel files of last 10 days every time (some public holidays takes longer) and should check If that files already transferred or not to our server that way I think we can make sure that we got every file.
As defined above, the files are updated daily. There is no spesific time for the update, It could be 9:00 in the monring or 14:00 in the afternoon in workhours. Therefore, the software should constantly search for the last missing daily excel file in every 5 minutes. I guess this can be achived by a cron job.
2-TRANSFER EXCEL FILE Excel file will be transferred to our server ftp.
3- READ DATA FROM CELLS: The data inside the excel file is needed. The tables in the excel files are always updated into same cells, table is always same cell structure, only the variables inside the data tables are updated for each day seperately.
There is only one issue with the data cells inside the excel file: Data table inside the sheet is always same but sometimes the data table itself moves one cell right or left. This happens very rarely but happens and which could prevent reading the data completely. I mean software can't be programmed to read from cell B3 for example.
I guess this can be solved by finding the first top left cell and by checking If there is a data considering rest of the cells has no data. Thay way, the table will be determined correctly because table structure is always same pattern.
4- STORE TO MYSQL: We need this data stored in mysql database. As you can understand, there are variables for each days which means there is times series. Therefore, we need to store the data with day unique id's and variable names on the columns.
I will inform additionaly regarding the table structure.
5- CREATE A JS CHART: Now, we have the data in our server. We need to see trends by line charts for example in this time series data. This is the last ste, therefore, I will explain what we need after we complete the previous 4 steps.
Please find attached the exce data table which needs to be read.
As you can understand, most importing thing in this project is reading excel files timely and storing this to mysql correctly as a datatable.
21 freelancers are bidding on average $648 for this job
I currently do not have a ready sample for your review. however I have extensive knowledge in PHP and MySQL to complete this job to your specifications.