Hi I've got a table in an Excel spreadsheet with data in it and I would like to insert the table into a Jira issue. I know that I'm able to use the Jira Text Formatting notation to put it into a nice table format as per below: Notation Comment ||heading 1||heading 2||heading 3|| |col A1|col A2|col A3| |col B1|col B2|col B3|Makes a table. Use double bars for a table heading row.The code given here produces a table that looks like: heading 1 heading 2 heading 3 col A1col A2col A3col B1col B2col B3 My problem is converting the spreadsheet table into this notation is very time consuming. Is there an easy/quick way to do it? I'd imagine that someone's got a nice Excel macro or something similar to do this and I'd really appreciate it if someone can point me in the right direction. Thanks David

Ah, bad luck :( If you are okay in using Perl you can do the following: * Save the file as CSV from Excel * Run this perl onliner perl -p -i -e 's/,/\|/g' exportedfile.csv * Change the first line to have double pipes (||) instead of single since it is a heading in the file. * Copy the entire contents and put it into JIRA.

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Convert a spreadsheet into a table

David Stokes (david.stokes.nz@gmail.com)

I've got a table in an Excel spreadsheet with data in it and I would like to insert the table into a Jira issue.

I know that I'm able to use the Jira Text Formatting notation to put it into a nice table format as per below:

<th width="250">Notation</th><th>Comment</th>

||heading 1||heading 2||heading 3||
|col A1|col A2|col A3|
|col B1|col B2|col B3|

Makes a table. Use double bars for a table heading row.

The code given here produces a table that looks like:

<th>heading 1</th><th>heading 2</th><th>heading 3</th>

col A1	col A2	col A3
col B1	col B2	col B3

My problem is converting the spreadsheet table into this notation is very time consuming. Is there an easy/quick way to do it? I'd imagine that someone's got a nice Excel macro or something similar to do this and I'd really appreciate it if someone can point me in the right direction.

Thanks
David

Accepted answers

Renjith Pillai

Ah, bad luck

If you are okay in using Perl you can do the following:

Save the file as CSV from Excel
Run this perl onliner perl -p -i -e 's/,/\|/g' exportedfile.csv
Change the first line to have double pipes (||) instead of single since it is a heading in the file.
Copy the entire contents and put it into JIRA.

Murat Tinaz CBA

I know this is a very old question, but, if you also have MS-Word, you can copy the EXCEL table to Word first, and then copy the Word-table to JIRA. It keeps some table formatting.

All comments

AgentSmith

Greetings!

Just curious if you were aware or had an opportunity to review the following documentation:

https://confluence.atlassian.com/display/JIRA/Importing+Data+from+CSV

The CSV import wizard will present you with the ability to manually map values. Although this might not be perceived as a faster approach, it can make the process more intuitive.

Please let me know if you have any questions.

Cheers,

Jason | Atlassian

Renjith Pillai

David,

While I am not sure about an Excel macro for JIRA, there is a custom field that allows you to have Rich Text Editing (not Wiki). And I believe that you should be able to copy the Excel and just paste into that editor (have not tried it myself).

Try it out and let us know.

David Stokes (david.stokes.nz@gmail.com)

Hi Jason

My understanding of the importing from CSV is that it allows you to import from a spreadsheet into Jira issues.

This is different than what I'm wanting to achieve: I've got a Jira issue and what I want to do is store a table in a Jira field - not creating a Jira record for each row of the table.

Thanks

David

David Stokes (david.stokes.nz@gmail.com)

Hi Renjith

Thanks for you suggestion. You can paste in a range of cells from a spreadsheet, but you don't get any of the table formatting that you get if you use the Jira Text Formatting notation - you just get this:

heading 1heading 2heading 3

col A1col A2col A3

col B1col B2col B3

So i'm needing some thing that will convert the above from a spreadsheet into this:

||heading 1||heading 2||heading 3||
|col A1|col A2|col A3|
|col B1|col B2|col B3|

This then looks like this in Jira:

Thanks

David

Renjith Pillai

Ah, bad luck

If you are okay in using Perl you can do the following:

Save the file as CSV from Excel
Run this perl onliner perl -p -i -e 's/,/\|/g' exportedfile.csv
Change the first line to have double pipes (||) instead of single since it is a heading in the file.
Copy the entire contents and put it into JIRA.

David Stokes (david.stokes.nz@gmail.com)

Thanks for that - this is along the lines of what I was after. I'll need to play with the pearl because each line also needs a | at the start as well at the end.

Thanks for heading me in the right direction.

Cheers

David

David Stokes (david.stokes.nz@gmail.com)

Hi again Renjith

I'm not a programmer and Perl is way over my head

You obviously know your way around Perl - I'd really appreciate it if you could let me know what Perl command I should run to also add the | at the beginning and end of each line. For the heading lines I can enter the || manually. I've got a lot of data to bring load in and this would speed it up immensely.

Many thanks
David

Renjith Pillai

David, put the below into a file and execute it as

perl scriptname.pl input.csv outout.wiki

#!/usr/bin/perl
open FF,$ARGV[0];
my @data = &lt;FF&gt;;
close(FF);
open OO,"&gt;$ARGV[1]";
my $header = shift @data;
chomp($header);
$header =~ s/,/\|\|/g;
$header =~ s/\r//g;
print OO "||$header||\n";
foreach(@data)
{
        chomp;
        s/\r//g;
        s/,/\|/g;
        print OO "|$_|\n";
}
close(OO);

Sergei Emeliano

The best way is to use sed

sed -e 's/,/| /g' -e 's/^/|/g' -e 's/$/|/g' -e '1 s/|/||/g' &lt; your_xls_exported2.csv

Jorg Saretzki

I had a similar script, which also caters for commas within cells, e.g. in cases where a thousand separator is used in, say dollar values.

Example CSV:

seq,first,last,age,street,city,state,zip,dollar,pick
1,Mila,Goodman,72,Wefaco Extension,Guptohbe,OK,15564,"$8,719.59",WHITE
2,Wyatt,Lee,95,Jaru Center,Vahohzuw,GA,42634,"$1,280.55",YELLOW
3,Anthony,Evans,42,Ogeozi Turnpike,Ijulkec,DC,60351,"$3,977.66",WHITE
4,Vanessa,Riley,64,Icuto Pike,Huegonuk,IN,84785,"$2,202.65",GREEN
5,Quinn,Shelton,27,Wazo Road,Dojebjiz,WY,8482,"$1,935.27",WHITE

Command:

sed -e 's/,/|/g' -e 's/^/|/g' -e 's/$/|/g' -e '1 s/|/||/g' -e 's/"\(.*\)|\(.*\)"/"\1,\2"/g' example.csv

or shorter (saving a few bytes):

sed 's/,/|/g;s/^/|/g;s/$/|/g;1 s/|/||/g;s/"\(.*\)|\(.*\)"/"\1,\2"/g' example.csv

Limitation: Only one comma between quotes is considered. Therefore something like "$1,354,535" wouldn't work.

Jorg Saretzki

One more convenience. If you're on OS X, pipe the result to 'pbcopy' to paste the output to the clipboard ready to be pasted into Jira.

sed 's/,/|/g;s/^/|/g;s/$/|/g;1 s/|/||/g;s/"\(.*\)|\(.*\)"/"\1,\2"/g' example.csv | pbcopy

Bob Swift

If you have Confluence, use the CSV Macro or Excel Macro with parameter showWiki=true, then cut and paste the wiki markup into JIRA.

Amanda Burma2

Here's an example extracting against a spreadsheet, handling multiple commas within quotes, removing undesired columns as well as white space and some document information early in csv file that didn't want included in conversion. Comments included to explain various components. This example was created to parse a GoToWebinar survey report, and format it for a Confluence Pipe table.

#!/bin/bash
#$1 is the csv file to convert
filename=$1

# Run against folder with survey reports: for i in ./*.csv; do ./sed.sh $i; done
# Remove everything from header row and prior
#    sed '1,/Comments/ s/.*//'
# Remove any line breaks within a set of double quotes
#    sed -n 'H;g;/^[^"]*"[^"]*\("[^"]*"[^"]*\)*$/d; s/^\n//; y/\n/ /; p; s/.*//; h'
# Remove blank lines at top and bottom of file
#    sed '/^\s*$/d'
# Replace all commas with a pipe
#    sed 's/,/| /g;s/^/|/g;s/$/|/g;1 s/|/|/g'
# Replace any commas updated to pipes that reside within quotes back to commas
#    sed -E -e ':redo
#    s/^(([^"]*("[^\|"]*")?)*)("[^\|"]*)\|([^"]*")/\1\4,\5/
#    t redo' $filename.1 &gt; $filename.2
# Remove the first 3 columns (keeping columns 5-9) containing contact name and email 
#    cut -d\| -f5-9
# Add pipe to begin and end of every line
#    sed 's/^/\|/;s/$/ \|/'
# Append to file
#    &gt;&gt; surveyReport.txt

cat $filename | LC_ALL=C sed '1,/Comments/ s/.*//' | LC_ALL=C sed -n 'H;g;/^[^"]*"[^"]*\("[^"]*"[^"]*\)*$/d; s/^\n//; y/\n/ /; p; s/.*//; h' | \
LC_ALL=C sed '/^\s*$/d' | LC_ALL=C sed 's/,/| /g;s/^/|/g;s/$/|/g;1 s/|/|/g' &gt; $filename.1
LC_ALL=C sed -E -e ':redo
s/^(([^"]*("[^\|"]*")?)*)("[^\|"]*)\|([^"]*")/\1\4,\5/
t redo' $filename.1 &gt; $filename.2
cat $filename.2  | LC_ALL=C cut -d\| -f5-9 | LC_ALL=C sed 's/^/\|/;s/$/ \|/' &gt;&gt; surveyReport.txt

icebrg

Upload Excel spreadsheet as attachment to page.

Use Excel Macro to display spreadsheet and save.

Copy table while viewing the page.

Edit page and paste in copied table.

Delete Excel macro.

Morgan Smith

Some nice person has made this:

http://www.bluurgh.com/tableconverter/

Jeremiah Roque

smile

CelesteCS

Hello,

If you need to have that part of spreadsheets functionality, which regards any calculations, you may consider CelesteCS Math for Confluence. There are versions for both Confluence Server and Confluence Cloud.

Assume you have a table with three columns and three rows on your page – from A1 till C3, where row 1 is the header row. The sum calculation formula for rows 2 and 3 will be simple:

SUM(Table1.A2:C3)

There is a plenty of other functions in the macro, so you may perform any calculations using formulas in EXCEL style.

Thanks!

Daniel Hegner

If you happen to be on windows, here is a powershell script reading your pasted table from Excel:

Write-Host "Paste Excel table here. Hit enter when done."
$lines=while(($x=read-host) -ne ''){$x}
$newLines = $lines | % { 
    $newLine = $_ -replace "`t","|" 
    "|$newLine|"
}
$newlines[0] = $newLines[0] -replace "\|","||"
$newlines

Save it as Convert-ExcelTableToMarkup.ps1 or something.

Mark and copy your excel table.

Run Convert-ExcelTableToMarkup.ps1 in PS

Paste your excel table.

Copy the markup table and paste into JIRA.

Kate Hanna

This is awesome.

Troy_4bzzw1

Best yet!

Use the free online converter here: http://excel2jira.bluurgh.com/

You simply copy and paste the Excel table into the first text area, hit the big button, then copy the markup output from the second text area into Jira.

So many use cases:

Someone send you an email with a table, and that table needs to go into Jira, formatted correctly? Paste it into Excel first then use the tool.

Have an Excel table with requirements you want to show in a Jira description? Copy it into the converter.

Want to type a table into a Jira description, but don’t feel like dealing with all the markup? Create your Jira tables in Excel and use the tool.

Eddie Lau_asl2yk

Love this! Thanks Troy.

Subhash Gowda

Awesome. Way to go Morgan.

Murat Tinaz CBA

I know this is a very old question, but, if you also have MS-Word, you can copy the EXCEL table to Word first, and then copy the Word-table to JIRA. It keeps some table formatting.

Christian_u2uxo9

This is genius in a nutshell Works like a charm in Confluence Server 7.13

Be sure to drag&drop the Excel file onto your page before you add the Excel Macro. That way it will display the Excel in table form instead of an embedded file! (!)

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

cloud

jira-cloud

ms-excel

table