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

4 pointsryncewynd2y ago7 comments

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

bhavikchavda2y ago

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.

ryncewyndOP2y ago

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)

bhavikchavda2y ago

Thanks

ssss112y ago

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.

wizzerking2y ago

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.

slater2y ago

Can you provide a few examples of these different files?

ryncewyndOP2y ago

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

j / k navigate · click thread line to collapse

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

4 pointsryncewynd2y ago7 comments

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

bhavikchavda2y ago

The data import solution can help!

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.

ryncewyndOP2y ago

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)

bhavikchavda2y ago

Thanks

ssss112y ago

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.

wizzerking2y ago

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.

slater2y ago

Can you provide a few examples of these different files?

ryncewyndOP2y ago

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

j / k navigate · click thread line to collapse