Data Import and Export (from web sites)

Today database designers and programmers have numerous options to support when it comes to the import and export of data. Common formats supported are:
Comma Separated Values - CSV
Lotus Symphony spreadsheets
Lotus 1-2-3 spreadsheets
Excel spreadsheets
Other databases - SQL, Visual FoxPro, MS Access
XML
Common application formats - MS Word, MS OutLook
All of these have their pros and cons. However, considering them all, XML (Extensible Markup Language) has; as intended; become the most important.

This is due to its wide spread support and flexible nature. Its flexibility allows for any form of data from any database to be exported to, or imported from, an XML file.

So any modern ERP software system worth considering, must be able to import/export data into/from any of its tables. Not just a few tables chosen by the developers.

This ability to import data is very important for SME's with an e-commerce web site. As if it cannot import web site data into its ERP system without manually re-entering the data, it seriously needs to consider replacing the system with one that can. Just to avoid the errors and mistakes generated by the process of human intervention.

With a capable ERP system data from an e-commerce web site can be handled in various ways depending on whether the site is hosted or held on an internal server.

Hosted Site
Two approaches are commonly taken -
1) e-mail of the data to the company when the customer confirms the order, or
2) FTP access to an order file produced when the customer confirms the order.

1) E-mail - given a low volume of orders; less than 100 per hour; the e-mail route is considered the best approach to take.
With this approach when the customer clicks the Confirm button on the web site, the underlying program formats the data into XML format, either as a separate file that becomes an e-mail attachment or as the body contents of an e-mail. The e-mail is then automatically sent to the companies e-mail address.
Depending upon volume and required response times the e-mail address is read on a frequent basis with rules to place the e-mail(s) into a special folder. When XML attachments are used, these need to be saved into a folder on the server ready for the ERP system to process.
When the XML is in the body of the e-mail they can be read directly by the ERP system.
Having now got access to the data, the ERP system can be run manually or automatically under a timer to read the XML data and update its tables, ready for the normal sales fullfilment process to begin.
After processing each e-mail/file the system should move the source e-mail/file to an archive folder prior to deleting it from the source folder. The e-mails/files need to be available for when a processing error occurs.

2) FTP - here the ERP system can run automatically under a timer to read; on a frequent basis; the XML data files held in a web site folder, process their contents and then move the file(s) to an archive folder, prior to removing the file(s) from the web site folder, in order that they are not processed again. (An alternative approach is to rename each file as it is processed.) The file(s) need to be available for when a processing error occurs.
Once the data is imported into the ERP's tables, the normal sales fullfilment process can begin.

Internal Server Site
Many more options are available here, but the essential feature is that a folder is available to both the web server and ERP system. The web server places XML files in the folder as orders are confirmed, whilst the ERP system reads these files and updates its own tables on a regular basis and removes or renames the XML files after processing.

On all these approaches the format and data content of the XML file is dependent upon the specfics of the ERP systems and its requirements.

The only manual intervention (other than dettaching XML e-mail files) in these processes is when the user company decides to manually run the import process instead of it running automatically under a timer.

When an ERP system can export its data in XML format, the resulting file(s) can be FTP'd to the web site. Then using XSL (Extensible Scripting Lanaguage) the contents of these XML files can be easily incorporated into web pages.

So products, stock levels, customer accounts, etc. can all be made available on the web based upon the primary source (ERP system) with very little manual intervention. Thus removing the need for dual data maintenance.

An example of a web site using this approach is www.places-to-go.org here advertisors and people requesting a listing complete a form which is e-mailed to the company. Who import the data from the e-mail using Acceptum Business Software as its ERP software. This data can then be exported in XML format and then FTP'd to the web site to provide the list of attractions, which is formatted, sorted and extracted on a county basis using XSL.

XML use has opened up a whole new world for the SME allowing web sites to have the power and feel of a much larger organisation. In fact the very flexibility of SME's over monolithic large corporations gives them the opportunity to have a far better web presence than larger organisations.