PHP script - read Excel / CSV files into database table
- or -
Post a project like this2925
$$
- Posted:
- Proposals: 7
- Remote
- #1091034
- Awarded
20+ Years of experience | Your Trusted Tech Partner for Critical Websites & Web Apps
Sofia
3515236982165149690460694670311757441186341
Description
Experience Level: Intermediate
Estimated project duration: 1 day or less
General information for the website: private / transactional website
Kind of development: Customization of existing website
Description of requirements/features: Site runs on CakePHP / MySql. Is hosted on a *nix* hosting platform.
GITHUB versioning is in place. a dev and test site with data for testing exist and are separate to the live site.
Need this to run as a PHP script to fit into some other process flows going on.
site is running. need additional enhancements. looking for someone with correct skills to allow me to work on other processes in parallel.
Requirements:
Files are received with information that needs to be written into a specific database table.
Files will be Excel or CSV format. There may be different formats for different files.
The contents of the file lines need to be mapped into a main database table.
4 columns within the file need to be cross referenced / looked up against Masterdata held in the main database tables – using the
A logging table, recording the process and status of the overall files and whether there are individual errors will need to be updated.
[Part 1] – PHP Script to process files from folder
PHP Script to process file (file name / location / sender passed as variables into the program)
There may be different file layouts for different senders so a cross reference table in database will indicate which column numbers in attachment file will match which database columns in table for each file type.
When processing starts a log table will be updated with attachment name, unique id (per attachment run), sending address, date/time received, number of rows in attachment and status (initially unprocessed).
If there are catastrophic errors with the file and it cannot be read (columns missing, etc) status will be set to fatal error, and email send to an admin account with the original attachment included, the attachment moved to an error sub folder and no further processing to take place.
If there are not fatal errors processing will continue. The main database table will be updated with the contents of the attachment, and will be identified by the unique ID.
Two sets of columns need to be cross referenced from data in the file against Masterdata already held in system. Data in file for these 2 columns will be replaced with Masterdata from the existing system (code / name pairs).
If there are errors with specific lines (cross references not found etc) the entire process must be rolled back out, the update log set to error. The attachment will be moved to an error folder. An email will be triggered – send to the administrator – containing the attachment name and unique ID and a description of what was missing / incorrect. The administrator can then correct the issues and resend the file for processing as necessary.
If there are no errors the main table is updated successfully. A check to ensure that the correct number of lines compared to the original attachment, have been created. Once successful checks the status log needs to be updated to successful and the original attachment moved to a successful sub folder.
The file contents are matched to the existing table using some key fields. If those fields with the same value exist in the table already those values are updated. If they do not exist already then a new row is inserted.
CMS and Admin requirements: CakePHP - already used - rest of site already running.
Extra notes: database / file same as attached.
further details can be provided.
confidentiality clause will be required - as this is a transactional site with live business data.
Kind of development: Customization of existing website
Description of requirements/features: Site runs on CakePHP / MySql. Is hosted on a *nix* hosting platform.
GITHUB versioning is in place. a dev and test site with data for testing exist and are separate to the live site.
Need this to run as a PHP script to fit into some other process flows going on.
site is running. need additional enhancements. looking for someone with correct skills to allow me to work on other processes in parallel.
Requirements:
Files are received with information that needs to be written into a specific database table.
Files will be Excel or CSV format. There may be different formats for different files.
The contents of the file lines need to be mapped into a main database table.
4 columns within the file need to be cross referenced / looked up against Masterdata held in the main database tables – using the
A logging table, recording the process and status of the overall files and whether there are individual errors will need to be updated.
[Part 1] – PHP Script to process files from folder
PHP Script to process file (file name / location / sender passed as variables into the program)
There may be different file layouts for different senders so a cross reference table in database will indicate which column numbers in attachment file will match which database columns in table for each file type.
When processing starts a log table will be updated with attachment name, unique id (per attachment run), sending address, date/time received, number of rows in attachment and status (initially unprocessed).
If there are catastrophic errors with the file and it cannot be read (columns missing, etc) status will be set to fatal error, and email send to an admin account with the original attachment included, the attachment moved to an error sub folder and no further processing to take place.
If there are not fatal errors processing will continue. The main database table will be updated with the contents of the attachment, and will be identified by the unique ID.
Two sets of columns need to be cross referenced from data in the file against Masterdata already held in system. Data in file for these 2 columns will be replaced with Masterdata from the existing system (code / name pairs).
If there are errors with specific lines (cross references not found etc) the entire process must be rolled back out, the update log set to error. The attachment will be moved to an error folder. An email will be triggered – send to the administrator – containing the attachment name and unique ID and a description of what was missing / incorrect. The administrator can then correct the issues and resend the file for processing as necessary.
If there are no errors the main table is updated successfully. A check to ensure that the correct number of lines compared to the original attachment, have been created. Once successful checks the status log needs to be updated to successful and the original attachment moved to a successful sub folder.
The file contents are matched to the existing table using some key fields. If those fields with the same value exist in the table already those values are updated. If they do not exist already then a new row is inserted.
CMS and Admin requirements: CakePHP - already used - rest of site already running.
Extra notes: database / file same as attached.
further details can be provided.
confidentiality clause will be required - as this is a transactional site with live business data.
Charles P.
0% (0)Projects Completed
1
Freelancers worked with
1
Projects awarded
100%
Last project
5 Apr 2016
United Kingdom
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
There are no clarification messages.
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies