Convert a spreadsheet into a table

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:

<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

12 answers

2 accepted

2 votes
Answer accepted
Renjith Pillai
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 28, 2012

Ah, bad luck :(

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

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

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

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 29, 2012

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);

Thanks so much Renjith!

I've run the script as above.

input.csv contains:

A,B,C,D

1,2,3,4

5,6,7,8

outout.wiki after running the script contains:

||A||B||C||D1||2||3||45||6||7||8||

It's as though it isn't recognising the end of the line and is doing everything as the header line.

I feel really guilty about taking anymore of your time, but it seems like we are so close to getting this right.

Many thanks again
David

BTW - I'm running this on a Mac and I'm not sure that would have any impact on it.

Renjith Pillai
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 29, 2012

I did some handling for the Windows CR in that script, but I am not sure how exactly Excel exports the CSVs. Can you attach a sample csv? You need to find a place to host it.

I've saved the files into a folder in my DropBox account which you can access at:https://www.dropbox.com/sh/uo2pic9obehd0gi/1_706UE9L0

Thanks
David

Hi

You are exactly right - I've created a Windows csv file and it works perfectly.

Any ideas on what I need to change to make it work on a Mac?

Thanks
David

Renjith Pillai
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 30, 2012

The file seems to have only CR, no LF. Let me check it.

Renjith Pillai
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 30, 2012

Use this

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

You've nailed it - thanks so much.

Have a great new year!

Cheers
David

Renjith Pillai
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 30, 2012

Great David, you too have a great year ahead.

0 votes
Answer accepted
Murat Tinaz CBA February 11, 2021

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.

7 votes
Morgan Smith February 22, 2016

Some nice person has made this:

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

Jeremiah Roque March 14, 2016

smile

Kate Hanna January 10, 2017

This is awesome.

Subhash Gowda May 22, 2019

Awesome. Way to go Morgan. 

2 votes
Troy June 26, 2017

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 January 30, 2019

Love this!  Thanks Troy.

2 votes
icebrg September 10, 2015

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.

Christian October 1, 2021

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! (!) 

2 votes
Bob Swift OSS (Bob Swift Atlassian Apps)
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
June 25, 2014

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

1 vote
Sergei Emelianov March 12, 2014

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 June 25, 2014

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 June 25, 2014

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

0 votes
Daniel Hegner October 6, 2016

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.

0 votes
CelesteCS July 20, 2016

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!

0 votes
Amanda Burma June 5, 2015

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
0 votes
Renjith Pillai
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 28, 2012

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.

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

0 votes
AgentSmith
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
December 28, 2012

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

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

Like Christian likes this

Suggest an answer

Log in or Sign up to answer