I took a look at $:/plugins/tiddlywiki/xlsx-utils/xlsx.js, and it's beyond
me. But I bet someone with js understanding could figure out how to modify
the xlsx plugin so that TW wrote fields into its new tiddlers using column
numbers (auto incremented) as field names, rather than the contents of row
1 as field names.

Advertising

It's interesting to here about your setup. Thanks, //steve.
On Tuesday, March 6, 2018 at 11:18:09 PM UTC-5, Schulerbot wrote:
Hi Steven,
Thanks for responding, true, I can manipulate the spreadsheets easily to
make this work, but i'm trying to fully optimize before resorting to doing
that. A little more about my setup.
I have spreadsheet reports emailed as attachments, for several things that
I get for work, and I spend about an hour of the day grabbing data from
these reports, manipulating and putting data into other spreadsheets for
inventories and other action items, would love to automate-ish this work.
For this question, I get a specific report, its something sent to
customers, a widget report of what we shipped to them. I want to take this
email with an excel attachment and drag the attachment into my
tiddlydesktop/tiddlywiki (which I keep open most of the day and use as a
knowledgebase/intranet) to create a rolling list of shipped assets as a
database without having to take the step of opening up the spreadsheet
attachment, manipulating the column titles, saving it to an actual location
on the machine/server, then importing it to my tiddlywiki. Because this
spreadsheet is formatted for viewing by customers, it has line breaks in
the column titles. I tried endless combinations of line change characters,
formatting, and even updating the xlsx.js code tiddler before posting.
I haven't had a chance to try Jeremy's suggestions yet, but I echo the
usefulness that an updated controls panel would have for other Excel
applications, or even to build an entire tiddlywiki via Excel. For my
application though (importing data repetitively via reports that are
consistently formatted), this plugin seems to match quite well.
-Mike
On Tuesday, March 6, 2018 at 5:07:05 PM UTC-5, Steven Schneider wrote:
I'm sure you could fix this if you edited the file. One edit-the-field
strategies that I've employed: insert a new row at the top of the
spreadsheet, and generate a set of useable field names. leave the cell
containing the title field blank in this row; when setting up xlsx import,
make sure the checkbox "skip if blank" is ticked. I realize this creates an
step requiring edit of the xlsx file, which as you noted is not something
you want to do. But you must need to open it to get the column names, no?
If you copy/paste-special-transpose the column names into column1 of
another sheet in the workbook, you could read in the column names as values
in tiddlers (you might set the title of the tiddlers to be =row(), which
would give you a # as a hook. You could then transclude these values to the
import spec tiddlers ..... but this gets messy quickly.
Jeremy, is there any way to get access to the column number, or is
everything in the plugin running on column names (i.e. the text in row1 of
the column)?
I've long wanted the xlsx import plugin to be modified so that there was an
option to name the fields Column1, Column2, etc to avoid these issues.
Similarly, set the title of imported tiddlers as Row1, Row2, etc. An
additional feature might create tiddlers for each Column, and populate the
caption field for these tiddlers with the contents of Row 1. And populate
the caption field of the row tiddlers with the contents of a specified
column.
This would save much of the tedium of building xlsx imports, especially for
large spreadsheets.
//steve.
On Tuesday, March 6, 2018 at 4:32:44 AM UTC-5, Jeremy Ruston wrote:
Hi Mike
Trying to import excel spreadsheet report repetitively to create database
of tiddlers.
In the primary row of the excel sheet, where the column titles are that
specify the pointers for the field import specification, I have a couple
columns with line breaks (ALT-Enter in Excel)...Not my choice
How do i specify the line break character in the "column" field of the
plugin control so that the plugin finds the correct column and
corresponding data to put in the field upon import?
Seeing if I have an option here besides manipulating the excel data. I'd
rather just import the existing report as-is that is e-mailed to me.
Interesting problem! As you’ll have discovered, attempts to copy the line
break character from Excel and paste it into the editor of the XSLX plugin
don’t work. The reason is that those single line text fields don’t allow
control characters.
A possible workaround is to identify the tiddler field(s) that need to have
these control characters pasted, and to edit those fields in a scrap
tiddler via a textarea:
<$edit-text tiddler="$:/_importspec/Presidents and
Justices/States/Main/title" field="import-field-column" tag="textarea"/>
Then you should be able to paste the required line breaks into the fields.
The problem you’ll then likely run into is that control characters are
removed from tiddler fields when you save and reload as a standalone HTML
file. A quick and dirty workaround would be to save the wiki in JSON format
(via the export button).
Best wishes
Jeremy
Thanks!
-Mike
--
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 post to this group, send email to tiddl...@googlegroups.com.
Visit this group at https://groups.google.com/group/tiddlywiki.
To view this discussion on the web visit
https://groups.google.com/d/msgid/tiddlywiki/376094f7-e825-469f-90ec-7d3bf83b7d27%40googlegroups.com
<https://groups.google.com/d/msgid/tiddlywiki/376094f7-e825-469f-90ec-7d3bf83b7d27%40googlegroups.com?utm_medium=email&utm_source=footer>
.
For more options, visit https://groups.google.com/d/optout.
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to tiddlywiki@googlegroups.com.
Visit this group at https://groups.google.com/group/tiddlywiki.
To view this discussion on the web visit
https://groups.google.com/d/msgid/tiddlywiki/05cfca17-67ab-49c1-9884-0b2f0f23d1b7%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.