Re-importing a previously exported CSV (JSON Mangler? XLXS Utils?)

[Osin]

Hello,

I am working on transforming an FAQ contained in an Excel workbook (with only one spreadsheet) into a simple TW. I posted about it here : https://groups.google.com/g/tiddlywiki/c/i6-Ge9ahVCQ/m/X8BhP54IDAAJ , got some great feedback, and I have been testing and playing around TW, I think I'm starting to wrap my head around it (Mat's "Big Picture" text really made things click, thanks!).

The original FAQ Excel file contains about 60 entries, one entry per row. I manually "transferred" 10 of them into tiddlers, linked them with tags and created a TOC within a TW5 instance. However, the full 60 entries will need to be revised and edited by two of my teammates before I go ahead and import everything.

I exported the 10-tiddler TW into a .CSV file. It gave me (and my colleagues) a good idea about how the data is structured. I am now at the stage of modifying the CSV by adding the remaining 50 QAs after my colleagues have reviewed them, and re-importing the CSV into TW. I'm doing a test-run and adding 6 additional rows to the CSV.

How can I easily re-import the CSV? Tones mentioned JSON Mangler in the other thread, but I am a bit lost in the documentation (again). I found a thread about it as well, but there is talk of shadow tiddlers, turning the import into a plug-in, and other options I am not sure concern my use case. At this point, I'm unsure whether I should just just transfer everything manually, it might be quicker than troubleshooting the import. I also saw there is xlsx-utils, would that plugin serve a similar purpose?

Thank you.

[Joshua Fontany]

Hi Osin,

Plugin author of JsonMangler here. The CSV options in JsonMangler are complex and can be a bit hard to figure out.

What I would do in your situation is to ensure that you have column headers in your Excel file, and that one of the column headers is "title" and one colum header is "text" (all headers need to be lower case, and "tags" would be a good one to include). Then, run your file through a CSV/XLMS -> JSON converter. This should turn your file into an "array of objects" with each object having at least a "title" property. You can then drag and drop this JSON file into vanilla TW5 and it should import as 1-tiddler-per-object.

Here is a converter that would work for you: https://json-csv.com/reverse

For example I ran this small sample CSV through the converter:

title,tags,text

One,test,Question One?

Two,test,Question Two?

Three,test,Question Three?

Which renders as:

[{

"title": "One",

"tags": "test",

"text": "Question One?"

},{

"title": "Two",

"tags": "test",

"text": "Question Two?"

},{

"title": "Three",

"tags": "test",

"text": "Question Three?"

}

]

SAVE this output as a text file with the "*.josn" extension (it comes out of that converter as "result.json"). Then, simply drag and drop this Json file into a Tiddlywiki. If it is a file with this correct format, each object will become a separate (regular) tiddler.

This will help you use the other features, like Filters, etc on your tiddlers without worrying about them being "json tiddlers".

Best,
Joshua Fotnany

[Osin]

Hi Josh,

Thanks a lot! I am already halfway through with the headers (TW exports them as such, so I just kept the same structure). I actually considered this route, but abandoned it because I wasn't able to find a CSV to JSON converter that ran locally - I work with protected data so I'll avoid using online data converters.

I then explored XLXS Utils, but there's a bug in the newest version, so I used 5.1.22 to import, then export to JSON, and import into the full wiki. Still a bit convoluted, so I will explore your suggestion and look for a locally-run JSON converter. I work in a team with people of different technical skills, so I am trying to make it as painless as possible in case someone else needs to take over my role.

Again, thank you!

[Joshua Fontany]

Gotcha. This is actually very do-able with a local copy of my JsonMangler Demo Wiki. 

The following will import a CSV as one tiddler per line, as regular tiddlers (not json), but it packs them as shadow tiddlers into a plugin tidder.

 I'll try to step you through it. :)

• Go to: https://chronicles.wiki/TW5-JsonMangler/
• Download and open a local copy of that demo wiki.
• Create a New Tiddler, give it type "application.csv", and paste your CSV data into the text field.
• Save the New Tiddler (change the title as suits you).
• The type "application/csv" makes my CSV UI render on the tiddler. Click on the double right arrow ">>".
• Click the very last option, the Paper Clip Icon next to "Import Options".
• A new section is revealded. Change the second drop down in that section, labeled "Primary Key:" to the column number of your "title column, this is usually "Column 0" for me.
• Right below that in the "Tiddler Names:" textbox, enter "[<primaryKey>]" as the only text in the box.
• Click the Paperclip Icon & New Data Tiddler Name Link next to "$:/Import:"!!!
• The regular  $:/Import UI is displayed. Confirm here by clicking the [Import] button.
• A new Plugin Tiddler, probably called "Data/New Tiddler", with all your new tiddlers packed inside it is created. 

(Aside - use "all[tiddlers+shadows]" to start filters to target Shadow Tiddlers) 

Best,

Joshua Fontany 

[Joshua Fontany]

Ack, the `type` should be `application/csv`. I misspelled it once. :)

[TW Tones]

I will just add, although I do use the JSON mangler; another option is to open the CSV file and copy the content of all entries and past into a tiddlers text field.

Now along with few methods such as;

<$list filter="[{Data}splitregexp[\n]limit[10]]">

</$list>

Then another list parameter that splits on the bases of "," etc... you can build your own CSV parser. 

I am not providing the full details here just an indication of n alternate path you  can take.

Tones

[Osin]

This worked, thank you so much Joshua!

For anyone reading, make sure that you copy/paste the CSV data from something like Notepad, and not Excel.