Hi,
I am wanting to import data into BPM (Cases) where some of the fields have carriage return line feed (CRLF) or Line Feeds, which when importing into BPM will be converted to _x000d_. This obviously impacts the content as there are no line feed formatting making reading the text difficult.
Research showed that this is an Excel thing and suggestions to use the following methods:
1. Do not save the file as .XLSX, instead use .XLS. However BPM only supports the import of .XLSX files
2. Once the data is imported do a SQL Update to convert _x000d_ to Char(13) to fix the formatting
I do not believe either of these options are viable, but does anyone else know how to import text into BPM which includes CRLF correctly?
I have several thousand records to import and so editing content manually after import is not an option.
thanks
Mark
Like
Hello!
You can use CLEAN function in excel in order to clear the file from the CRLF symbols. http://prntscr.com/lo6rab
Moreover, we have registered a suggestion for our R&D team in order to improve the following functionality in the upcoming releases.
Best regards,
Matt
Matt Watts,
Hi Matt,
Thanks for your reply
I was also aware of the ability to use the CLEAN function, but this therefore means that all of the formatting of the text to create paragraphs and text line breaks are all removed. The outcome of this is that a single block of text is created which is VERY hard to read.
Do we have the ability to perform the SQL Update in a way to achieve the following:
UPDATE ImportedExcelTable SET Description = Replace([Description], _x000d_, Chr(13));If you are only going to allow XLSX file formats for import, this is a common issue and does really need to be fixed.
thanks
Mark
Mark Roberts,
Hello.
You can create a process, that would parse the already imported records and replace the crlf symbols with the required characters. You would need to utilize the script task element of the process designer. The example of the implementation can be found on the MSDN:
https://docs.microsoft.com/en-us/dotnet/api/system.string.replace?view=…
Best regards,
Matt
Mark,
We found the same thing when we were doing the import. We were exporting from another SQL database to a csv then converted it to a xlsx. What I found is that if you replace the char(13) (Carriage Return) with a blank, Bpmonline recognized the remaining Line Feed (char 10) and formatted the text properly.
Bob
Hi Bob,
Thanks for your reply, but for the life of me I cannot recreate this.
I have:
1. Used the Excel formula to replace the Char(13)
SUBSTITUTE(Y4,CHAR(13),""))
2. Entered text directly into a cell in excel and used ALT+Enter
3. Performed an export of the text from a formatted Note field in BPM, copied that content to another row and then re-imported
ALL of the import methods fail to format and include line breaks. I have also performed MANY variations on the above theme with Char(13), which always enters the _x000d_ characters. The export of existing formatted content from within BPM shows that the line breaks are correctly using CHAR(10). Just importing these does not work!
I am going to review the Process method to see if this can work, but I cannot understand why if this worked for you, why this is not working for me. I do not see why this does not just work out of the box, as I cannot be one of a few customer who needs to import data into the Notes field with line breaks maintained.
thanks
Mark
To let anyone following this thread know, I raised a support case and this is something that has been fixed and will be available in the 7.13.3 release.
Hello,
Indeed the problem described in this community article was resolved by our R&D team and it is planned to deploy the fix in 7.13.3 version of the application. Please monitor bpm'online release notes for up to date information on releases.
Best regards,
Oscar