Sharing select records via CSV export/import

For HanDBase related topics that are not specific to one platform.

Sharing select records via CSV export/import

Postby jensen.todd » Wed Dec 10, 2014 12:15 pm

I want the ability to filter for particular records, email only those specific records (not the entire .pdb file) to a friend, and have the friend be able to import them into his (identically structured) table.

Am I correct in my thinking that for this to work, I'll need to set the Default View (Or use Properties>Fields) to NOT export any computed fields? No calculated, conditional, relationship, link/linked, and probably others? In other words, fields that users populate directly should be included in the export, any field that HanDBase populates automatically should be excluded.
jensen.todd
 
Posts: 15
Joined: Wed Sep 29, 2010 1:23 pm

Re: Sharing select records via CSV export/import

Postby jensen.todd » Wed Dec 10, 2014 3:26 pm

I figured it out. As best I can tell, computed fields aren't a problem (HanDBase simply ignores the data from those columns of the CSV and computes it's own output). For others who may have similar questions in the future, below is my understanding. Maybe someone can chime in if I still have something wrong:

A CSV containing X columns will import correctly if the columns correspond to the order of the first X fields of the database (As listed under Properties>Change Field Order, or alternatively, as listed under Edit View>Default View>Field Settings). I say the first X fields because the CSV doesn't have to match the entire structure. For instance if the first 6 fields of your database are user input fields and the next 4 are calculated output fields, a 6 column CSV containing only the raw data is sufficient.

Note that a CSV export from HanDBase doesn't necessarily reveal the structure (order). It might, but if any fields are set to not be included in exports, there will be a discrepancy. Be careful, because the import may still succeed, but some of the data will appear in the wrong fields.
jensen.todd
 
Posts: 15
Joined: Wed Sep 29, 2010 1:23 pm

Re: Sharing select records via CSV export/import

Postby Brian_Houghton » Thu Dec 11, 2014 12:23 am

Hi,

Thanks for sharing this information. You are right that when dealing with exporting and importing of CSV files, one needs to be conscious of field ordering because it can throw of the import if not done properly!
Kind Regards,
Brian Houghton, DDH Software
Brian_Houghton
 
Posts: 2246
Joined: Wed May 20, 2009 8:30 am

Re: Sharing select records via CSV export/import

Postby Alanwestwood » Thu Dec 11, 2014 9:54 am

I use CSV import a lot, in fact nearly all my data is imported as opposed to entered manually. So, I've made all the mistakes - you can crash HanDBase for iOS by importing incorrect data to some field types. What Jensen says is all correct of course, but HanDBase has some other hidden secrets.

In fact HB uses the field order set in the current list view - not necessarily the default view. So if you wish you can design a list view called Import (eg) and make sure that it is selected before an import. The trouble is that if HanDBase is closed, it might select the wrong list view when opened for the Open-In command - in which case if the field order is different you'll have trouble. There is a safer workaround...

Create a duplicate copy of the database, delete all the records and all the list views except the import one. Always import to this database, and when you have checked the records simply copy or move them all to the original database. In years of CSV import, this has saved me many problems. Also, it avoids the need to have identically structured files - you may want slight differences for personal preference - all you have to do is design an import list view that will properly accept the incoming data. This way, I can in fact accept data to my databases from many different sources, I just need an import database for each source, it works a treat.

Having said all that, it would be of great help if we could define a list view as being the one selected for import or export. That would avoid the current confusion around exactly which field order is used - it took me a while (and several crashes and incorrect record imports) to fathom that :-)

Cheers, Alan.
Alanwestwood
 
Posts: 62
Joined: Mon Aug 15, 2011 12:43 am

Re: Sharing select records via CSV export/import

Postby jensen.todd » Thu Dec 11, 2014 12:02 pm

Thanks for the tips, Alan! I love the importer database idea! As a practical matter this seems like the only way to go, otherwise sooner or later I'll end up messing up my data. And it appears that it doesn't matter what view is selected in the Importer DB or the Primary DB when I execute that last step of "Copy Records To" or "Move Records To." Presumably the copy and move functions rely on the structural order rather than the display order.

Building on your concept, I've created an empty duplicate of my primary database called Customs. To accomplish my original objective of sharing only specific records with a friend, I first copy the records I wish to share into Customs. Then I email the Customs.PDB file to my friend. Then he can review them before copying or moving them to his primary database, as you suggested. So, for PDB-to-PDB transfers where the structures are identical, we can bypass the perils of CSV importing altogether. When I need to import data from a spreadsheet (or a non-identically structured PDB), I'll use Customs.PDB in the way you described.

Dave and Brian: Considering how prone to user errors the CSV import process is, is it worth considering adding data validation to the import process? Like, if all X column headings in the CSV don't precisely match the first X fields of the view the user was using last, a warning pops up and asks the user if he wants to proceed with the import anyway. I'll add that, now that I understand it, I really like the current functionality allowing us to import CSVs with several different layouts (ie, a different layout for each view).
jensen.todd
 
Posts: 15
Joined: Wed Sep 29, 2010 1:23 pm

Re: Sharing select records via CSV export/import

Postby Alanwestwood » Thu Dec 11, 2014 5:23 pm

Jensen, I agree, once you are clear about how it works the current functionality works very well - it is flexible and solid, though in my opinion rather hidden away. My primary sports training log accepts data in CSV format from several different tracking apps without issues - at least it does once I have set up the import list views and thoroughly tested them. And of course it's possible to use calculated fields to manipulate the incoming data. I find HanDBase very powerful used this way.

You're correct about copy and move. HB uses an internal data format which makes this totally independent of any field order, but the file structures do have to be identical - even small changes matter (HB will refuse the record transfer). But of course it's simple to recreate the import database after a structure change.

The chore of correcting a damaged database after an incorrect import shouldn't be underestimated. That's why I always recommend an import database to review the records first. Glad you like the idea :-)

Cheers, Alan.
Alanwestwood
 
Posts: 62
Joined: Mon Aug 15, 2011 12:43 am

Re: Sharing select records via CSV export/import

Postby dhaupert » Fri Dec 12, 2014 8:28 am

I like your idea of a separate database to import to as well with the copy to or move to option. There is one caveat which is that unique and link field types are auto generated, and can't really be moved from one db to the other. For example, your record may have a unique field in it that was 7 and then when it copies to the other database, it will be regenerated to whatever the next number in there is. For relational tables, this could pose a problem.

I think another alternative is to add a 'date record created' date stamp to your database. If you find that the import doesn't work well, you could then filter on that date and see only the records added and then delete them easily enough. That may be quicker and more flexible in the long run. What do you think?

Ideally a data import preview would be the best solution, I just don't know that I'll be able to add that any time soon.
dhaupert
 
Posts: 4111
Joined: Tue May 26, 2009 11:51 am

Re: Sharing select records via CSV export/import

Postby jensen.todd » Fri Dec 12, 2014 12:07 pm

dhaupert wrote:I think another alternative is to add a 'date record created' date stamp to your database. If you find that the import doesn't work well, you could then filter on that date and see only the records added and then delete them easily enough. That may be quicker and more flexible in the long run. What do you think?


Although that question is probably directed at Alan, I'll throw in a couple of cents anyway: I think I love HanDBase. It's intuitive enough that a person with no database experience can master it just by experimenting, without ever opening the instruction manual. Yet it's robust enough to offer multiple solutions to most problems users encounter. I like the date record created approach too. I think it may be faster to go this way for imports sent by and to expert-level users who have learned exactly how to format their CSVs and rarely find anything wrong with their imports anymore. The importer DB approach might be better if either are more casual users, though.
jensen.todd
 
Posts: 15
Joined: Wed Sep 29, 2010 1:23 pm

Re: Sharing select records via CSV export/import

Postby Alanwestwood » Fri Dec 12, 2014 7:26 pm

I think it's a bit 'horses for courses' Dave, similar to what Jensen says actually. I much prefer to review the incoming data where possible, before adding to the primary database. True, a date created field allows the data to be easily identified and deleted if necessary, but I've crashed HanDBase a few times now simply by having the wrong list view open. With a crash I always worry about my database being corrupted, though to date I have always been able to recover with few problems.

To be honest, I avoid unique fields and links. I favour relationships because I find them more robust, and they are much easier to recreate than links should I ever have to reload data from scratch. But I agree with you that for users with those types of field your suggestion may be better.

But that's the thing I really like about your software - there are nearly always alternative approaches, and a bit of experimentation will usually make it obvious which is the best for any particular situation - just as Jensen has discovered with regard to copy/move records. And I would add that since the addition of full support for Open-In, yous iOS app is now streets above the others for serious database users - who will of course want to get data to and from colleagues or other apps, most often via CSV files.

My one caveat is that I don't believe that the importance of the list view issue for CSV import and export is very clear. It's essential that it is fully understood if problems are to be avoided, and I'm not sure many users know about it - you'll have seen that other users have posted similarly to Jensen, even a very experienced one.

Cheers, Alan.
Alanwestwood
 
Posts: 62
Joined: Mon Aug 15, 2011 12:43 am


Return to HanDBase General Discussion

Who is online

Users browsing this forum: No registered users and 1 guest

cron