UniversalDataReader issue

Hi,

I have a txt file with the following data (24 columns where the last column might be null):

2617|N|N|903|Kupelweiser Wine Tasting with Brand Ambassador Peter Zemmer|Meet the Maker Tasting|Wine||11/20/2014|16:00:00|19:00:00||||In 1878 Fritz Kupelwieser founded a cellar in the prestigious Alto Adige region of Northern Italy that still bears his name. During the 1960’s Kupelwieser began to produce superior white wines in response to demand for elegant whites. Today, Peter Zemmer, the grandson of Fritz, has developed Kupelwieser Winery into one of the top estates in the Trentino-Alto Adige, a region noted for its crisp and elegant wines.|Kupelweiser|Kupelweiser|Italy|Peter|Zemmer|Brand Ambassador|95476750;99842750;95475750;99843750;;;;;;;||
2618|N|N|906|Kupelweiser Wine Tasting with Brand Ambassador Peter Zemmer|Meet the Maker Tasting|Wine||11/21/2014|16:00:00|19:00:00||||In 1878 Fritz Kupelwieser founded a cellar in the prestigious Alto Adige region of Northern Italy that still bears his name. During the 1960’s Kupelwieser began to produce superior white wines in response to demand for elegant whites. Today, Peter Zemmer, the grandson of Fritz, has developed Kupelwieser Winery into one of the top estates in the Trentino-Alto Adige, a region noted for its crisp and elegant wines.|Kupelweiser|Kupelweiser|Italy|Peter|Zemmer|Brand Ambassador|99842750;95475750;95476750;99843750;;;;;;;||

The graph is failing to parse the file with the following error message, (where the 1st and 2nd record are getting concatenated since \n is not recognized):
Unexpected default field delimiter, probably record has too many fields. in record 1, field 24 (“OfficePremiseLocation”), metadata “event”; value: '2617|N|N|903|Kupelweiser Wine Tasting with Brand Ambassador Peter Zemmer|Meet the Maker Tasting|Wine||11/20/2014|16:00:00|19:00:00||||In 1878 Fritz Kupelwieser founded a cellar in the prestigious Alto Adige region of Northern Italy that still bears his name. During the 1960\u0092s Kupelwieser began to produce superior white wines in response to demand for elegant whites. Today, Peter Zemmer, the grandson of Fritz, has developed Kupelwieser Winery into one of the top estates in the Trentino-Alto Adige, a region noted for its crisp and elegant wines.|Kupelweiser|Kupelweiser|Italy|Peter|Zemmer|Brand Ambassador|95476750;99842750;95475750;99843750;;;;;;;||\n2618|N|N|906|Kupelweiser Wine Tasting with Brand Ambassador Peter Zemmer|Meet the Maker Tasting|Wine

The 24th column is having delimiter such as \n whereas the other columns are delimited with |.
I tried all the possibilities for 24th column’s delimiter but it’s still giving me same error.

Even adding a value to the 24th column (instead of having it null) is giving the same error.

Could you please let me know how to solve this issue?

Thanks and regards,
Sarah

Hi Sarah,

I see at least two potential sources of troubles:

  1. According to the standard, CSV files should contain an empty line at the end of the document. Does your file contain it?
  2. Are you sure your record delimiter should be \n and not the Windows equivalent \r\n? During the file editting, it may easily happen in some editors that line separators are changed without any warning. Please check that your input file really contain \n separator and not some other one.

Nevertheless, the safest way is to extract CloverETL metadata directly from the flat file. For more details see the attached example graph and this documentation page: http://doc.cloveretl.com/documentation/ … -file.html
metadata_delimiter.zip
I hope this helps.

Regards,

Hi Lubos,

Thanks for your reply.
I have checked the file with special characters view and it’s having “LF” at the end of each line which mean “\n” if i’m not wrong.

I also have another concern:
The last column might be empty for some rows, and i’m having the following error:

Component [UniversalDataReader:UNIVERSAL_DATA_READER] finished with status ERROR. (Out0: 0 recs)
Parsing error: Unexpected end of file in record 1, field 24 (“productType”), metadata “event”; value: ‘SimpleDataParser does not provide raw record.’

How can we force Clover to accept null values in the last column and process them as empty?
Or do we need to give a non-null column as the last one?

Thanks and regards,
Sarah

Did you try the metadata extraction as I advised in my previous post? Did it help?

The last column being empty is not an issue but the number of delimiters still has to be the same. I.e. those lines would end with a delimiter. For example:


1|2|3
4|5|
7|8|9


(the last line is empty)

If you send me a file you are not able to read, I can prepare an example graph tailor made for the data.

Regards,

Hi Lubos,

I have tried with the metadata extraction and it’s working fine. After comparing the generated metadata and mine, i found that the data type was not matching and thus the data was messed up.

Thank you very much for your precious help. Now everything is working as expected. :smiley:

Thanks and regards,
Sarah

Hi Sarah,

How did you solve the problem exactly? I’m having the same issue and can’t find a solution.

Thank you,
Egidio

Hi Egidio,

Could you describe the issue in more details? I think that in Sarah’s case it helped to extract metadata automatically instead of creating it manually. See Metedata extraction

Hi Imriskal,

I’ve found the issue and was not related to the CSV nor with CloverETL. When moving the file to Clover Server it get modified.

Thank you,
Egidio