Ok I have a nice form designed…. but

[Roger Neilson]

I want to import records that are in CSV format. Normally with a database you get the chance on import to link the field that the csv data has with the field you want the data to go into on the database…. I cannot do this?

Assuming I overcome this issue, when i get another csv file to import into the same database how will I do this?

In Bento it was easy…..

Hmmmmmm

[Brendan]

Hi Roger,

Did I answer this one already for you? Sounds familiar.

Anyway, you need to make sure the names in the header row are the same as the field names in the form (character for character). Tap Forms uses the names of the columns in the header row of the CSV file to match up with the field names in the form. With that setup, you can import into an existing form and Tap Forms will append the records from the CSV file to your form.

Thanks,

Brendan

[Roger Neilson]

Ok I get that, the data is effectively just one field, so provided that one field has the same filed name as on the form it will import in without a problem? New data imports will happen maybe every other day to be added to the data that is already there.

Is there a problem with the fact that the form may well have ten or more fields – nine or more of which should not be affected by the import?

Really what i am doing is importing the one field, then going through and extracting into other fields key information….

Looks like my problem was I was using different field name to the import header field name,

Roger

[Brendan]

Hi Roger,

Yes, the field name in the CSV file must match character for character the name of the field in the form.

Thanks,

Brendan

[Roger Neilson]

Got this working perfectly now, thanks for the help.

Roger

[Glenn]

I must be missing something obvious here but I cant get the import to “append”, but it creates another set of records.

Here is my situation. I have a 150 records which correspond to individual location maximum temperatures. Each week I planned to add a field and populate values using a CSV import for the previous weeks maximum temperature for each of the 150 records. This would be done for a year. Therefore, ultimately each record would have 52 fields (one per week).

I have double checked that the field name created matches the header in the csv but when I do the next weeks import, my 150 records goes to 300 with duplication of the location names.

So my CSV file for the first import would be

Location,Temp 29 Dec
Yaamba,32.4

Prior to the import next week, I alter the existing form to create a new field called Temp 5 Jan

What is the CSV supposed to look like for the subsequent imports? I have tried:

Location,Temp 5 Jan
Yaamba,36.6

However, this just gives me two Yaambas, one with a blank Temp 29 Dec value, and the other with a blank Temp 5 Jan value.

I must be missing a critical step somewhere here

Regards

Glenn

[David Butenhof]

That’s because TapForms doesn’t match records on import unless you include the TapForms internally generated record ID field.

You can do what you want, but you should start by EXPORTING your form with the original data. After that, you can just modify the CSV and re-import, adding the new column each time.

(Although, incidentally, I probably would have just added a record for each sample.)

So first, design your form. Attachment “Step 1” shows a really simple form like you’re describing.

Now EXPORT that form to a CSV file, which will look like “Step 2” in a text editor. The important part is the “form_record_id” column added by TapForms. That’s what allows it to match records on import.

Now use your text editor to add a new column, with the data point, as in “Step 3”.

Import that CSV file back into TapForms, as in “Step 4”. You already know that the important part here is selecting your existing form rather than letting it create a new one.

The result should look like you want, with the new data point (and column) added into your existing form. (But the forum software won’t let me add a 5th attachment!)

Attachments:

You must be logged in to view attached files.

[Roger Neilson]

I am having no problems now doing data imports provided I make sure that the headers are exactly as they are on the form.

Where I have constant importing from a generated csv its simple because its always the same data format.

Where it isn’t simple I open it in numbers first, dom any tidying there and add any data I want, then export as CSV and then import to my TF database.

Roger

[David Butenhof]

Seeing Roger’s reply right after mine makes me realize that I wasn’t careful in setting up my post. ;-)

So, belatedly, and just for the record, my post was specifically in reply to Glenn’s comment about being unable to get the Import to “append fields” (replace/update an existing record) in the form. He wanted to add columns to an existing row rather than create a new row, and I was explaining that will only happen if the form_record_id UUID field values match.

[Glenn]

Thanks for such a detailed response and clarification David. Much appreciated. I will try this tonight.

Can I ask what you are using to edit CSV files and what was featured in your screenshots? I work with raw data from Excel for Mac and convert to CSV prior to the import but based on Roger’s comment above just wondering if there is something more “pure” to work with handling CSV.

David, I just have a question re the Record ID field. If I maintain the 150 records, and keep adding fields for the weekly updates, does the Record ID change when a new field is added to an existing record? The reason I ask is that I could only export the Tap Forms data once at the start to get the 150 Record ID’s, and that way I can massage the CSV directly with the new weekly data and then import.

This might be a silly question but I am a novice when it comes to the inner workings of database engines.

Regards

Glenn

[David Butenhof]

Can I ask what you are using to edit CSV files and what was featured in your screenshots?

The screen shots were from TextWrangler, a free and powerful text editor oriented towards programming.

David, I just have a question re the Record ID field. If I maintain the 150 records, and keep adding fields for the weekly updates, does the Record ID change when a new field is added to an existing record?

The record ID isn’t changed. It’s generated when the record is first created, and that’s how TapForms can track changes to the record when you synchronize changes through iCloud. If two records have the same ID, then they’re different “versions” of the same record.

So if you initially create your form in TapForms with the first column of each of your rows (records), and export that as CSV, from then on you should be able to just modify the CSV and re-import the changes. To TapForms, this is essentially the same thing as making the changes on another device and then synchronizing through iCloud.

In fact you could start out with the CSV and creating the record IDs yourself, then import that into TapForms to create the form in the first place. That’s what I did to import a lot of Bento data with Simple Lists, which TapForms doesn’t support. However in your case letting TapForms generate the record IDs is safer and just as easy.

[Glenn]

Thanks David. This is all working nicely now thanks to your help.

With this form, I am now looking at implementing forward and inverse linkages between two forms.

I am just wondering that when exporting and importing CSV into forms when updating or appending data where form linkages exist, is there anything special that needs to be done with record_ids that store the linkage information.

I presume I don’t need to touch or export any of the linkage id’s that are generated between forms if I handle the linkages manually in the forms after I have appended the data with an import.

This may seem a vague question, but I guess I am asking if there are any common traps to be aware of when appending fields, or updating records with csv imports where linkages exist.

I was hoping I could just turn of the export of the linkage fields to avoid any data handling issues.

regards

Glenn

[David Butenhof]

I am just wondering that when exporting and importing CSV into forms when updating or appending data where form linkages exist, is there anything special that needs to be done with record_ids that store the linkage information.

The record in the child form included UUID columns for the parent form, the parent field that refers to the child, and the parent record ID. If you export a linked child form, you’ll see those columns, and you have to make sure you don’t change them if you’re planning to add data (such as your scheme of adding columns) and re-import into the same form. (And if you delete and re-create the parent, then those values will all change.)

This was the part I had to work out manually (thanks to Brendan’s information on the TapForms sqlite schema) in order to generate and import child forms from my Bento Simple List data. The big step is understanding what’s happening under the covers, and one of the fantastic things about TapForms is that we can directly talk to the developer who’s willing to share information like that.

Your question is a little vague, and I’m not entirely certain what you want to do (it sounds like you might actually want to avoid re-importing with linkages?), but in any case I hope this helps you understand what the data means.

[Author]

Posts