CSV table

[Ankit Mittal]

Hi,

I am trying to understand how the $$$text/csv parser works and as far as I can see its using the underlying Csvparser function in the javascript - 

$:/core/modules/parsers/csvparser.js

I changed the line 

var columns = lineText.split(",");

To 

var columns = lineText.split("#");

In order to see if instead of comma another character will yield a quick table but that did not work. What am i doing wrong and how can I achieve the intended result?

My motivation is this... as a programme manager, I rely on lot of excel files flowing in and from time to time I take snapshots of information and put as table on a tiddler. Problem is to convert that data into wiki table requires me to first paste the data on notepad++ then search and replace all tabs with | and then manually add | at beginning and end of the lines... 

I found the wonderful plugin by Joshua https://joshuafontany.github.io/TW5-JsonMangler/ where he mentions using CSV widget to parse the "character (not comma)" separated value data and a quick test on demo shows that tables can be created instantaneously by pasting data copied from excel into tiddler of type application/csv... however when I tried to invoke application by means of $$$application/csv it did not work.

I will really appreciate some guidance on how to make it work.

[Ton Gerner]

Hi Ankit,

Try http://skplugins.tiddlyspot.com/#TableHelper

Cheers,

Ton

[Ankit Mittal]

Thanks Ton. Its very helpful indeed. 

--
You received this message because you are subscribed to the Google Groups "TiddlyWiki" group.
To unsubscribe from this group and stop receiving emails from it, send an email to tiddlywiki+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/tiddlywiki/ecfea85e-b097-4c48-a120-96dca361ee29o%40googlegroups.com.

[TW Tones]

Ankit,

No need for javascript here, a man with a hammer sees everything as a nail?

If you paste the content of a csv file into a tiddler, or drop it on the wiki it is imported as csv. It is then surprisingly simple to parse it into separate tiddlers or directly to display a table. No extra manual work.

Also prior to the xml parser there is the https://tiddlywiki.com/#XLSX%20Utilities%20Edition that goes from EXCEL to tiddlers.

<table>
<$list filter="[[TransactionHistory.csv]get[text]splitregexp[\n]]" variable=row>
<tr>
   <$list filter="[<row>split[,]]" variable=cell>
        <td><<cell>></td>
   </$list>
</tr>
</$list>
</table>

This can be extended to obtain the first[] row as headings, and the "rest[]" and use that in the table.

However I in some cases I would be inclined to convert rows to tiddlers since the csv export can be used to send them back as a csv file.

Regards

Tony

[Eric Shulman]

On Tuesday, July 28, 2020 at 6:05:34 PM UTC-7, TW Tones wrote:

If you paste the content of a csv file into a tiddler, or drop it on the wiki it is imported as csv. It is then surprisingly simple to parse it into separate tiddlers or directly to display a table. No extra manual work.

<table>
<$list filter="[[TransactionHistory.csv]get[text]splitregexp[\n]]" variable=row>
<tr>
   <$list filter="[<row>split[,]]" variable=cell>
        <td><<cell>></td>
   </$list>
</tr>
</$list>
</table>

This is actually not sufficient to handle all CSV output.  If a "cell" in CSV *contains a comma as content*,

then the entire cell content is wrapped in quotes, like this:

foo, bar, "baz, has a comma", mumble, frotz, "gronk has one, too", snork, snerfle

which contains cells:

baz, has a comma

and

gronk has one, too

Unfortunately, CSV doesn't use quotes around *every cell* (i.e., even the ones without commas),

so it's not possible to incorporate simple split[", "] handling into the filter logic.  

Even worse, a cell can contain literal quotes as well, which are automatically *doubled* to distinguish

them from quotes used as cell delimiters... and they don't even have to be matched pairs of quotes!

Thus:

foo, bar, "baz, has ""a"" comma", mumble, ""frotz"", "gronk has ""one quote, too", snork, snerfle

contains cells:

baz, has "a" comma

and

"frotz"

and

gronk has "one quote, too

To make the problem yet still more complicated, cell values in CSV can also contain literal newlines which,

similar to cells containing commas, are also contained within quotes as delimiters.

Thus, this can be *one* row in CSV:

foo, bar, "baz, has ""a"" comma", "mumble
""two"" lines, and a comma", ""frotz"", "gronk has ""one quote, too", snork, snerfle

which contains the cell:

mumble
"two" lines, and a comma

So it's not even possible to use splitregexp[\n] to get each row of the input, unless you first recognize when

a newline is within a quoted cell value.

Back in the TWC days, I wrote a javascript-based handler that accounted for these difficult cases, but the

code wasn't trivial... here's a link to my code: http://tiddlytools.com/#CreateTiddlersFromCSV

As you can see, I had to go to great lengths to juggle the input, converting embedded commas and

newlines into placeholders !~comma~! and !~newline~! just to be able to split it into lines and cells,

and then convert the placeholders back to embedded literals when processing the cell values.

I suppose something similar *might* be possible using TW5 filter logic, but it's certainly a lot more

complicated than the algorithm you indicated.

Sometimes, you just need a scalpel instead of a hammer, even if there are nails to deal with!

-e

[TW Tones]

Eric, Ankit,

Sorry, I may have made an ass out of my self by ass'uming. 

As you point Eric out the csv needs to be "strict" and can't contain complex data in cells such as additional commas. 

I don't dispute what you are saying only that there are many cases where this simple and strict CSV standard is available so a simple parser works. This is even more so the case if you control the source of the CSV records because you can ensure they comply with these simple standards.

Ankit - If your need varies from this simple standard, do ask for a fix, otherwise a more robust solution like Eric's is essential.

Regards

TW Tones

[Ankit Mittal]

Thanks chaps, for this discussion... helps me understand a few things. 

Although I still dont understand that when i change the comma in the underlying javascript of the parser why it does not honor the change...

For my problem of excel workflow, what i need is just the simple requirement to parse data that is tab separated rather than comma separated. The work done by Joshua already does the heavy lifting so i am going to try and create a editor button that opens a modal asking for user input and then create a tiddler of type application/csv using that user input and transclude it on current tiddler... something like excise but with ability to provide application type can do that too... just not sure if i am setting a goal too high.

Would either of you gentleman kindly point me to the code behind excise button please? 

Regards,

Ankit

--
You received this message because you are subscribed to the Google Groups "TiddlyWiki" group.
To unsubscribe from this group and stop receiving emails from it, send an email to tiddlywiki+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/tiddlywiki/dc9f599f-0cfa-42e4-aaa0-a99419ba8d45o%40googlegroups.com.

[Stephen Kimmel]

Ankit,

When I first developed Tablehelper http://skplugins.tiddlyspot.com/#TableHelper (which predated the XLSX and the regex abilities in TW) I was copying a selected area in an Excel file and pasting it into a tiddler. That data came across as lines with tabs separating the individual cells. What I wanted a TW table and not just something that looked like a table in TW. I later added the ability to convert CSV files which are comma separated cells rather than tab separated cells so I added that capability to my routine. As Eric points out, though, some cells contain commas and other things that can trip up simple regex statements. I ultimately decided that the result wasn't going to be perfect, just easier to finish converting manually. So I added the option to do only tab separated fields which it sounds like what you need.

One point to remember, if you are altering a javascript routine, you have to save and refresh the wiki for the change to take effect.

Stephen

[Ankit Mittal]

Hi Stephen,

First thanks for creating the ever so helpful tool. It is very useful indeed. 

I was however hell bent on making a button that makes use of the "jsonmangler" plugin and I did actually manage to create one this afternoon. It's nowhere near as polished as what you have created but it works.

In case anyone is interest, I have included the 4 tld files here that can simply be loaded on to the demo site for jsonmangler and then to test follow these steps:

1. Create a new tiddler

2. Copy some excel data

3. Paste data in new tiddler

4. Select the pasted data

5. Click on spiral button that appears on the editor buttons.

6. Give a name to this table

7. Save the tiddler to see the table created.

above steps in pictures:

To use it on a local wiki ofcourse one will have to install jsonmangler plugin. It however does not beat the lightweight simplicity of  http://skplugins.tiddlyspot.com/#TableHelper 

Thanks a lot for all the help guys. You are all so very kind. :)

--
You received this message because you are subscribed to the Google Groups "TiddlyWiki" group.
To unsubscribe from this group and stop receiving emails from it, send an email to tiddlywiki+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/tiddlywiki/4874fef6-a93d-4a93-80b0-31c1ea38ebf7o%40googlegroups.com.

[Joshua Fontany]

Well done!

Each transclusion of an 'application/csv' tiddler gets its own UNIQUE state tiddler (all of the options that you can under the first double-chevron).
This means that you can set each transclusion of a CSV to have its own "lines per page", "preview or show all" etc.

IF you want to, you wan wrap the transclusion in a classed Div, like so:

```
<div class="hide-csv-controls">

{{CSVTIDDLERS/DATA12345}}

</div>
```

Then, by adding and removing the tag `$:/tags/Stylsheet` from a tiddler with the following text:
```

.hide-csv-controls   .tc-csv-options { display: none; }

.hide-csv-controls   .tc-csv-controls { display: none; }

```
You can hide the controls on those transclusions (thus not bothering the reader with things they don't need to see).

OR split that CSS up into two tiddlers and just hide the Options (keeping the page controls visible). OR change the class name so you can customize what is shown or hidden.

Best,
Joshua Fontany

[Ankit Mittal]

Thanks for the kind words Joshua and for the wonderful plugin and not to forget the tips you have just provided. 

As I am the only user for this and I dont mind those settings appearing on the table infact I quite like them. That said someone else might not want to show those and so if I can I will try adding that option.

Once again thanks for the jasonmangler plugin - I absolutely love it.

To view this discussion on the web visit https://groups.google.com/d/msgid/tiddlywiki/83d0b67c-437a-428f-8a66-f6ceb49290afn%40googlegroups.com.

[Eric Shulman]

On Sunday, August 2, 2020 at 2:30:49 AM UTC-7, Ankit Mittal wrote:

Once again thanks for the jasonmangler plugin - I absolutely love it.

LOL! "jasonmangler" sounds like a reference to Jason Voorhees, the main a character in the "Friday the 13th" series of horror films.  In contrast, "JSON" (note the capitalization) stands for "JavaScript Object Notation".

I just found that kind of amusing.  Thanks for the chuckle!

-e

[Ankit Mittal]

Lol... yep thats my autocorrect working overtime 🤣😂

--

You received this message because you are subscribed to the Google Groups "TiddlyWiki" group.
To unsubscribe from this group and stop receiving emails from it, send an email to tiddlywiki+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/tiddlywiki/f6569c0d-81eb-4d28-922f-35e4b92156c5o%40googlegroups.com.