We have to export invoices in an electronic format. It looks like an EDI format, simplified. I'm wondering what is the easiest solution to do it. I thought about using the Data Mapper, but we don't have any experience with it, and I'm not even sure if this file structure can be used with it. The "ILD" segment, containing the items of the invoice, doesn't have any field name, I don't know if it's possible to handle that in the Data Mapper.
Please not that there is NO loop to consider, we can put one generic line for ALL the items in the invoice (just one line, even if there are 10 items), so it's a simple mapping : a list of value for each invoice, to map in this text structure.
There is ONE line where it's not the simple "field name" "value" syntax, it's "field name" "value1" "value2". And the delimiter are a bit weird, I'm attaching a file example to let you see.
Could someone more familiar with this kind of a file give us some ideas ? Thanks !
Yes, you can handle it in data mapper.
I removed a few lines in your file to make the example faster:
|INV TYP| FREE
x1 VERNIS UV BRILLANT R° 112.00 19.60 224.00 T1
x1 FRAIS DE LIVRAISON PARTICIPATION 18.00 19.60 18.00 T1
|INV TOT| 155.48
The structure in TDM could be:
You can then map it to any structure.
For instance a XML structure:
<DESCRIPTION>VERNIS UV BRILLANT R�</DESCRIPTION>
<DESCRIPTION>FRAIS DE LIVRAISON PARTICIPATION</DESCRIPTION>
I enclosed the test structure (It is Talend 6.2.1 version).
Great Eric ! It's totally helping us, thanks a lot ! It's a huge time saver. I was wrong to use the EDI format. I have imported your project, and completed the structure, it's quite easy to do. A silly question now, if I want to use the datamapper, I need to have a mapping, I have created one from XML to flat, and I want to output my invoice content to the XML structure, then the DataMapper will transform it into the flat file. Is this the right way to do it?
So it would be :
SQLInput -> tXMLMap -> tHMap -> FlatFileOutput
My main problem now is to have the XML structure as a schema for my tXMLMap. Can't I export it in any way from the structure I have created ? Or do i have to create one manually to give the structure to the tXMLMap ?
Sorry if the questions are a bit silly, we are total beginners with complex structures...
You can do it like that. You could also remove your tXmlMap and just do:
SQLInput -> tHMap -> tFileOutputRaw
If you use a tXmlMap, you can create the structure from a test XML file.
Sorry, just seeing that I didn't reply to your message, it's not polite at all.
I did as you said, and it worked really well. You were a great help for this little project, thanks a lot. If one day you are passing by our office, we own you a free beer !
Talend named a Leader.
Kickstart your first data integration and ETL projects.
Watch the recorded webinar!
Part 2 of a series on Context Variables
Learn how to do cool things with Context Variables
Find out how to migrate from one database to another using the Dynamic schema