[tw] [TW5] Import a CSV file... how actually?

[Mat]

Hm, I can swear I've had this working previously but now I can't remember;

How do I actuallly import a csv file so it becomes separate tiddlers? I
only get one tiddler that literally reads:

"title","text","modified","created","tags"
"A new tid","some text","20160108130129287","20160108130113268","[[a tag]]"

..and just more in that one tiddler if it's multiple (supposed) tiddlers.

Thanx

<:-)

[Jed Carty]

This doesn't really answer your question, but you gave me an idea that may
be useful.

Would people find a plugin that lets you paste text into a tiddler from a
csv or some other file type that can hold tiddler data and then have the
pasted text converted into tiddlers?
Making a very simple (and probably full of bugs) version shouldn't be too
hard.
I am not sure how useful it would be because you would still need to copy
and paste text.

Almost completely off topic:
It just occurred to me that this could go both ways, we could pretty easily
make a utility that takes tiddlers returned by a filter and put them into
the format used by .tid files using only wikitext. This is already done by
the export mechanism but we want to put the output into the text field of a
tiddler so it is easy to copy between wikis. I guess that is just the
tiddler swarm idea from a while ago where we make something like a
temporary plugin that is just used to import a group of tiddlers from one
wiki to another. But the same utility could work with multiple formats
(.tid, csv, json, etc.) to make life easier.

[Mat]

Hi Jed

well, considering my inability as described I think you have at least my
answer. But then maybe the answer to my question, i.e how do I import a csv
file into separate tiddlers, is really trivial so...

Post by Jed Carty
This doesn't really answer your question, but you gave me an idea that may
be useful.
Would people find a plugin that lets you paste text into a tiddler from a
csv or some other file type that can hold tiddler data and then have the
pasted text converted into tiddlers?
Making a very simple (and probably full of bugs) version shouldn't be too
hard.
I am not sure how useful it would be because you would still need to copy
and paste text.
It just occurred to me that this could go both ways, we could pretty
easily make a utility that takes tiddlers returned by a filter and put them
into the format used by .tid files using only wikitext. This is already
done by the export mechanism

Actually, I believe currently we're limited to exporting one tiddler or
all, or in the case of .tid only one. I wrote an issue on it #1687 -
<https://github.com/Jermolene/TiddlyWiki5/issues/1687>so exporting based on
a filter should definitely be valuable.

Post by Jed Carty
but we want to put the output into the text field of a tiddler so it is
easy to copy between wikis.

Hm, if we're going to merely transfer it between TWs, then is there a point
in converting it to another format first? I'm probably misunderstanding
something.

Post by Jed Carty
I guess that is just the tiddler swarm idea from a while ago where we make
something like a temporary plugin that is just used to import a group of
tiddlers from one wiki to another. But the same utility could work with
multiple formats (.tid, csv, json, etc.) to make life easier.

I'm vaguely reminded of @Andreas Hahns Tinka plugin packer. But what wuold
be neat is a quicker thing where you, for instance, filter out some
tiddlers and this generates a temporary single link that can be immediately
pulled into some other TW. ...where I guess it would ideally be interpreted
as multiple tiddlers immediately. Again, I don't understand why it should
convert formats, unless you want to export it to a non-TW or perhaps from a
single file TW into a node.js TW.

<:-)

[Eric Shulman]

Post by Jed Carty
This doesn't really answer your question, but you gave me an idea that may
be useful.
Would people find a plugin that lets you paste text into a tiddler from a
csv or some other file type that can hold tiddler data and then have the
pasted text converted into tiddlers?
Making a very simple (and probably full of bugs) version shouldn't be too
hard.
I am not sure how useful it would be because you would still need to copy
and paste text.

I did exactly that for TWClassic:
http://www.tiddlytools.com/#CreateTiddlersFromCSV

This was implemented as wikitext + HTML.... and all of the real work is in
the HTML code, using onclick handlers. You can ignore everything outside
of the <html><nowiki>...</nowiki></html> block. There's also a bit of
stuff for selecting a tiddler to populate the textarea. You can also
ignore this. It should be very easy to adapt this code to TW5, using an
action widget in place of the onclick handler.

enjoy,
-e

[Mat]

@Eric - your input is appreciated, thank you!

@Jed- a demo is great news!

@everyone
Having now read (even more) in the archives, I conclude there just is no
decent solution yet. There are two less-than-satisfying solutions suggested;

1) Find external service that converts from whatever-format-you-have into
JSON. In my case CSV to JSON. I did find this CSV conversions
<http://www.convertcsv.com/csv-to-json.htm> service which looks very nice.
They also provide a bunch of other conversion alternatives with CSV on one
side (see left menu there) and, fyi, also a sister site with some JSON
conversions <http://convertjson.com>.
To use this there are a bunch of parameters to set so I have not yet dived
(dove? doven? dividaded?) into it. Some quick attempts seem to generate a
JSON but it takes all existing fields from all tiddlers and assumes they
are all valid for all tiddlers. In other words I suspect you'd get tiddlers
with loads of empty extra fields.

2) The other "solution" is explained by Richard Smith here
<https://groups.google.com/d/msg/tiddlywiki/E4n5ss-An30/bZjwHxJ3gGcJ>+ two
posts down there. Basically, you manually add extra columns in your CSV
file in a spreadsheet and put in JSON characters/delimiters. Then paste
into a text file. Then drag this textfile into TW.

...so an in-house solution, even if it involves some copy pasting *inside
tiddlers* would definitely be welcome.

I can think of two cases why it is important with a decent solution:

1) It is reasonable to expect newcomers to TW to want to - or even see it
as a precondition for trying TW out - to be able to import data from
previous systems.

2) I believe we can create editions (or "applications" as I'd prefer to
call them) that attracts people to try out TW where a big part of the
appeal is *in the content*. Currently, most TW solutions are "merely" an
empty tool, but I think we can reach more/other people if we provide a tool
with also valuable content.

Ok, better stop twaddling. I have a site for that. Anyway, really hoping
for some decent solution on this.

Thanks guys

<:-)



<:-)

[Mark Heptinstall]

Post by Eric Shulman
http://www.tiddlytools.com/#CreateTiddlersFromCSV
This was implemented as wikitext + HTML.... and all of the real work is in
the HTML code, using onclick handlers. You can ignore everything outside
of the <html><nowiki>...</nowiki></html> block. There's also a bit of
stuff for selecting a tiddler to populate the textarea. You can also
ignore this. It should be very easy to adapt this code to TW5, using an
action widget in place of the onclick handler.
enjoy,
-e

Has anyone successfully adapted this to TW5?

[stevesuny]

Hi Mark,

I don't believe Eric's #CreateTiddlersFromCSV has been implemented in TW5.

My working solution is to three-step it: (1) Create CSV (2) Convert CSV to
JSON (best tool: http://www.convertcsv.com/csv-to-json.htm) (3) import
JSON. It's reasonably fast and relatively painless.

A few tips:

(1) Create CSV.

(a) If you are using a spreadsheet, make sure you have a column called
"title" (lower case, as all field names must be). All other columns become
fields in tiddlers, including text, tags, etc.

(b) In spreadsheets, I have build complex formulas that generate tags and
sometimes text; using [[WikiText]] markup in spreadsheet cells makes
interesting things possible. I'd be glad to share examples; let me know
here. Here, your best friend is the =concatenate(b1,b2,b3) formula, also
expressed as =B1&B2&B32 or something like ="[["&b1&"]]" to as a way of
mixing cell data with wikitext.

(c) I have found, again assuming you are using a spreadsheet, that
collecting my "raw" data on one sheet in the workbook, and building
tiddlers in another sheet, works the best. That way, you can concentrate on
making sure your title, text and tags columns are well organized and clean,
and the original data on the other sheet can be somewhat dirty. Be careful
sorting spreadsheets; leads to sometimes unexpected results. If you have a
lot of text, that presents some other challenges from time to time.

(2) Convert CSV to JSON

(a) The referenced conversion tool works well. Develop the habit of
clearing input each time before uploading or copy/paste new CSV. I find
that I engage in a cycle of download as CSV/convert CSV to json/import JSON
in TW while I discover mistakes in my CSV; I correct mistakes, and have to
do cycle again. At that point, make sure you clear input.

(b) For field with numeric values, choose "output option" to Force Wrap
numeric values in double quotes, otherwise you will have no values.

(c) Use a distinctively named field, or tag, to filter all your imported
tiddlers for the inevitable need to delete them all when you've messed up.

I use this code in my "Clean Up the Mess" tiddler:

There are <$count filter="[!field:unique-field-name[]]"/> tiddlers with a
value in unique-field-name.<br>
<$button>
<$action-deletetiddler $filter="[!field:unique-field-name[]]"/>
Delete tiddlers with values in field:unique-field-name"
</$button>

Hope that helps. This will be a core lesson in the upcoming
{{DesignWriteStudio}} Tiddlywiki "class" in January 2017. Stay tuned!

//steve.

Post by Mark Heptinstall

Post by Eric Shulman
http://www.tiddlytools.com/#CreateTiddlersFromCSV
This was implemented as wikitext + HTML.... and all of the real work is
in the HTML code, using onclick handlers. You can ignore everything
outside of the <html><nowiki>...</nowiki></html> block. There's also a bit
of stuff for selecting a tiddler to populate the textarea. You can also
ignore this. It should be very easy to adapt this code to TW5, using an
action widget in place of the onclick handler.
enjoy,
-e

Has anyone successfully adapted this to TW5?

[Mark Heptinstall]

Post by stevesuny
Hi Mark,
I don't believe Eric's #CreateTiddlersFromCSV has been implemented in TW5.
My working solution is to three-step it: (1) Create CSV (2) Convert CSV to
JSON (best tool: http://www.convertcsv.com/csv-to-json.htm) (3) import
JSON. It's reasonably fast and relatively painless.

Awesome, didn't realise you could drag and drop import a JSON file. Thanks!

['Mark S.' via TiddlyWiki]

I do have a tool for CSV here:

https://groups.google.com/d/msg/tiddlywiki/qabE7I41Hos/SnHShTZsBgAJ

Think I've written a more automatic version, but don't have time to sift
through my files at the moment.

Good luck!
Mark

Post by Mark Heptinstall

Post by stevesuny
Hi Mark,
I don't believe Eric's #CreateTiddlersFromCSV has been implemented in TW5.
My working solution is to three-step it: (1) Create CSV (2) Convert CSV
to JSON (best tool: http://www.convertcsv.com/csv-to-json.htm) (3)
import JSON. It's reasonably fast and relatively painless.

Awesome, didn't realise you could drag and drop import a JSON file. Thanks!

[Jed Carty]

Yes, the idea is to take the Tinka plugin and instead of creating a plugin
you just make a link that can be dragged to another wiki to import many
tiddlers at the same time. The tiddlers are converted because there needs
to be some way to pack them into a single thing to import, if this didn't
require some sort of conversion than we wouldn't have the problem.

The way this relates to your original question is that it could provide a
framework for copying plaintext into a tiddler and applying some transform
to it to create tiddlers. I don't think I am doing a good job of explaining
this so I am just going to try and make a demo. I will post a link here if
i get anything.

[Jed Carty]

I made a tool to make tiddler schools (didn't someone come up with a better
word for that a while ago? I can't remember.). You give a filter and a name
and it creates a tiddler that contains all the information for all the
tiddlers returned by the filter. You can then import that tiddler into
another wiki and use the widget I made (action-import-tiddlers) to unpack
the imported tiddlers.

There is a demo here <http://ooktech.com/jed/ExampleWikis/TiddlerSchools/>.
There is a bug that I can't track down, if you try to make a school using
the filter [tag[Filter Operators]] or [tag[Messages]] you get a too much
recursion error. I can't figure out why and I haven't found any other bugs
so far. I am sure there are plenty though, so play with it and try to break
it.

[Tobias Beer]

Hi Mat,

I believe, what we eventually want or need is a csv (multi-)tid serializer
for the core, i.e. another option to export one or more tiddlers and then
the appropriate import handler.

Best wishes,

Tobias.

['Mark S.' via TiddlyWiki]

I see Jeremy has a post about importing XLSX files. Seems that any CSV
could probably be converted to XLS and then loaded via the new plugin.

Thanks,
Mark

Post by Tobias Beer
Hi Mat,
I believe, what we eventually want or need is a csv (multi-)tid serializer
for the core, i.e. another option to export one or more tiddlers and then
the appropriate import handler. All this preferably allowing to configure
custom csv formats, e.g.
- delimiter(s)
- quote symbol(s) — to allow for line-breaks in cells
- escape symbol(s) — to escape delimiters or quote symbols in cells
Best wishes,
Tobias.

[Josiah]

This post might be inappropriate. Click to display it.

['Mark S.' via TiddlyWiki]

Who ya calling flabby ? ;-)

Actually, CSV is streamlined. Especially if typing by hand. You just put
the text you want on the line and bang -- done. You don't have to repeat
labels over and over and get the spelling right every time. You don't get a
crash because you forgot a closing bracket or curly brace.

CSV has been around a long time, and you're more likely to find converters
for it in various applications. It's much easier to write a CSV parser by
hand if you have to than JSON (assuming you don't have a handy library).
Looking at export options on Excel 2013, I don't see the ability to export
to JSON.

I suppose the most universal format would be XML, though it's even messier
to type than JSON.

Have fun,
Mark

Post by Josiah
Ciao Tobias & Mat
I simply don't understand the appeal of CSV. Its a flabby standard. Often
a pain-in-the arse. What's wrong with JSON, its much more robust & with
clear rules?
Best wishes
Josiah

Post by Tobias Beer
Hi Mat,
I believe, what we eventually want or need is a csv (multi-)tid
serializer for the core, i.e. another option to export one or more tiddlers
and then the appropriate import handler. All this preferably allowing to
configure custom csv formats, e.g.
- delimiter(s)
- quote symbol(s) — to allow for line-breaks in cells
- escape symbol(s) — to escape delimiters or quote symbols in cells
Best wishes,
Tobias.

[Tobias Beer]

This post might be inappropriate. Click to display it.

[Josiah]

Ciao Tobias & Mark S.

Actually I agree with you on your TW pragmatics.

What I didn't make clear enough is I was talking about CSV generally. Its
inconsistent. Its an old standard that you have to get right in context.
Its got several flavours. JSON I prefer because its consistent in its
rules.

Best wishes
Josiah

Post by Tobias Beer
Hi Josiah,

Post by Josiah
I simply don't understand the appeal of CSV. Its a flabby standard.

Often a pain-in-the arse. What's wrong with JSON, its much more robust &

Post by Josiah
with clear rules?

1. *Export *from TiddlyWiki
2. *Edit* in Spreadsheet
3. *Import *into TiddlyWiki
Apart from a one time setup you may need in terms of CSV options,
what would be the pain in the ass?
1. bulk modify things outside of TiddlyWiki
2. convert some spreadsheet data into tiddlers
Best wishes,
Tobias.