question Nifi ConvertExcelToCSVProcessor - broken pipe separator in Support Questions

Nifi ConvertExcelToCSVProcessor - broken pipe separator

Stefi — Fri, 18 Jun 2021 13:24:36 GMT

Hello,

I have a file in .xlsx format that needs to be transofmerd to .csv format with broken pipe as separator. When I enter broken pipe (¦) as Value Separator in ConvertExcelToCSVProcessor, in CSV file appears "?" as separator instead of "¦". Is there a way to convert .xlsx to .csv with broken pipe delimiter?

Re: Nifi ConvertExcelToCSVProcessor - broken pipe separator

aakulov — Fri, 18 Jun 2021 17:15:25 GMT

Hi @Stefi ,

It could be as simple as setting the Include Trailing Delimiter parameter to False in your NiFi processor. Then NiFi should not include any delimiter at the end of each CSV record. See this documentation page for ConvertExcelToCSVProcessor.

Hope this helps you out.

Regards,

Alex

Re: Nifi ConvertExcelToCSVProcessor - broken pipe separator

Stefi — Fri, 18 Jun 2021 17:58:23 GMT

Hi @aakulov ,

Thanks for answer.

I just checked and this parameter was set to false, as you can see at the picture. When i use a regular pipe | the csv file is ok, but when when I use broken pipe, I get ? as separator between fields in one record, instead of ¦

Re: Nifi ConvertExcelToCSVProcessor - broken pipe separator

aakulov — Fri, 18 Jun 2021 18:12:14 GMT

@Stefi , could you please provide an example of what one input record looks like and what you want the output CSV record to look like, and also what output you are getting right now? That will help us understand what the problem you are facing is.

Re: Nifi ConvertExcelToCSVProcessor - broken pipe separator

aakulov — Fri, 18 Jun 2021 18:26:59 GMT

The broken pipe character seems to be a special hex character that is longer than a single byte. The workaround here could be doing a ReplaceText processor on your Excel file first, replacing broken pipe character with something simple like a comma (,) and then doing conversion to CSV.

Re: Nifi ConvertExcelToCSVProcessor - broken pipe separator

Stefi — Fri, 18 Jun 2021 19:23:42 GMT

Hi @aakulov
Fiirst picture is example of .xlsx file, second picture is what I get, and third what I want to get.

Did you mean first to convert excel to csv with comma delimiter, and then to use ReplaceText to replace comma with broken pipe? I don’t have broken pipe in excel file, why to use ReplaceText on original .xlsx file first?

Thanks!

Re: Nifi ConvertExcelToCSVProcessor - broken pipe separator

aakulov — Fri, 18 Jun 2021 19:38:42 GMT

Oh, I thought you had broken pipe in your Excel file, but this makes more sense. Thanks for clarifying.

Do you need to use broken pipe delimiter on the output because the target process only accepts that character as the delimiter? Seems strange that you'd use broken pipe for a csv file.

You can have ConvertExcelToCSVProcessor write out a simple comma-separated format. Then use a ReplaceTest processor to replace delimiter commas with your broken pipe hex code (see example here). Will that work for you?

Re: Nifi ConvertExcelToCSVProcessor - broken pipe separator

Stefi — Fri, 18 Jun 2021 20:26:40 GMT

Yes, ReplaceText processor helps. First I convert to csv with regular pipe as delimiter, and after that I replace it with broken pipe.

Thanks!