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

[ryncewynd]

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

[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)

[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.

[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.

[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