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

11 answers

1 accepted

This widget could not be displayed.

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

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.

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

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

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

Great David, you too have a great year ahead.

This widget could not be displayed.

Some nice person has made this:

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

This is awesome.

This widget could not be displayed.

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

This widget could not be displayed.

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.

This widget could not be displayed.

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

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.

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

This widget could not be displayed.
Jason Worley Atlassian Team Dec 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

This widget could not be displayed.

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

This widget could not be displayed.

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
This widget could not be displayed.

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!

This widget could not be displayed.

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.

This widget could not be displayed.

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.

Suggest an answer

Log in or Sign up to answer
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted Wednesday in New to Jira

Are you planning to trial, or are currently trialling Jira Software? - We want to talk to you!

Hello! I'm Rayen, a product manager at Atlassian. My team and I are working hard to improve the trial experience for Jira Software Cloud. We are interested in   talking to 20 people planning t...

88 views 1 0
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you