Converting from Excel files to Twiki tables

Twiki supports tables without the need for HTML tags. A twiki table consists of items delimited by a vertical bar: "|". Example:

A B C
1 2 3

Raw text:

|A|B|C|
|1|2|3|

Creating a header row:

A B C
1 2 3

Raw text:

|*A*|*B*|*C*|
|1|2|3|

Conversion Instructions

It is possible to convert an Excel file (or equivalent) to a Twiki table by exporting the file to a .CSV (comma separated values) and using a program like Notepad++ to replace those comma delimiters with a vertical bar delimiter and create a Twiki table in as little as a minute.

  • First, open the .XLS or .XLSX file with Excel or an equivalent program.
    • Optional but recommended: ensure that none of the sheet's text fields includes a comma! Commas in automatically formatted numbers (i.e. "1,000") are fine. Excel will use quote marks to surround text with commas upon converting to .CSV, in effect making the cell text a literal string. These quote marks will show up in your converted table.
  • Modify the table in Excel:
    • Add one column to the left of the first column in the table. For example, to the left of column A. Type the word "remove" in the top cell of this column. Leave the other cells in the column empty.
    • Modify one column to the right of the last column in the table. Type the word "remove" in the top cell of this column. Leave the other cells in the column empty.
  • Save the sheet in Excel as a .CSV file. This option is at File > Save As > [select .CSV file extension] in most spreadsheet programs.
  • Open the .CSV file with an advanced raw text editor like Notepad++.
Your table should be formatted something like this:
remove,Alpha,Beta,Gamma,Delta,remove
,one,,,four,
  • Use the Search > Replace dialog (or equivalent) to replace commas with vertical bars, literally replacing "," with "|". Replace all commas.
Your table should be formatted something like this:
remove|Alpha|Beta|Gamma|Delta|remove
|one|||four|
  • Now remove the "remove" text from the beginning and end of the first line of text.
Your table should be formatted something like this:
|Alpha|Beta|Gamma|Delta|
|one|||four|
  • Your table is now ready to paste into Twiki, which will automatically format it so long as the vertical bars are in the place you intend.
    • Optional: Use the bold tag, i.e.
      *bold*
      , on each item in the top row to create a heading row on your table.
Your table should be formatted something like this:
Alpha Beta Gamma Delta
one four

Bonus: using the spreadsheet plugin (TWikiSheetPlugin):

This wiki supports spreadsheet-like table editing through an installed plugin (TWikiSheetPlugin). To learn more about using this plugin or advanced plugin features, visit http://twiki.org/cgi-bin/view/Plugins/TWikiSheetPlugin .

  • Edit a page with a Twiki table with a header row, or create a table with a header row. See instructions above.
  • Check that the page has a title heading at the top (three dashes, one plus sign, and a short text title for the page).
  • On an empty line between the page title text and the first row of the table, insert the following text:
     %TWIKISHEET{ }% 
  • The page should look something like this:
    ---+ Page Title
    %TWIKISHEET{ }%
    |*Alpha*|*Beta*|*Gamma*|*Delta*|
    |one|||four|
     
  • Save the changes.
  • Check that the table rendered correctly on the page. Your table should now have a small edit button allowing you to edit cell contents.
-- JosephBreithaupt - 2017-02-16
Topic revision: r2 - 2017-02-24 - JosephBreithaupt
 
This site is powered by the TWiki collaboration platform Powered by PerlCopyright © 2008-2017 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback