Nifi ConvertExcelToCSVProcessor - broken pipe separator

[#1]

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?

[#2]

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

[#3]

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 ¦

[#4]

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

[#5]

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.  

[#6]

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!

[#7 · Accepted solution]

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?

[#8]

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

Thanks!