10 reasons why your Magento integration is custom
We do a lot of Magento integration work to connect to inventory, PoS and stock control systems. Most of them are capable of outputting a well structured CSV or XML file that can, in theory, be imported into Magento. It seems like a simple task – just use Magmi or one of many commercial CSV import extensions. If it’s that straight forward, why do we have to write custom code for almost every Magento integration?
Here is a short list of some of our recent Magento data import quirks that required custom programming.
1. File naming
The inventory system exported the CSV files into the same folder regardless of how they were produced. Anyone using the inventory system may generate a CSV file for their own needs with a different set of data and the importer needs to be smart enough to ignore that.
An inventory control system called FastBase exports files with the same name during the day. The only way to tell them apart is to check the file creation / modification date. It may not be 100% reliable either if someone opened and saved the file before it was uploaded to Magento.
2. Old files get re-submitted
Depending on how the connection with Magento is configured it is possible for some outdated files to be sent to Magento again. The importer will process them and overwrite the correct data in Magento with the outdated data from the files. It can be prevented by checking file date and comparing it to the previous CSV file.
Outdated CSV files were uploaded to Magento when the customer did some maintenance work at the inventory system end or just copied files around. It was happening often enough for us to build protection in our Magento CSV importer.
3. Prices are not rounded
There is no one right way to display prices. Some merchants prefer $199.95 while others like to round the number to $200. Inventory systems often use “float” data type for prices making them few cents off. The inventory system corrects for it in the code, but not when in the exported data.
4. Multiple columns for prices
Merchants doing retail and wholesale have different ways of separating retail and wholesale prices. Some do it via a discount field, some have two different price fields. It all gets confusing if both methods are used in the same CSV export.
A CSV export file had only prices for electronic gadgets at the retail price in one column and only trade prices for all other products in the other. To complicate things further one column had taxes included and the other didn’t.
5. Decimal quantities for per-item products
A popular accounting and retail management system called MyOB doesn’t have a flag to restrict quantities to integer values, so you may end up with something like 1.95 motorcycles in stock.
A tiles and flooring warehouse had many tiles listed with decimal quantities like 0.0009 or 1.95. No one could explain how those numbers ended up in the database. We had to correct for these errors in our MyOB to Magento integration, but only for some product categories because it is OK to have 10.5m of lino.
6. This column is not what you think!
Retailers face their own difficulties dealing with limitations of their stock control systems. Sometimes the way they use them in unconventional ways or the system may be so flexible that there is simply no conventional way.
A clothing retailer was putting Cost into Special Price field and if the product did go on sale they would change the Special Price field to the sale price and tick some other flag to indicate it was actually on sale. The flag was never exported into the CSV. They decided they don’t want to have a sale price on the website anyway, so the problem went away.
7. Parsing text to populate Magento attributes
Sometimes the product name, code or SKU in the stock control system contain valuable information that need to be imported into one of Magento attributes. For example a supplier code, garment size or color can be part of the product name.
Orion, a car and motorcycle dealership system doesn’t allow any custom fields. All useful information about the product was entered into the name in a cryptic, but quite systematic way. We had to extract size, brand and the actual product name using text parsing and matching patterns inside our inventory control app.
8. Product description formatting
Magento allows short and long product descriptions to contain HTML. Even if all they have is plain text it should be a nicely formatted text. Our Mageto integrations convert * into bullets where they are used as bullets, wrap paragraphs of text into <p> tags and make links clickable <a> tags.
A custom built inventory control system used both: \n for a new line and the actual new line ASCII code. Some products had ASCII, others had \n. We had to cater for both.
9. Linking products to images
Stock control or PoS systems don’t usually store images. Even if they do, they are downsized or not exported. Merchants have to store images as files in folders using naming conventions to tell which image belongs to which product or style. It is quite arbitrary and often unreliable.
One customer used file names like P17383b.jpg and P17383b_1.jpg where P17383b is the SKU of the product and _1 is the number of the image in the sequence. Another customer had file names starting with the SKU followed by the description of the image and parts of the product name, e.g. AO86_HOLLYWOOD_SEP_FLAT_NOCOL.jpg
10. Image replacement
Images are usually replaced by removing an old file and adding a new one with a different file name. Changing the file name makes it easier to track the changes.
A dealership management system exports all motorcycles in a single batch every time, including the image files. The contents of the files have to be compared to the contents of the files on Magento because even the date gets overwritten every time an export file is created. We got away with just comparing the file lengths.
A 90% universal Magento importer
Our Magento CSV import module is quite universal and easily configurable in most cases. It is the seemingly small part that needs tweaking, but it still may take many hours of software engineers time to make changes. And it’s OK, because we don’t like to tell merchants how to run their business – we adapt our solution to how they do it.