Custom Record Delimiter With FlatFileWriter?

Support/help with CloverETL implementation problems

PBizme
Posts: 9
Joined: Thu Feb 09, 2017 2:59 pm

Custom Record Delimiter With FlatFileWriter?

Postby PBizme » Wed Feb 15, 2017 4:09 pm

Is it possible to change the record delimiter? I'm writing a file out from one database table and then I'm trying to load it into another database, but I'm having trouble with the BCP utility and I believe it is because it isn't recognizing the end of the lines correctly.

PBizme
Posts: 9
Joined: Thu Feb 09, 2017 2:59 pm

Re: Custom Record Delimiter With FlatFileWriter?

Postby PBizme » Wed Feb 15, 2017 8:04 pm

According to Notepad++ the files are being written with just a LF at the end.

If I manually change the file using Notepad++ to have a CRLF terminator then it loads one row and complains about the first column of row 2 instead of loading zero rows and complaining about the last column of row 1.

If I manually change the file to have just a CR terminator then all of the rows load successfully.

PBizme
Posts: 9
Joined: Thu Feb 09, 2017 2:59 pm

Re: Custom Record Delimiter With FlatFileWriter?

Postby PBizme » Wed Feb 15, 2017 8:44 pm

I've also got semicolons in my data so changing the default column delimiter is also important to me, but I don't see a place to change that on FlatFileWriter either

jandikovae
Posts: 23
Joined: Fri Nov 04, 2016 8:51 am

Re: Custom Record Delimiter With FlatFileWriter?

Postby jandikovae » Fri Feb 17, 2017 11:20 am

Hi,
In fact, the delimiters are managed on the metadata level. Therefore if you need to write your data in a specific format (with given delimiters), data has to already come to the Writer through the input port in the desired structure.

In order to accomplish the above-mentioned concept, you should do following:
1.) Create new metadata with the same field structure (or you can just right-click your original metadata and select "Duplicate metadata"). Double-click the newly created metadata which gets you to the "Edit Metadata" screen. In the screen you can see a "Delimiter" column that allows you to setup delimiter on a record level (first row) and a field level (other rows). Update the newly created metadata as desired.
2.) Add a Reformat component right before your Writer (FlatFileWriter in your case), connect the edges and setup the incoming edge to use your original metadata and outcoming metadata from Reformat component should use the newly created ones (you can drag and drop the metadata from the Outline pane to the edge). Then you can simply map fields in the Transform property in Reformat component from original metadata fields on the left side to the appropriate fields on the right side (again, you can just drag and drop them).

This way the newly created metadata will be used by the next component after the Reformat component. Hope this helps.

Best Regards, Eva
---
Eva Jandikova
CloverCARE Support
CloverETL | Rapid Data Integration

Visit us online at http://www.cloveretl.com
How to speed up communication with CloverCARE support

PBizme
Posts: 9
Joined: Thu Feb 09, 2017 2:59 pm

Re: Custom Record Delimiter With FlatFileWriter?

Postby PBizme » Fri Feb 17, 2017 4:40 pm

I'm using dynamic metadata here so I can't define anything in a menu. I've attempted to add fieldDelimiter and recordDelimiter into my dynamic metadata line, but it didn't change anything. Is there a way to change delimiters with dynamic metadata?

svecp
Posts: 25
Joined: Wed Nov 09, 2016 11:51 pm
Location: 2111 Wilson Blvd., Arlington VA 22201
Contact:

Re: Custom Record Delimiter With FlatFileWriter?

Postby svecp » Sun Feb 19, 2017 7:24 pm

Well, it is not unsolvable. Metadata are in fact represented by an XML document. So using jobflows it is possible to pre-generate metadata, similarly as Eva suggested and set them any delimiters as engine allows. Basically create some sort of static/dynamic infrastructure. Below is a prototype how "metadata generator" could look like. The only thing missing is a graph, which will use external metadata, with parametrized fileUrl attribute. Of course, this one should run in jobflow prior the DB dump itself.
Attachments
metadata.PNG
metadata.PNG (26.25 KiB) Viewed 352 times
--
Pavel Švec | CloverETL | Sales Engineer | 2111 Wilson Blvd | Suite 320 | Arlington, VA 22201


cron