Convert a spreadsheet into a table

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

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

[Renjith Pillai · Accepted solution]

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.

[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 = <FF>;
close(FF);
open OO,">$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);

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

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]

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.

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

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

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

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]

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

[Renjith Pillai]

Use this

#!/usr/bin/perl
use strict;
my $contents;
{
local $/;
open FF,$ARGV[0];
$contents = <FF>;
close(FF);
}
my @data = split(/\r/,$contents);
open OO,">$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);

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

You've nailed it - thanks so much.

Have a great new year!

Cheers
David

[Renjith Pillai]

Great David, you too have a great year ahead.

[Murat Tinaz CBA · Accepted solution]

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.

[Morgan Smith]

Some nice person has made this:

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

[Jeremiah Roque]

[Kate Hanna]

This is awesome.

[Subhash Gowda]

Awesome. Way to go Morgan. 

[Troy]

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]

Love this!  Thanks Troy.

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

[Christian]

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

[Bob Swift (personal)]

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

[Sergei Emelianov]

The best way is to use sed

sed -e 's/,/| /g' -e 's/^/|/g' -e 's/$/|/g' -e '1 s/|/||/g' < 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

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

[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!

[Amanda Burma]

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 > $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
#    >> 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' > $filename.1
LC_ALL=C sed -E -e ':redo
s/^(([^"]*("[^\|"]*")?)*)("[^\|"]*)\|([^"]*")/\1\4,\5/
t redo' $filename.1 > $filename.2
cat $filename.2  | LC_ALL=C cut -d\| -f5-9 | LC_ALL=C sed 's/^/\|/;s/$/ \|/' >> surveyReport.txt

[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 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

[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

[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