Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Nifi ConvertExcelToCSVProcessor - broken pipe separator

avatar
Explorer

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?

1 ACCEPTED SOLUTION

avatar
Master Collaborator

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?

View solution in original post

7 REPLIES 7

avatar
Master Collaborator

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

avatar
Explorer

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 ¦

Stefi_0-1624038997070.png

 

avatar
Master Collaborator

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

avatar
Master Collaborator

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.  

 

 

avatar
Explorer

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!

 

Stefi_0-1624044025242.pngStefi_1-1624044036472.pngStefi_2-1624044045651.png

 

avatar
Master Collaborator

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?

avatar
Explorer

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

Thanks!