Today I had to create a CSV (comma separated value) file containing product offers and convert it into another spreadsheet file which required slightly different columns and some modifications to columns.
I could have done all this in Excel or Open Office or write my self a little script e.g. in PHP or Java. But I found a cool web-based tool called Transformy which allowed me to do just that without any coding or hacking in Excel. In this article I want to do a little review of this still young tool.
Transformy works pretty simple. You upload your CSV or Excel file, it automatically detects your file delimiter and shows your data in a table. Then you just click here and there to modify your columns. You can remove, add or rename columns, change the order of columns as required. You can apply different various functions like Search&Replace, simple math calculations or date format conversions to each column to modify the content. The Quick-Start Example is pretty nice to get started even without an own file.
The UI comes pretty clean, but it also contains lots of useful advanced features covered behind the Settings button. It has pretty good support for all the low level stuff like delimiters, text qualifiers, line endings (Unix, Windows) and character encoding. All that can be configured for the source and for the target file.
In a perfect world all systems would use UTF-8 for encoding, but as most of us know file encodings often can be a real pain and properly converting between different encodings is necessary. In e-commerce webshops, marketplaces or price comparison sites exchange product data, prices and stock data using Comma-separated files but each system usually requires a different encoding. This tool makes it pretty easy to convert between the most common encodings with a few clicks.
Another nice effect is that it is pretty tolerant even to invalid CSV-files. A frequent problem when people create those files are double quotes and line breaks in descriptions. In order to let CSV-parsers recognize line breaks you have to enclose your content with a text-qualifier, usually double quotes (“your content”). Doing that allows you to also have line-breaks in description fields. The problem is when your description also contains a double quote character as in 22″ monitor. Tor circumvent the problem you have to double the quote character like this 22″” monitor to still let the parser parse your file. Transformy seemed to even handle those cases where the doubling was not done properly which is often reality.
Another nice feature is the built-in group by functionality. It allows you to group by one column and apply aggregate-functions to the other columns.
In the screenshot I grouped by the column pricecurrency and applied two aggregate functions to the identifier and name column. The identifier column has the Concat distinct values aggregate function applied which concatenates all grouped values together using a specified delimiter. The name column just has the Count rows aggregate function applied which shows the number of grouped rows. This grouping and aggregate is pretty handy when you want to quickly analyse e.g. a file with product data to see how many distinct categories are contained in the category column.
The sorting option lets you sort the complete file by a specified column in ascending or descending order.
Of course, most of the stuff can be done with Excel or a database like MySQL. But this tool is web-based and just allows to do it quickly online without requiring you to do coding, mastering Excel or a MySQL database.
If you are all done mass-editing, crunching and converting and you see the final result you can simply download the target file using your specified delimiter, text-qualifier and encoding or alternatively download it as a real Excel (.xls) file. If you are working on large files and just want to test the resulting file, you can also use the Download preview file which just downloads a file containing the rows you see in the preview, which is much smaller than the actual final file. I especially liked the Excel download option, because it gives me a easy way to convert a txt file to Excel (.xls) online. This is great because Excel always has problems with line-breaks in columns of CSV files, but if I first open it in Transformy and then convert it to Excel I don’t have problems with line breaks anymore.
That’s it for my little review of Transformy. If you are working with CSV files and spreadsheets and often need to manipulate and crunch the data or even just need to quickly convert between those formats or export your Magento product data to a different format then maybe it also is a tool for you. A german description of the tool can be found here.