UniversalDataReader issue

Support/help with CloverETL implementation problems

scl84
Posts: 11
Joined: Mon Jun 30, 2014 9:25 am

UniversalDataReader issue

Postby scl84 » Thu Aug 14, 2014 1:04 pm

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

imriskal
Posts: 383
Joined: Wed Aug 15, 2012 8:18 am

Re: UniversalDataReader issue

Postby imriskal » Fri Aug 15, 2014 10:14 am

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
(2 KiB) Downloaded 65 times

I hope this helps.

Regards,
---
Lubos Imriska
CloverCARE Support
CloverETL | Rapid Data Integration

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

scl84
Posts: 11
Joined: Mon Jun 30, 2014 9:25 am

Re: UniversalDataReader issue

Postby scl84 » Mon Aug 18, 2014 2:11 pm

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

imriskal
Posts: 383
Joined: Wed Aug 15, 2012 8:18 am

Re: UniversalDataReader issue

Postby imriskal » Tue Aug 19, 2014 1:05 pm

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:

Code: Select all

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,
---
Lubos Imriska
CloverCARE Support
CloverETL | Rapid Data Integration

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

scl84
Posts: 11
Joined: Mon Jun 30, 2014 9:25 am

Re: UniversalDataReader issue

Postby scl84 » Tue Aug 19, 2014 3:11 pm

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. :D

Thanks and regards,
Sarah

ECLogicalGlue
Posts: 3
Joined: Tue Mar 28, 2017 2:54 pm

Re: UniversalDataReader issue

Postby ECLogicalGlue » Thu Mar 30, 2017 10:29 am

Hi Sarah,

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

Thank you,
Egidio

imriskal
Posts: 383
Joined: Wed Aug 15, 2012 8:18 am

Re: UniversalDataReader issue

Postby imriskal » Fri Mar 31, 2017 8:30 am

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
---
Lubos Imriska
CloverCARE Support
CloverETL | Rapid Data Integration

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

ECLogicalGlue
Posts: 3
Joined: Tue Mar 28, 2017 2:54 pm

Re: UniversalDataReader issue

Postby ECLogicalGlue » Fri Mar 31, 2017 8:56 am

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


cron