Table from CSV Data Block

[Mohammad]

How can one create a WikiText table from a copied CSV block, such as data pasted from an Excel sheet?

One can create an inputbox and then process the input by replacing the csv delimiters like comma or tab into pipe and then copy the result to clipboard. Below is a small UI that help to create a wikitext table from csv block. It accepts both tab and comma as delimiter.

Code

\procedure convert-csv()
<!-- create the setting row -->
CSV delimiter:&nbsp;
<$tiddler tiddler="$:/temp/convertxls">
<$radio field=delimiter value="tab" default="tab"> tab</$radio>
<$radio field=delimiter value="comma" default="tab"> comma</$radio>
</$tiddler>&nbsp;|&nbsp;
<$checkbox tiddler="$:/temp/convertxls" field="header" checked="yes" unchecked="no" default="no">&nbsp;Has header?</$checkbox><br/>

<!--Create the input textbox -->
<$macrocall $name="copy-to-clipboard-above-right" src=<<output>> />
<$edit-text tiddler="$:/temp/convertxls" tag=textarea class="tc-max-width"/>
<!-- Preview output checkbox-->
<$checkbox tiddler="$:/temp/convertxls" field="preview" checked="open" unchecked="closed" default="closed"> Prview?</$checkbox>

<!--Define required variables-->
<$let
  newline={{{ [charcode[10]] }}}
	th=     {{{ [<newline>addprefix[h]] }}}
  tab=    {{{ [charcode[9]] }}}
	delim=  {{{ [[$:/temp/convertxls]get[delimiter]match[comma]then[,]else<tab>]  }}}
	header= {{{ [[$:/temp/convertxls]get[header]match[yes]else[no]] }}}
  input=  {{$:/temp/convertxls}}
  output= {{{ [<input>split<newline>!is[blank]search-replace:g:<delim>,[|]addprefix[|]addsuffix[|]join<newline>] }}}
	output= {{{ [<header>match[yes]]:then[<output>search-replace<newline>,<th>]:else[<output>] }}}}  >
	<!-- Preview output-->
	<%if [[$:/temp/convertxls]get[preview]match[open]] %>
		<$transclude $variable="output" $mode="block"/>
	<%endif%>
</$let>
\end

<!-- Example-->
<<convert-csv>>

To give a try:

• Download Table from CSV.json (1.6 KB)
• Go to https://tiddlywiki.com
• Drag and drop the downloaded code into into https://tiddlywiki.com
• Paste a chunk of data from Excel, Google Sheet or a CSV file
• Click on copy to clipboard and paste the result in any tiddler you like
• You may click on preview and see the result before use

Screencast

References

• Shiraz 2.9.7 — create stylish contents in Tiddlywiki
• Is there a tool to create a table by inserting values either as a block copied from excel or colums by column - Discussion - Talk TW
• tb5 — Welcome
• Special thanks to @stobot Is there a tool to create a table by inserting values either as a block copied from excel or colums by column - #3 by stobot

Revision II

Based on comments from @Eskha and @Springer, a second version is given below, It

• handles entries with comma where given inside '...', and "..."
• has working copy-to-clipboard at the bottom of textbox
• the code is more complex with more computation steps

Table from CSV - Rev02.json (1.8 KB)

[Mohammad]

If do not need the converted csv and just want to wikify a csv data block into a table, the best solution is Shiraz quick tables (csv-table). Look at:

https://kookma.github.io/TW-Shiraz/#Tutorial%20Quick%20Tables

[AlfieA]

Well, @Mohammad, this is excellent work.
input= {{$:/temp/convertxls}} is where the pasted data goes before being processed?
And…

<$radio field=delimiter value="tab" default="tab"> tab</$radio>
<$radio field=delimiter value="comma" default="tab"> comma</$radio>

Could this work, I wonder:
<$radio field=delimiter value="/" default="tab"> comma</$radio> for a file path listing?

[Mohammad]

@AlfieA

Could this work, I wonder:
<$radio field=delimiter value="/" default="tab"> comma</$radio> for a file path listing?

Use

<$radio field=delimiter value="slash" default="tab"> slash</$radio>

And then replace

delim=  {{{ [[$:/temp/convertxls]get[delimiter]match[comma]then[,]else<tab>]  }}}

with

	rdelim = {{{ [[$:/temp/convertxls]get[delimiter]] }}}
	delim=  {{{ [<rdelim>match[comma]then[,]][<rdelim>match[slash]then[/]] :else[<tab>] }}}

Here in delim you can process any number of delimiters.

[Eskha]

Hello Mohammad,

Useful tool.

In my TiddlyWiki, the Copy to macro part <$macrocall $name="copy-to-clipboard-above-right" src=<<output>> /> only works if I put it inside the $let widget where the output variable is defined.

BR,

Eskha

[Mohammad]

@Eskha

n my TiddlyWiki, the Copy to macro part <$macrocall $name="copy-to-clipboard-above-right" src=<<output>> /> only works if I put it inside the $let widget where the output variable is defined.

Good catch! It should indeed be placed inside the $let. I will update initial post later this weekend. One issue that has arisen is that if the textbox is manually edited, the focus is lost I need to see how to address that.

[Springer]

@Mohammad,

Hello! I was just looking for a tool like this, and happy to see your progress!

The flavor of CSV that I inherit from my university is comma-delimited, but always includes double-quote framing around each field value — the purpose of which is to flag the value as a text string and to allow commas within the contents of a cell.

This is important for being able to have field contents like "LastName, FirstName".

The tool as currently set up does not recognize such a role for quote-marks around string values, so it treats the comma before FirstName as requiring a new column.

And this might be just a minor inconvenience (extra columns), except that not all fields have the same number of commas!

For example, a column in this CSV with the “Majors” heading can show "ECON,GOVT,PSYC" for one student, and "" (= no major yet) for another. All columns to the right of that column are thus not properly aligned if the comma is treated as ending a cell.

As always, your development work is much appreciated!!

Data-redacted example:

"UniversityID","Name","Pronouns","Remote","Class Year","Credit","Grade","Email","Crosslisting","Status","Major(s)"
"998999","Abernathy,Acorn","he, him, his","","2029","1","CR/U","aabernathy@university.edu","COURSE 299","Advisor Approved",""
"989899","Bronsky,Bedard","","","2025","1","A-F","bbronksy@university.edu","COURSE 299","Advisor Approved","ECON,GOVT,PSYC"

[Mohammad]

Please try below revised code. It processes framed entries within ' and, " at the cost of more complex code.

\procedure convert-csv()
<!-- create the setting row -->
CSV delimiter:&nbsp;
<$tiddler tiddler="$:/temp/convertxls">
<$radio field=delimiter value="tab" default="tab"> tab</$radio>
<$radio field=delimiter value="comma" default="tab"> comma</$radio>
</$tiddler>&nbsp;|&nbsp;
<$checkbox tiddler="$:/temp/convertxls" field="header" checked="yes" unchecked="no" default="no">&nbsp;Has header?</$checkbox><br/>

<!--Create the input textbox -->
<$macrocall $name="copy-to-clipboard-above-right" src=<<output>> />
<$edit-text tiddler="$:/temp/convertxls" tag=textarea class="tc-max-width"/>
<!-- Preview output checkbox-->
<$checkbox tiddler="$:/temp/convertxls" field="preview" checked="open" unchecked="closed" default="closed"> Prview?</$checkbox>

<!--Define required variables-->
<$let
  newline={{{ [charcode[10]] }}}
	th=     {{{ [<newline>addprefix[h]] }}}
  tab=    {{{ [charcode[9]] }}}
	comma = `,(?=(?:[^'"]|'[^']*'|"[^"]*")*$)`
	delim=  {{{ [[$:/temp/convertxls]get[delimiter]match[comma]then<comma>else<tab>]  }}}
	header= {{{ [[$:/temp/convertxls]get[header]match[yes]else[no]] }}}
  input=  {{$:/temp/convertxls}}
  output= {{{ [<input>split<newline>!is[blank]search-replace:g:regexp<delim>,[|]] :map[split[|]trim[]trim["]trim[']join[|]] :and[addprefix[|]addsuffix[|]join<newline>] }}}
	output= {{{ [<header>match[yes]]:then[<output>search-replace<newline>,<th>]:else[<output>] }}}}  >
	<!-- Preview output-->
	<%if [[$:/temp/convertxls]get[preview]match[open]] %>
		<$transclude $variable="output" $mode="block"/>
	<%endif%>
</$let>
\end

<<convert-csv>>

@Eskha the copy to clipboard still does not work.

[Mohammad]

Example

[Mohammad]

Try the below one, it works, but I had to put the copy to clipboard below the textbox

\procedure convert-csv()
<!-- create the setting row -->
CSV delimiter:&nbsp;
<$tiddler tiddler="$:/temp/convertxls">
<$radio field=delimiter value="tab" default="tab"> tab</$radio>
<$radio field=delimiter value="comma" default="tab"> comma</$radio>
</$tiddler>&nbsp;|&nbsp;
<$checkbox tiddler="$:/temp/convertxls" field="header" checked="yes" unchecked="no" default="no">&nbsp;Has header?</$checkbox><br/>

<!--Create the input textbox -->
<$edit-text tiddler="$:/temp/convertxls" tag=textarea class="tc-max-width"/>
<!-- Preview output checkbox-->
<$checkbox tiddler="$:/temp/convertxls" field="preview" checked="open" unchecked="closed" default="closed"> Prview?</$checkbox>

<!--Define required variables-->
<$let
  newline={{{ [charcode[10]] }}}
	th=     {{{ [<newline>addprefix[h]] }}}
  tab=    {{{ [charcode[9]] }}}
	comma = `,(?=(?:[^'"]|'[^']*'|"[^"]*")*$)`
	delim=  {{{ [[$:/temp/convertxls]get[delimiter]match[comma]then<comma>else<tab>]  }}}
	header= {{{ [[$:/temp/convertxls]get[header]match[yes]else[no]] }}}
  input=  {{$:/temp/convertxls}}
  output= {{{ [<input>split<newline>!is[blank]search-replace:g:regexp<delim>,[|]] :map[split[|]trim[]trim["]trim[']join[|]] :and[addprefix[|]addsuffix[|]join<newline>] }}}
	output= {{{ [<header>match[yes]]:then[<output>search-replace<newline>,<th>]:else[<output>] }}}}  >
	<$macrocall $name="copy-to-clipboard-above-right" src=<<output>> />
	<!-- Preview output-->
	<%if [[$:/temp/convertxls]get[preview]match[open]] %>
		<$transclude $variable="output" $mode="block"/>
	<%endif%>
</$let>
\end

<<convert-csv>>

[Springer]

Quick test: it seems to work perfectly!

Thanks!!

[Springer]

@Mohammad

Try the below one, it works, but I had to put the copy to clipboard below the textbox

OK, works now, to get wiki-style pipe-structure tables to clipboard.

Fantastic!

[Mohammad]

The Revision II also introduces a div around the generated table in the preview to prevent horizontal overflow.

[Scott_Sauyet]

This is great! It will reduce a bunch of manual processing I tend to do before importing data.

But, in the category of no-good-deed-goes-unpunished, there is one more trick that I think would make it hit 99.9% of all CSVs in the wild: If you want to include quotation marks inside your quoted field, you need to double them. In the below, the extraction should give the tags for Betty of A, "quoted", D, since the doubled quotes inside the single one really represents one quote.

name,age,tags,shoe-size
Barney,25,"B, C, D",9
Betty,27,"A, ""quoted"", D",6
Fred,26,"A, B",12.5
Wilma,24,"B",7

If you don’t get to this soon, I may see if I can fix it, but I will have no time for anything like this until at least Sunday.

[Mohammad]

@Scott_Sauyet

If you don’t get to this soon, I may see if I can fix it, but I will have no time for anything like this until at least Sunday.

Yes, please go ahead!

[AlfieA]

@Mohammad
Do you know what I really like about your work: STC; Stand-alone Tiddler Coding.