• View
• Edit
• History
• Attach
• Print
• Backlinks

CSVInclude

Goal

Large, complex tables are a pain to maintain in mark-up (wiki or HMTL). It would be nice to have a straightforward way to maintain tables in a spreadsheet tool and then include them directly into the wiki.

Solution

CSVInclude.phpΔ

Put CSVInclude.php in the PmWiki cookbook/ directory (you may have to create it).

Add the following line to local/config.php:

 include_once("cookbook/CSVInclude.php");

Use mark-up like [:attachcsv <csvfile>:] to include a CSV file in your wiki page. (See Usage Notes below for more options.)

Discussion

Wiki mark-up is great for simple tables but they get hairy to create and maintain where there are a lot of columns or the cell contents are long. In these cases, it really is more natural to maintain the table contents in a spreadsheet tool and simply include the table into the wiki.

The CSVInclude cookbook file allows you to upload a CSV file you've exported from a spreadsheet and have it appear as part of your wiki page. Cells can contain wiki mark-up just as if the text were between the "||" (double pipe) mark-up in a simple wiki table. This also means that you can't use block mark-up in your tables (i.e. no bullets, headers or indentation).

Why CSV vs. Excel or SYLK or something like that? Well CSV is pretty much the lowest common denominator for spreadsheet file formats and because there are CSV parsing routines already in PHP. Also, dealing with Excel or some more "advanced" file format would involve resolving formulas and multiple sheets within the same file, which I really don't want to deal with. It's also reasonable to edit CSV by hand or post process CSV by hand in a text editor, which is pretty much impossible in other formats.

The CSVInclude currently uses Table mark-up, if for no other reason, because advanced table mark-up is currently not available for PmWiki2. It's also a nice choice because of mark-up goodies like headers and alignment which would be a lot more work in the advanced table mark-up. In general, it seems that the Table mark-up matches with CSV pretty well.

I haven't done extensive testing with all the possible CSV output that may be possible. In particular I've only really tested this with CSV output from OpenOffice, so feedback on how it works (and doesn't work) with other spreadsheets would be much appreciated.

Usage Notes

Getting The CSV File In-line

1. Create a spreadsheet with your favourite spreadsheet tool (OpenOffice for instance), and export it as text (CSV) format. The important thing is that cells should be separated by comas (,) and delimited by double quotes (").
2. On some wiki page add mark-up to include the CSV table. The simplest format is
   [:attachcsv <csvfilename>:]
   but see below for the full syntax
3. Save the page to get see an unresolved Attach:<csvfilename> link, which will take you to the file upload page.
4. Upload the CSV file
5. Click the page title link at the top of the upload page to get back to the original page
6. Checkout your table as part of the wiki page

Wiki Page Mark-up

The full mark-up is as follows: :[:attachcsv <csvfilename>'<table attributes>':] expire=<max_age_in_seconds>

Maybe some example would help:

• [:attachcsv pnl.csv:] — include the PnL table into the page
• [:attachcsv pnl.csv'border=1 cellpadding=3 cellspacing=0':] — put thin border around cells
• [:attachcsv pnl.csv'class=pnltable':] — apply CSS class "pnltable" to table appearance
• [:attachcsv pnl.csv'class=pnltable' expire=600:] — apply CSS class "pnltable" to table appearance; set max age to 10 minutes (600 seconds)

Mark-up within CSV tables

The contents of the CSV file are inlined into the wiki text of the page, so you can include wiki mark-up in your CSV cells. Anything that will work between double bar table mark-up in the wiki will work within the cells of the CSV table.

A couple of special mark-ups are available in the CSV table:

• To join a cell with the previous one (create a colspan) put <<del>> as the only text in the cell (same as leaving a simple table cell empty)
• using a pair of backslashes (\\) within the text of a cell will create a line break
• any sequences of pipe characters (||...) are escaped (i.e. they don't "magically" create sub cells within the table)

(NEW in version 1.5!) Remote inclusion

CSV Include now supports remote CSV files. Just put a URL (beginning with http: or https:) in place of the file name. (This works with any CSV file accessible via a URL, including the output of CSV Action, URLs generated by the CSV export of Google Docs, etc.)

(NEW in version 1.6!) Local caching of remote CSVs

CSV Include now caches remote CSVs (see above) locally (to avoid querying the remote server, and waiting for the .csv to be downloaded, with every single page load). Several config variables (which may be set in config.php, prior to including CSVInclude.php, control this behavior:

• $CSVIncludeLocalCachingEnabled (may be true or false; defaults to true)—enable or disable local caching of remote CSVs
• $CSVIncludeLocalCacheFolder (defaults to uploads/csvincludecache/)—where to store the cached files
• $CSVIncludeDefaultCacheExpiration (defaults to 300, i.e. 5 minutes)—the cached version of a remote CSV will be used (instead of retrieving the remote file anew) if it at most this many seconds old

(Note that cached files are identified by an md5 hash of the full URL; any difference in the URL is interpreted as a totally different file.)

Optionally, you can specify a custom expiration time (i.e., maximum cached file age) via the [:attachcsv:] markup itself, by placing an expire= parameter (e.g., expire=600 for 10 minutes) at the end of the markup. This overrides the default expiration time. (See examples above.)

You can also specify an expiration time of 0 to disable local caching for that particular CSV (i.e., that particular instance of the [:attachcsv:] markup) only.

Manual invalidation of cached files

If you append ?csvinclude_clear_cache=1 to the URL of a page with one or more [:attachcsv:] markups on it that refer to remote CSVs, and if you have edit authorization for that page, then all the locally cached versions (if any) of the remote CSVs referred to on that page only will immediately be deleted. The page will then be reloaded, and new versions of the remote CSVs will be retrieved (and cached, if caching is enabled).

Updates

• Version 2020-06-24:
  • Now follows redirects when caching is enabled (this can be disabled by setting $CSVIncludeFollowRedirects to false in config.php).
  • Updated for compatibility with PHP 7.
  • Bug fixes.

See Also

• ExcelPaste - cut-and-paste data from Excel
• Text2Tbl - Convert structured text (including CSV) into either simple tables or advanced table directives

Contributors

• LarryBaltz
• Said Achmiz

Comments

See discussion at CSVInclude-Talk