One of my friends is working on a project which she spends most of her time integrating data from Xero, MyOB and other sources with their product at her work place, pretty much everyday.
Normally this data exports come as CSV and Excel spreadsheets, last week she asked me whether we can build something to help her with this repetitive, boring task which needs lot of attention to get it right.
So I asked her what she does, and she explained that the application the company has developed, only accepts data in a certain format, so she normally open spreadsheets coming from multiple sources and transform them by hand using a spreadsheet software to fit the template that developers have provided to her to get the data into the system. So this is what she does,
- Remove unnecessary fields from the import file.
- Merge multiple fields to a single field (ex: First name and Surname becomes Name, etc)
- Trim certain characters from Address fields and Phone number fields so the system doesn’t blow up trying to import them into their database.
As she claimed, this normally takes around an hour per file with 100 rows.
I thought there might be more than 1 person in the world who might need help with manipulating data in spreadsheets. So csvfix.com was born last weekend.
What it does is pretty much what I explained above in three points, it can Trim/Remove characters or words from Columns that get imported, merge and map multiple columns into single column, convert file types while doing all the above and get the output in a clean, compatible Excel file. Oh it also supports previewing data before taking an export, so proof-read before really export stuff.
I also implemented user accounts in the applications as well, which let users to save their Mappings as templates in the cloud, so ones they have build whatever templates they want to use against multiple different types of files, all they gotta do is upload the file and apply the template and rest gets done automatically, producing a clean, precise spreadsheet which fits the template user wants.
Look here for the application CSVFix.com Like everything else I do, find the code here :)