I have an Open Cart eCommerce site and need a module to export orders, which can then be imported into a legacy MS Access application.
We have been quoted 20 hours by a local company, who built the original MS Access database.
At the moment I have to re-enter the order into the MS Access application whenever a new order is placed on the website. A new lookup table will need to be built within MS Access to map the product code from the website to a product within the MS Access database. The lookup table will also contain additional fields required to populate the Access database correctly. A view will need to be added within the Access application to allow the management of this mapping.
Some of the products on the website map to multiple products within the MS Access database and I believe hidden attributes within Open Cart will need to be added, one for each sub-product, so these can be matched to individual products in the MS Access database. We do not use the attributes feature within Open Cart so these can be utilised if necessary or additional fields created within the admin view.
A new module will need to be added to Open Cart to allow the download of orders by date range or since last export.
1) File Format
A csv file will be provided from the web site for each new order. The first field in each line of the csv file will be a line identifier and will be set to “H” for the order header details and “D” for each order line detail. In order for the import process to route data correctly all non-numeric fields should be enclosed in double quotes. Any double quotes keyed in by the customer should be removed programmatically when the csv file is created.
2) Import Routine
A new menu option will be added to the Access system labelled “Website Orders”. Selecting this will display a file import screen where the user can select the location of all csv files to be imported. This screen will default to a folder location set up in the administration area but users will be able to select a different folder if required.
When the location has been selected the system will import each valid csv file in the folder. For each file a verification routine will be performed to ensure that data is in an identifiable format. If the file is correct the import routine will be run and a new order will be created. Most information will be provided by the csv file, however there will be a number of lookups within Access to interpret and correctly map fields when necessary.
At the end of a successful import, each csv file will be flagged as imported and moved to an archive folder whose location will be set up in the administration menu.
When the import is completed the Import Details screen will be displayed with the new orders created listed. Where an error has occurred in the import routine the relevant file will be marked as not imported and an error reason given.
3) Import Details
A listing screen will be designed to display details of each import. Search parameters will allow users to list all files imported within a date range and the associated orders created. An option will be available to select an order and display the standard order screen.
4) Product Lookup
The web site file will provide an six to eight character alphanumeric code for each product detail line. This code will be different to the product code within Access and so to enable the import routine to create orders accurately the “Maintain Products” option will be amended to allow the user to record the Web Product Code against any existing products. Once this information is stored the import routine will be able to map to other fields.
Finally we are looking to build an ongoing relationship with a software company to assist us in future work. We have 3 Open Cart sites and are expanding.