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