Jira import issues via CSV - Embedded newlines in text fields are not preserved

[Amir Katz (Outseer)]

I am migrating issues from one Jira instance (the source) to another Jira instance (the destination).

I am exporting issues via CSV and then importing the CSV to the destination.

I have several multi-line text fields which have newlines embedded - some are custom fields, but system fields (e.g. Description) display the same behavior.

In the source Jira the field looks like this:

MyValue-1
MyValue-2
MyValue-3

When I check the exported CSV (exported via Better Excel add-on), I see the data in the specific column as this (double-quotes included), which is what Jira requires (see link at the end):

some-previous-column-value<tab>"MyValue-1<CR><LF>
MyValue-2<CR><LF>
MyValue-3<CR><LF>
"<tab>next-column-value

The <CR> and <LF> are the actual carriage return and linefeed characters.

After performing the CSV import, in the destination Jira, the CR/LF are gone and the field value is:

MyValue-1MyValue2MyValue-3

My CSV file is on Windows, the destination Jira runs on Linux, not sure about the OS of the source Jira.

Any idea?

Thanks

P.S. I have performed export/import of comments (also via the Better Excel add-on) and the same problem happens there as well.

======================

The Jira docs (https://confluence.atlassian.com/adminjiraserver0711/importing-data-from-csv-955168458.html) says:

Use double-quote marks (") in your CSV file to capture data that spans multiple lines

[Amir Katz (Outseer) · Accepted solution]

I have submitted a ticket to Atlassian support and they came back that it's a known bug:

https://jira.atlassian.com/browse/JRASERVER-46365

Fortunately, the bug description suggests 2 workarounds. I tried the first one (do not check Map field value) and it works.

[Andy Heinzer]

Thanks for posting back with the solution you found. 

[Amir Katz (Outseer)]

This is a known Jira bug. See link below. It also includes a workaround that I am using:

https://jira.atlassian.com/browse/JRASERVER-63388

Amir

[Amir Katz (Outseer)]

I have just realized that I may be trying to do something which is not supported.

I re-read the section in the documentation about newlines (link is in my initial question) and noticed that it does not say that the embedded newlines will be preserved.

All it says is this:

>>>>

Capturing data that spans multiple lines

Use double-quote marks (") in your CSV file to capture data that spans multiple lines. For example, upon import, Jira will treat the following as a valid CSV file with a single record:

>>>

So maybe I need to rephrase the question to ask "Is it possible to preserve embedded newlines when importing issues via CSV?".

Anyone?

[Andy Heinzer]

Encapsulating the data within " " should work to preserve this line break information for that field.  However that does not appear to be the case for you here, I suspect that your source Jira instance might not be using the expected character encoding that Jira expects (UTF-8).

In turn, even if your destination instance is using the correct encoding, it won't be able to process this data the way you expect because the characters are not exactly what is expected.

I would recommend walking through the KB JIRA Application internationalisation and encoding troubleshooting.  I have seen a significant number of Jira installations on Windows environments that are actually using an ANSI or CP1252 character encoding.   This can still allow Jira to function to what appears normally for end users on those systems in some cases, but in turn for certain characters can't be displayed/stored correctly.  In turn I think we might be seeing something similar here in regards to keeping your data portable with other Jira installs.

One way to see what encoding is currently in use is to take a closer look at your $JIRAHOME/log/atlassian-jira.log file during a startup and search for the string 'sun.jnu.encoding' (without quotes).   I would check this value on both systems.  If either come back as anything other than UTF-8, I would recommend following the KB above to set the JVM startup option of -Dsun.jnu.encoding=UTF-8 then restart that Jira application and try to perform the export again to see if this helps.

[Amir Katz (Outseer)]

@Andy Heinzer  Thanks. Extremely helpful.

More details:

Source Jira is 6.4.11; Destination Jira is 7.11.2.

At this moment I do not have access to the server logs on either server, but I do have access to the system details on both (Jira->Admin->System->System Info).

I assume that whatever is shown in the log is reflected in the system configuration.

I found that there are difference between the servers regarding encoding.

In the system info of each I looked for the string 'encoding' and found these items:

>>> Source server  <<<

In Server Info:

    System Encoding with value of UTF-8

In Application Properties:

   webwork.i18n.encoding with value of  UTF-8

In System Properties:

   sun.jnu.encoding with value of UTF-8

   sun.io.unicode.encoding with value of UnicodeLittle

>>> Destination Server <<<

In Server Info:

   System Encoding with value of  ANSI_X3.4-1968

In Application Properties:

   webwork.i18n.encoding with value of UTF-8

In System Properties:

   sun.jnu.encoding with value of  ANSI_X3.4-1968

   sun.io.unicode.encoding with value of UnicodeLittle

I don't think that the Jira admins on both servers would agree to change the encoding, so I wonder whether I can modify those text fields and convert the values (I already have a Python program that performs transformation of fields).

Amir

[Amir Katz (Outseer)]

One more data point:

When I perform the import from CSV, the last step before Begin Import shows me all the data in the input rows, by fields.

For each field you see all the inputs for each row and how it each will be written to the output field. 

What I see in the input (for a field that has embedded CR/LF) is this (which may already be  a problem):

Value01 Value02   // there is 1 space between the values, instead of CR/LF

And in the output:

Value01Value02   // space is gone

[Andy Heinzer]

This just confirms my suspicion that the source of the problem here is that the two different Jira installations are using different character encodings.

I suppose it might be possible for you to get around this problem by trying to manipulate the csv file using a replace function to try to change all the line breaks with some other control character that is correctly recognized on the destination system as a new line character, but I'm not sure what that specific character would be for a linux machine using this ANSI encoding.

I would still recommend that the destination instance use UTF-8 as per that guide.   Ultimately, I believe this is the best course of action long term and that is the supported encoding for Jira to use.

[Amir Katz (Outseer)]

@Andy Heinzer As I said, I cannot change the encoding of either server. So I decided to check how the export+import works when done on the same server.

I started with the destination server (Linux, encoding=ANSI_X3.4-1968).

1. I picked a single issue that has multiple newlines in the Description field.

2. I exported it to a CSV.

3. In the CSV I see that the line terminator character is LF

4. As expected, the Description field value is enclosed within double quotes and there are multiple LF characters inside the value separating the multiple lines.

5. I deleted the original issue.

6. I performed a CSV import from the CSV file - importing a single issue, with mapping of the key, so I will get back the exact same issue.

7. In the import process I specified file encoding of UTF-8 (although there are no UTF-8 characters in the CSV file). 

8. After the import, in the new issue, the LF characters are gone and the Description value is one long string of all original lines concatenated.

Next, I ran the same scenario on the source server which uses UTF-8 encoding.

The same happens - embedded newlines (CR/LF in this case) are removed.

So my conclusion so far is:

- Either this feature does not work as documented,

or

- Some configuration is wrong/missing causing this not to work as documented.

Please advise.

[venkatesh.prasad@ikea.com]

I have the same issue.

It looks like many others have the same issue as per this

https://community.atlassian.com/t5/Answers-Developer-Questions/import-CSV-data-as-Jira-issues/qaq-p/541576?_ga=2.202259154.2118889702.1544013960-705341794.1535370918

This is a bug in the tool

[Amir Katz (Outseer)]

it's indeed a bug, but at least we have a workaround (ugly, manual)