Problem
When you create a new metadata definition for a source file, sometimes it may seem that the first row is lost.
This can be caused if there are different line separators being used for the header record and subsequent rows. For example, the header record may use a return character (\r) and the subsequent rows may use a return character and a new line (\r\n).
If you have defined the line separator to be \n\r in the File wizard, then the header record will not be read correctly as there is no \n character. Here is an example:
- The source file is missing \r (shown as CR in Notepad++):
2. The following properties set \r\n as the line separator:
3. This results in the header record being misread because there is no \r, so the header record includes values from the 1st line:
4. Which means the reversed file only seems to hold 2 records, instead of 3:
Solution
The best solution would be for the source file to be formatted correctly and use the same line separator for both the header record and subsequent records, but this is not always possible to achieve.
This can be rectified in Convergence for DI:
- Launch the Flie Wizard on the file with this problem (right click on the metadata folder, select Actions>Launch File Wizard)
2. Change the Line Separator to \n only:
3. On the next screen, remove the existing columns, choose your reverse settings and click on Reverse again to reverse the file:
4. Now the file reverses correctly, with the correct column headings and expected number of records
*Note: As you have removed the \r as a line separator, it will still be in the reversed file as a character at the end of the value in the last column (it may appear as a space, but the control character is still there). Therefore, you may want to trim the values in the last column in any mappings you create from this file, using a function like trim() or substring() depending on what your target is.
Related articles