flat file conversion from one format to other - mapper tools/implementation

This is kinda broad topic and I did lot of research on stackoverflow and general google but didn't get good answer. May be cause our project need is bit unique(surely not a lot different).

We have a Web product which takes in completely csv file as an import of some business data. It as multiple sections of data.

Example

ParentRow
.........Single row with 10-15 columns
EndParentRow
ChildRows
.........Multiple child rows with 10-15 columns
EndChildRows
............
............

above continue multiple times depending on number of parents

We have 2 main problems:

1. We have 25-30 customers who uses above product and needs to upload above CSV file monthly. They complains about our file format that it is overwhelming(and I totally agree, it was designed by ex-Architect).

2. All 25-30 customers gets data in their own format from another system(all of them have different formats). Difference range from different column names, 2 dimensional data compared to our multipe table in same file format, different data values(which maps/corresponds to certain values in our system).

With time and money as big constraint, we cannot implement a mapping solution which caters to everyone's need.

We have to come up with creative solution to solve this problem. One direction we want to explore for now is using 开发者_C百科some out of the box generic mapping tool to convert a given CSV format to our CSV format. Idea is customer will buy this out of box product and will do one time favor to them by mapping files and then they should be able to run with it unless some mapping needs to change.

With that in mind, my research didn't yield good results so far. Only promising tool I have come across is Altova Mapforce. It does seems overkill at time and seems bit complicated considering our customer who are not advanced technical users.

Any help, ideas or suggestions on this are highly appreciated. I know I haven't explained the problem statement completely, hence I will be more than happy to answers question to define requirement better.

Look at the Pentaho PDI tool. It's open source and it will be able to do this easily. If you really want to, you could remove all necessary plugin and scale this down, or even embed this into your Java application.