HN Explorer

Ask HN: Massaging many client CSV/textfile to correct columns/format etc.?

4 points by ryncewynd ↗ HN
We have hundreds of files arriving every month and each client's layout looks a bit different.

For a single client, each file they send will be the same.

But each client has their own layout.

Is there a good service around that I can setup conversion templates?

Would be great if we can setup a template for each client and the file gets run through the template and arrives happily in our standardised layout.

I haven't managed to find one but no doubt I'm just using the wrong search terms

7 comments

[ 2.7 ms ] story [ 23.4 ms ] thread
slater
Can you provide a few examples of these different files?
ryncewynd
For a very basic example, lets say I want 3 columns

FirstName|LastName|Age

And client files look like

ClientA: First|Last|Age

ClientB: Age|FirstName|Last

CientC: FullName|Age

ClientD: DateOfBirth|First|Last

So they all have the required info, but some intervention is required.

Column matching/renaming

Column order

Splitting full name to first/last

Converting DateOfBirth to Age

bhavikchavda
The data import solution can help!

You can check out https://impler.io/ it covers many of the features you mention and you can extend its functionality as well like converting first/last to fullname. Your problems related to edge cases, configuration, and support will be resolved automatically with it.

It's open source at https://github.com/implerhq/impler.io/

I'm a co-founder of it and if you create a feature request for something you want, I will happily cover it.

ryncewynd
Thanks for much for your reply, impler looks the perfect solution

I'll try it out and do a demo with our administration team (who are the ones struggling with the files)

wizzerking
Me being a software engineer I would write this in python. Each python file would named for the client and produce either a text or rtf, or docx. file.
ssss11
Is VBA still a thing in excel? You could have a function per client and just run the correct one to format it.

Or using python or your choice of language to read .csv files and modify..

It may be overkill but n8n is a process automation tool (open source, self hosted) - give it input, run an action, get output. I’m sure you could do it in that.