Importing from CSV, Append the field, NOT replace entire contents on update

[Tony Diaz]

I've got a tickbox field with values, (a,b, cr, tr, i, h, etc). Single and two character values.

The data comes from an sqlite3 file that has each of those values in a seperate table/record with a key relationship to the main table.

I'm wanting to set up a one way flow of occasional data into FM where I'll have those values in a CSV format.

uuid,value

but I want to store them in FM in a single field, as if they were value list items ticked off. I'm generating the CSV file with each value having a C/R (Cursor down), whatever the character that FM uses.

The importing works like that and single values are showing:

uuid,value

22424,tr

22424,t

2242,cr

..and after an import I'll see this:

What I'd like to see is this:

..instead of keeping only the last value, I would like to add that value to the field. A bonus would be to validate if it's not already there, but that's not priority as the workflow would likely never encounter a duplicate value. It's not possible to create a duplicate value currently, the way I'm generating the CSV for this operation. But for using this method on other data where that could be a problem, a solution would be nice. Not sure what FM will do if it encounters multiple identical values in a tickbox field .. maybe it's not an issue anyway.

 

[comment]

Fields formatted as a checkbox set store their values as a return-separated list. If you want to import into such field, then your CSV source needs to look like this:

uid,value
22424,"tr
t
cr"

I am guessing you are importing using the update matching records method. In such case, each row of your CSV will overwrite the contents of a previous row having the same uuid. If you want to keep the current format of your CSV source, you will need to import it into a related table first. Then you can populate your checkbox field using =

List ( RelatedTable::Value )

 

20 minutes ago, Tony Diaz said:

I'm generating the CSV file with each value having a C/R (Cursor down), whatever the character that FM uses.

No clue what you mean by that.

 

[Tony Diaz]

 

2 hours ago, comment said:

...then your CSV source needs to look like this:

uid,value
22424,"tr
t
cr"

 

That's the point here. They don't as during the generation of the CSV those values are already a separate field.  So because they can't, I want to append the field contents rather than replace.

2 hours ago, comment said:

No clue what you mean by that.

That means I'm taking care of the needed carriage return, at least on an individual basis. So if I could -append- each of these single lines, it would result in the intended way. (Inverted red question mark)

 

Edited May 6, 2020 by Tony Diaz

[comment]

3 hours ago, Tony Diaz said:

I want to append the field contents rather than replace.

There is no way to append a value to a target field during import. Possibly (I haven't tested this), you could use a dummy target field and have your real field auto-enter a calculated value appending the imported value to its own. But I believe importing into a related table, as I suggested above, would be a more robust solution.

 

3 hours ago, Tony Diaz said:

I'm taking care of the needed carriage return, at least on an individual basis.

I am afraid you're wasting your time on a dead end.

 

Edited May 6, 2020 by comment

[Tony Diaz]

Well, not exactly a waste of time... as I've come across some other useful ways of manipulating data and learned a little more about    RegEx along the way.

But I can do it in FileMaker the way I want with a script. Except during an import. 🙁

So that means related table ...  and I need to get my head wrapped around relationships and portals once and for all. It's still somewhat voodoo to me, and I wasn't wanting to approach this with a portal or similar as first, Portals are only available in the one format, and I can't show.

So, a script that would then go parse that related table and add any values from the tag field to the associated record / field using something along the lines of

Set Field [ Data::Import_Tags ; Data::Import_Tags & RelatedData::Tag Holding ]

 

[comment]

No, it would be more like:

Set Field [ Data::Import_Tags ; List ( RelatedData::Tag Holding ) ]

This would put a list of all three related values into the checkbox field, all at once. There are no portals involved in this, but you do need to define a relationship matching on the uuid. And if you prefer, you could leave the related records in place to hold the data and make the checkbox field a calculation field just for display.

It can also be done without a relationship, but then the scripting gets more elaborate.

BTW, I believe that SQLite supports the group_concat aggregate function - so I am not sure why you can't generate a file that could be imported directly, in the format shown in my first post.

 

[Tony Diaz]

I'm not opposed to just leaving the data in a related table, it's the presentation of it that I'm hung up on,  being able to add/remove some and then to export the stuff in the future in a separated format with each of those tags having it's own column.

Hmm...  calculated value list. Somehow apply the value list in such a way that if a box is ticked/unticked there is/not a related record in the tag table?  That sounds curious. More to learn about...

So I'm going to setup the import to put them in a related table try that approach since it's apparent that the data can be used very flexibly when stored like that. Just need to figure that stuff out next.

 

 

Just for commenting

I looked at group_concat for sqlite .. which would mean a total restart at the python script I'm using to create that db file.

The data I'm working with comes originally in an XML format that is parsed specifically based on the order of fields, with only the first 4 being mandatory and the rest optional. Since the other values are unique in the whole string the rest of the parsing is done on an "if this is found" anywhere "it goes here/means this". Otherwise the rest of the string is ignored.  I need to get a lot better with RegEx and then this kind of stuff will be easier to manipulate.

My skill set is at the 'I know enough to be dangerous" level. I can follow scripts, logic.l.. but getting the syntax/phraseology.. lots more to learn with Python/RegEx, and applying that stuff to mysql/sqlite vs FileMaker. One day I'll get there. 

 

 

 

[comment]

24 minutes ago, Tony Diaz said:

calculated value list. Somehow apply the value list in such a way that if a box is ticked/unticked there is/not a related record in the tag table?

That cannot work the way you describe. Such calculation field can only be used for display, not for modification. But you could use another device, e.g. a portal.

It should be noted that strictly speaking, the "correct" structure is the one you have in the source SQLite DB. Filemaker allows you to keep multiple values in a single field, but this is a violation of normalization rules.

 

29 minutes ago, Tony Diaz said:

The data I'm working with comes originally in an XML format

Do you know that Filemaker can import XML? And that you can (in fact, must) apply an XSLT stylesheet during the import to transform the XML into Filemaker's own XML grammar? And that such transformation can also restructure the imported data into practically anything you want? That should have been your first avenue to explore. (And I sincerely hope you're not trying to process XML with regex.)

 

[Tony Diaz]

6 minutes ago, comment said:

That cannot work the way you describe. Such calculation field can only be used for display, not for modification. But you could use another device, e.g. a portal.

That's why the value list approach made sense to me. Exporting the csv with hard CRs in it just seems like a mess looking to happen, 

The end result is to be able to import what I have already, to be able to tick those selections on/off in FM, and then export it if I want to.

 

The lower field is just so I can see if what I'm doing with scripts / calculations is creating what I want. I just want the tick boxes displayed in the end. (and then there's another thread where I'm wondering about value lists vs. the actual data stored, but I can deal with that by using the value list with a transparent font and putting the value description on the layout)

 

53 minutes ago, comment said:

(And I sincerely hope you're not trying to process XML with regex.)

Okay, I won't. As in, *I* didn't do it. That's how the script I started with worked, and except for extracting the tag values and putting them into a single string.. it works.. ) 

I may be taking the long, hard way, boneheaded approach to this .. but I'm happy with what I've learned along the way, even when I finally see that something done one way could have been done so much differently. That's what 2.0 is for.

[comment]

I agree that the most straightforward approach is to populate the checkbox field with the values you want to be ticked. And the most straightforward method to accomplish this is to import a return-separated list of the ticked values into this field.

So the proper question to ask here is how to produce a source document that contains a single record for each uuid, with multiple values separated by carriage returns in the value column. And the most straightforward answers to this question lie outside of Filemaker (unless you take the XML/XSLT route, in which case the answer still lies outside of Filemaker, but can be executed by Filemaker during the import). 

I see no good reason to suppose that "csv with hard CRs in it just seems like a mess looking to happen". Such CSV is perfectly normal and common - how else could you export a text field with multiple paragraphs?

 

[Tony Diaz]

20 minutes ago, comment said:

I see no good reason to suppose that "csv with hard CRs in it just seems like a mess looking to happen". Such CSV is perfectly normal and common - how else could you export a text field with multiple paragraphs?

It's mostly because I'm just used to being able to look at CSV data in straight columns and seeing each line the same. I realize that paragraphs and special characters, being able to have the delimiter character actually exist within the data, etc.

Like way back when .. the first time I figured out how you could have a comma actually in the data. Until then, I always did it where I'd substitute that for another string that I knew wouldn't be there otherwise. (s/*1*/,) .. yeah. I know, Silly.  But Applesoft BASIC, text files and DOS 3.3. That was instant gratification.

Otherwise, ya, at the onset I wasn't able to change the way the data was formatted, but now that I've learned a few more things .. that's not so bad either, and I come away with understanding the points of each method.

The XML deal is, while it is in XML,  I don't think it's quite proper. All of the tag data is just lumped into a single line and certain ones go in certain places, a few of them there are either one or the other, never both. (contradicting), so that's the subset of that regex is taking care of.

Besides, until now, I had no idea that there was another layer for XML (XLST), but now that makes a lot more sense because otherwise I couldn't ever understand why XML was supposed to be "easier" for universal use.

Thanks!

[comment]

17 minutes ago, Tony Diaz said:

Until then, I always did it where I'd substitute that for another string that I knew wouldn't be there otherwise.

Well, you have something in common with Filemaker engineers. Although they properly quote the exported fields, and escape in-field quotation marks, for some reason they chose to substitute in-field carriage returns with vertical tab characters. Strangely, they have no problem importing a CSV containing in-field returns.

 

23 minutes ago, Tony Diaz said:

All of the tag data is just lumped into a single line and certain ones go in certain places, a few of them there are either one or the other, never both.

I am afraid I don't follow your description. It is certainly possible to misuse the XML format, but there is no requirement for it to be indented. A single line XML is perfectly valid (and easily converted to a more human-readable form by XSLT). And there is no requirement for it to be regular (i.e. table-like, with rows and columns). In fact, that is its major strength.