CSV Import Process

[tbx1513]

I’ve been doing some CSV imports into Tinderbox. These files are exported from a relational database, and each record has many attributes defined in said database. I want to keep these but add prefixes, so I’ll know these attributes weren’t ones I created in Tinderbox. I’m currently doing this by adding the prefix in the CSV file for each column heading before I do my import—no issues with this piece. However, I often have columns that don’t have values in these data sets. Tinderbox is really good at looking at values and assigning an attribute data type based when there are existing data values. However, in my situation, Tinderbox sets the attribute to Boolean-Type because there are no values. I thought it would be nice to tell Tinderbox what data type to assign. For example, add text to the column heading with special characters e.g., #String-Type#, and then have the data type set.

If the above is not possible, what would be the most effective after-import process to automatically do this instead of manually doing it from the inspector? Would it be a stamp or agent?

I thought I’d provide some more context. Manually applying the data type probably won’t take that much time. I usually don’t build processes around edge cases, but since I’m in learning mode with Tinderbox, I have a mindset of trying to understand what is possible so that I can squirrel away these tricks and apply them more broadly down the road.

Any advice or insights is appreciated!

[archurhh (Art Currim)]

@tbx1513

If the above is not possible, what would be the most effective after-import process to automatically do this instead of manually doing it from the inspector? Would it be a stamp or agent?

1. Import the CSV data once.
2. create the User Attributes.
3. Go through and correct the Attribute type as required.
4. Delete the data
5. Re-import the CSV.

[tbx1513]

Thanks Art!

[eastgate]

For some aspects of attribute, you can use the attribute operator in an action:

attribute("Fruits").[suggested]="apple; pear;plum;cherry";

I’m not entirely sure whether you can set type this way. I’m down in DC today and can’t check properly, but aTbRef will surely know.

[satikusala (Michael Becker)]

@eastgate

attribute("Fruits").[suggested]="apple; pear;plum;cherry";

I’ve tested it, and it works.

[eastgate]

But does

attribute("Fruits").[type]=boolean;

work?

[satikusala (Michael Becker)]

No, that does not seem to work.

[tbx1513]

I had similar testing results. I could set suggested values and also default values but not type. The below set a boolean default value to true without issue for an existing attribute. I also did some variations trying to get type to work and noticed things worked with various syntaxes. e.g., with or without​ ​. or “” .

attribute(“Fruit”)[“default”]=“true”

attribute(“Fruit”)[“default”]=true

attribute(“Fruit”).[“default”]=“true”

attribute(“Fruit”).[“default”]=true

[mwra (Mark Anderson)]

Currently, per attribute(attributeNameStr)[keyStr], only the default and suggested values can be set via action code.

[mwra (Mark Anderson)]

I caught this thread just we were heading into the weekly meet-up, so few more points:

I think the general points are well-covered by these articles: Setting up for Tab-Delimited Import and Spreadsheet Import: CSV and Tab-Delimited Text.

As has been noted, and as I realise the above omit to mention (on the to-do list!) is that Tinderbox can now create attribute via action code. That said, we still—as the user—have do decide:

• what source columns should map to in Tinderbox and if new attrIbutes
  • what these should be named
  • what data type they should use
• default values, suggested values etc.

Apart from simple exploratory tests, e.g. “Does anything import at all?” my approach is to ensure the correct (additional, if necessary) attributes are configured as needed before doing the main data import.

If the source if giving you helpful column names like TablA Pr_ea you might consider editing the heard row of the data to use your desired Tinderbox attribute name for each columns data as there is no import map process. If you didn’t make/don’t control the source the default header names are often unhelpful and using Tinderbox’s best guess at a Tinderbox-compliant guess is little better. 5 minutes work pre-import an save hours of frustration though the years of use of a document. You can always add a note (or an attribute description) recording the data source’s original column name.

New attributes can be created using createAttribute(nameStr[, dataType]).

@tbx1513

For example, add text to the column heading with special characters e.g., #String-Type#, and then have the data type set.

No but you can make a list like “Price:number;SKU;string” which is a list of attributename+colon+datatype+semicolon items. Both are case-sensitive : the attribute name must conform to attribute naming rules and data type is always all-lowercase. There should be no line breaks between name:type pairs. Let’s assume we’ve made such a listing the $Text of a note. Now a stamp can make the attributes:

$MyDictionary = dictionary($Text);
$MyDictionary.keys.each(aKey){
   createAttribute(aKey,$MyDictionary[aKey]);
};
$MyDictionary=;

[TomD (Tom Diaz)]

Here is another tool created by @webline that works for me for creating User attributes on the fly. I like it.

Tom

AS (Apple Script) app to add attributes to TBX

I’m working on my “architecture” to have a reusable set of action code functions. If I copy a function to a new TBX file I run into the problem that I need to create the user attributes used in the function manually. So I created a small AS app that will solve this issue for me. addTBXAtt.zip (56.9 KB) This is an AppleScript app with the source included - you can open it with the Script Editor if you like. if you don’t like to jump into the AS world - just place the app anywhere on your Mac, …

[tbx1513]

Hi Mark,

Thank you for this packed post! I’m still reviewing all the resources, but a lot is starting to click for me. Extremely helpful!

Editing my last

After a double check, it looks like the additional comma wasn’t necessary for multiple items. My bad, thanks again!

[tbx1513]

Hi Tom,

Sounds cool. I’ll give it a try; thanks!

[mwra (Mark Anderson)]

I my last post I used a Dictionary attribute as my variable-based code failed. I now realise this was a letter-case error in my variables (akey != aKey in code, but easy to miss by eye). So here is code as above but needing no new/system variables:

var:dictionary vDict = dictionary($Text);
vDict.keys.each(aKey){
   var:string vType = vDict[aKey];
   createAttribute(aKey,vType);
};

Note that the $Text is only for input string, as per my last post above. Now we don’t need any attribute in-process, a function is the next obvious step:

function generateAttributesString(iSourceStr:string){

   var:dictionary vDict = iSourceStr;
   vDict.keys.each(aKey){
      var:string vType = vDict[aKey];
      createAttribute(aKey,vType);
   }

}

called via a stamp like so:

generateAttributesString("Offer:number;Bin:string;HasBin:boolean");

You could also use a string-type variable as input. Or, with a slightly different function, pass in a dictionary:

function generateAttributesDictionary(iSourceDict:dictionary){

   iSourceDict.keys.each(aKey){
      var:string vType = iSourceDict[aKey];
      createAttribute(aKey,vType);
   }

}

Called from a stamp or other action like so:

var:dictionary vDict = dictionary("Event:number;Thing:string;HasThing:boolean");
generateAttributesDictionary(vDict);

[tbx1513]

Thanks for the follow-up and iterative options!!

By using createAttribute(nameStr[, dataType]) we can define the “Name” and “Type”, and by using attribute(attributeNameStr)[keyStr] we can define the “Default” and “Suggested” values. I’m not seeing a current way to define the “Description” field. Is that correct?

[eastgate]

Not at present, though this will come in the next backstage release.

[tbx1513]

Fantastic, thank you!

[tbx1513]

@eastgate adding the description works well; thanks again!

Apologies in advance; I should have noticed this when I asked about the description. I can’t seem to add “lines” for an attribute. This value is the last value I’d like to set. Am I missing something, or can this be added as well?

[mwra (Mark Anderson)]

@tbx1513

I can’t seem to add “lines” for an attribute.

If you mean the new doc-level String-only lines of display (as seen here) for use in Displayed Attributes and Get Info, then the answer is you cannot access this via Action code in v9.5.2. The setting is there in the XML:

In the left -hand TBX the ‘lines’ of $MyString have been set via the Inspector to 5. However, that attribute property is not (yet!) exposed via attribute().keys. For the above edited TBX use used this stamp to expose the accessible attribute properties:

$Text = attribute("MyString").keys;
// result: category;sequential;default;type;suggested;description

Note that ‘lines’ is not included. I suspect this is an oversight that will be corrected in forthcoming additions.

Indeed, of those 6 attribute properties listed above, as at v9.5.0, only default and suggested can be edited via code. The other 4 are read-only via action code.

[tbx1513]

Yes, I had looked as the XML as well. I knew of all the other keys that could be added but couldn’t add “lines.” So I wanted to confirm I wasn’t missing something. If not, see if tech support could add this additional key. It looks like I wasn’t missing anything. Thanks for the trick with the stamp though.

@tbx1513

By using createAttribute(nameStr[, dataType]) we can define the “Name” and “Type”, and by using attribute(attributeNameStr)[keyStr] we can define the “Default” and “Suggested” values. I’m not seeing a current way to define the “Description” field. Is that correct?

Note: Description was added in Backstage version recently.