Awesome, thank you very much. Especially a big +1 for the 'UNION ALL' directive! :)
On Wednesday, 4 January 2017 16:54:47 UTC-5, Adam Cox wrote: > > This is what Lucy was so graciously referring to: > https://arches-hip.readthedocs.io/en/latest/extra/#from-ms-excel-workbook > :) > > You are most of the way there, but pay close attention to the GROUPID. > Technically what you have written will work fine, but if you try to add two > attributes to the same resource that should be grouped (like name and name > type, as is illustrated in the documentation above) you will need to set > the groupids for those resources to match. > > As for relationships, that will be tricky. In the past, I've written small > python scripts to read from tables and create the .relations file. > Generally these scripts are very specific though, so not really applicable > outside of their initial use, and I haven't used SQL for that at all. Note > that you will need to use the RESOURCEIDs from your .arches file in the > .relations file. > > Perhaps others have some good SQL samples for achieving this task. > > Good luck! Migrating data is often a very complex task, so I'm interested > to hear how it goes for you. > > On Wed, Jan 4, 2017 at 1:46 PM, Vincent Meijer <meijer....@gmail.com > <javascript:>> wrote: > >> Where can I find Adam Cox's excellent guide to creating a .Arches file? :) >> >> I am importing an old database that is a huge mess, and it seems I will >> need to prepare a .arches file by querying every column of every table into >> a .arches format. >> E.g. this query: >> >> select CONCAT('HERITAGE_RESOURCE_', SpecimenID) as "RESOURCEID", >> 'HERITAGE_RESOURCE.E18' as "RESOURSETYPE", >> 'NAME.E41' as "ATTRIBUTENAME", >> CommonName as "ATTRIBUTEVALUE", >> 'NAME.E41' as "GROUPID" >> from dbo.Specimens; >> >> will result in: >> >> RESOURCEID RESOURCETYPE ATTRIBUTENAME ATTRIBUTEVALUE GROUPID >> HERITAGE_RESOURCE_4 HERITAGE_RESOURCE.E18 NAME.E41 Hot Springs >> NAME.E41 >> HERITAGE_RESOURCE_5 HERITAGE_RESOURCE.E18 NAME.E41 Sapsuk NAME.E41 >> HERITAGE_RESOURCE_6 HERITAGE_RESOURCE.E18 NAME.E41 Sapsuk NAME.E41 >> HERITAGE_RESOURCE_7 HERITAGE_RESOURCE.E18 NAME.E41 Adamagan NAME.E41 >> >> >> >> And then of course there are the relations... >> >> Any advise/tactics/example sql queries are very welcome! :D >> >> >> >> On Wednesday, 17 February 2016 12:46:48 UTC-5, Adam Cox wrote: >>> >>> Hi Lucy, I have come to a similar conclusion in the past. It's been >>> beneficial to batch import a single .arches file with a good deal of >>> information in it, but with no attempt to get absolutely everything. >>> Programming a way to convert some of the extra random pieces to .arches >>> seemed too costly (time-costly) compared to the relative ease of entering >>> that information through the arches interface. However, populating the >>> entire database with a core of base data that could be added to later is >>> extremely valuable. >>> >>> Also, heads-up! In v4 it looks like >>> <https://github.com/archesproject/arches/wiki/Import-Export> the >>> .arches format will give way to a more straightforward import approach. >>> That won't be relevant to you for a while, but just something to keep in >>> mind before you spend extra time building processes for .arches that you >>> expect to use for years... >>> >>> Adam >>> >>> On Wed, Feb 17, 2016 at 4:07 AM, Lucinda Fletcher-Jones < >>> luci...@gmail.com> wrote: >>> >>>> Hi Adam, >>>> >>>> Your help is always appreciated and I really like the MS Excel query >>>> method of converting the excel file into an .arches file but that >>>> perennial >>>> problem, diacritics, is rearing its ugly head again. The MS query cannot >>>> import the diacritics from the Excel file (I have tried many times using 2 >>>> different versions of Excel) and so adding those extra rows as you suggest >>>> below would be necessary for every resource as we have the Arabic name and >>>> an English name with diacritics for each. It probably wouldn't take that >>>> long but allows for human error to creep in... >>>> >>>> I also looked into using Open Office. By googling, I found a method by >>>> which I could use OO Base, import the Excel file and create a query from >>>> that. But, the modified query is not working at present. I have some >>>> syntax >>>> problems but I also believe that this method can only handle very simple >>>> queries. >>>> As for the macros, I don't know python and my Javascript is rusty, so I >>>> might leave that to a new programmer coming on board soon. >>>> I am rapidly coming to the conclusion that a combination of batch input >>>> through .arches files and user input might be best for our project! >>>> >>>> >>>> >>>> >>>> On Monday, February 15, 2016 at 7:42:42 PM UTC+2, Adam Cox wrote: >>>>> >>>>> Hi Lucy, personally I'd recommend modifying (a copy of!) the excel >>>>> file, because then you can continually recreate the .arches file from >>>>> it--I've always found it beneficial to spend the most time on replicable >>>>> steps rather than one time (copy/paste) operations. >>>>> >>>>> Here's something that could help the process, and I'll use the Name, >>>>> Name Type as an example: >>>>> >>>>> If you have a column of names, and you know that they are all primary >>>>> names, you can avoid creating a new column where every value is >>>>> "NAME_TYPE:1" by just hard-coding that term into a statement in the >>>>> query. >>>>> So, your new statement would not reference a column name, but would just >>>>> have "NAME_TYPE:1" written into it. In the example I made, the geometry >>>>> column shows an example of combining strings, 'POINT (', with column >>>>> names, lat. So that may be helpful for reference. >>>>> >>>>> A couple of other points related to this: >>>>> >>>>> If you have begun to use open office, it would be really great to see >>>>> if this same SQL stuff can be used there. Just a word of warning, even >>>>> though it's all SQL, the syntax (double vs. single quotes, the use of AS) >>>>> may differ between Excel and Open Office. >>>>> >>>>> Also, it occurs to me that once you have created a spreadsheet that is >>>>> formatted like a .arches file, you could probably do some pretty nimble >>>>> things with it by using the sorting and filtering capabilities that Excel >>>>> has and I'm sure open office has. A .arches file is just a bunch of rows >>>>> with two levels of sorting: first by RESOURCEID and second by GROUPID. >>>>> To >>>>> add a line to a specific resource, you could just filter the rows based >>>>> on >>>>> a RESOURCEID, add your new row or two, and then remove the filter, >>>>> re-sort, >>>>> and you will have properly "inserted" a row. >>>>> >>>>> Finally, I just downloaded Open Office, and it looks like you can >>>>> write Python or Javascript macros. This is pretty huge (if you're into >>>>> that sort of thing) and a simple python or js macro could be used in >>>>> place >>>>> of the SQL query I made. Sharing open office macros is probably really >>>>> easy, so this would be a great way to collaborate. >>>>> >>>>> Good luck! >>>>> >>>>> >>>>> On Sunday, February 14, 2016 at 6:19:35 AM UTC-6, Lucy FJ wrote: >>>>>> >>>>>> Hi Adam, >>>>>> >>>>>> I have now thoroughly read the documentation on .Arches file and have >>>>>> played around with the Microsoft query and have a much better idea of >>>>>> what >>>>>> I need to do, so there is no need to answer some of the questions I >>>>>> asked >>>>>> below! >>>>>> >>>>>> It seems that you either need to do a lot of manipulation of the >>>>>> excel file or a lot of copy and pasting in the .Arches file which will >>>>>> then >>>>>> be enormous even for our modest database! >>>>>> >>>>>> >>>>>> >>>>>> Lucy >>>>>> >>>>>> >>>>>> >>>>>> On Sunday, February 14, 2016 at 11:01:23 AM UTC+2, Lucy FJ wrote: >>>>>>> >>>>>>> Hi Adam, >>>>>>> >>>>>>> Finally I am back testing out creating an .Arches file (but I am not >>>>>>> able to load them yet and see the results) and I do have further >>>>>>> questions >>>>>>> about adding alternative names and several resource classifications. >>>>>>> >>>>>>> Just to be clear that I understood you correctly, the group ID keeps >>>>>>> records that are related within a heritage resource together, and the >>>>>>> resource ID ensures that all data for a particular resource is kept >>>>>>> together? Therefore, each alternative name/nametype would have a >>>>>>> separate >>>>>>> Group ID from the preferred name/nametype, but the same Resource ID? >>>>>>> >>>>>>> Secondly, I am not very familiar with Microsoft query, but it seems >>>>>>> that the original Excel file for creating the query from, can only have >>>>>>> one >>>>>>> 'row' per record or archaeological site in our case. So as you >>>>>>> suggested, >>>>>>> we would need to put in a new column for each alternative name we have >>>>>>> and >>>>>>> for each site type classification and of course modify the query to add >>>>>>> more statements for the extra names etc. I am looking for a method of >>>>>>> reading the Excel file in the way we have set up with all the >>>>>>> alternative >>>>>>> name being in *one* column, as it would be more efficient for us - >>>>>>> sometimes we have 10 alternative names but I don't think Microsoft >>>>>>> Query >>>>>>> can handle this. >>>>>>> >>>>>>> I don't know if you have any further thoughts on this or know how >>>>>>> others have handles this. >>>>>>> Thank you very much for your help, which is always appreciated! >>>>>>> Lucy >>>>>>> >>>>>>> On Monday, January 18, 2016 at 7:27:24 PM UTC+2, Adam Cox wrote: >>>>>>>> >>>>>>>> Hi Lucy, I'm happy to hear the documentation was helpful. >>>>>>>> >>>>>>>> To add more attributes to your .arches file, you'll just need to >>>>>>>> add more statements to the SQL query. First, because the SQL window >>>>>>>> is >>>>>>>> pretty cramped in Excel, I'd recommend pasting your query into Notepad >>>>>>>> ++ >>>>>>>> so it's easier to work with. >>>>>>>> >>>>>>>> If you look at the two statements that define Name and Name_Type, >>>>>>>> you'll see that they both reference "group1". What you want to do is >>>>>>>> make >>>>>>>> more groups, so I'd recommend copying and pasting the Name_Type >>>>>>>> statement >>>>>>>> from the existing query (because it is the most standard) and pasting >>>>>>>> it >>>>>>>> below, as many times as you need--one time per new attribute that you >>>>>>>> need >>>>>>>> to add. Then just make sure to set the group value appropriately: if >>>>>>>> you >>>>>>>> have an alternate name, you'll have to make a new column for that >>>>>>>> name, and >>>>>>>> make a column with the correct conceptid (or hard-code the conceptid >>>>>>>> into >>>>>>>> the new statement), and then put "group3" into each statement. Or, if >>>>>>>> you >>>>>>>> just have a single column for a standalone attribute like "Site Type", >>>>>>>> make >>>>>>>> sure it has its own unique group ID. >>>>>>>> >>>>>>>> I hope that makes sense. I have to run now, but if you have more >>>>>>>> questions don't hesitate to ask them! >>>>>>>> >>>>>>>> Adam >>>>>>>> >>>>>>>> On Sunday, January 17, 2016 at 12:17:31 PM UTC-6, Lucinda >>>>>>>> Fletcher-Jones wrote: >>>>>>>>> >>>>>>>>> >>>>>>>>> <https://lh3.googleusercontent.com/-Qu9c0GYT4ig/VpvaZj1w5yI/AAAAAAAAFXo/aMjV0c5GLJg/s1600/Dabanarti.JPG> >>>>>>>>> >>>>>>>>> Hi all, >>>>>>>>> >>>>>>>>> I have worked through the example in Adam Cox's excellent guide to >>>>>>>>> creating a .Arches file from a Microsoft Excel file which is where we >>>>>>>>> have >>>>>>>>> all our archaeological site data at present, and it worked very well. >>>>>>>>> Now I >>>>>>>>> have a question. Our data, as everyone else's is a little more >>>>>>>>> complicated >>>>>>>>> than the example. How do you deal with a site that has more than one >>>>>>>>> name >>>>>>>>> and more than one classification etc? for example: Dabanarti with >>>>>>>>> three >>>>>>>>> names and three site types. >>>>>>>>> >>>>>>>>> >>>>>>>>> <https://lh3.googleusercontent.com/-Qu9c0GYT4ig/VpvaZj1w5yI/AAAAAAAAFXo/aMjV0c5GLJg/s1600/Dabanarti.JPG> >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> Thank you very much, Lucy >>>>>>>>> >>>>>>>>> >>>>>>>>> -- >>>> -- To post, send email to arches...@googlegroups.com. To unsubscribe, >>>> send email to archesprojec...@googlegroups.com. For more information, >>>> visit https://groups.google.com/d/forum/archesproject?hl=en >>>> --- >>>> You received this message because you are subscribed to the Google >>>> Groups "Arches Project" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to archesprojec...@googlegroups.com. >>>> For more options, visit https://groups.google.com/d/optout. >>>> >>> >>> -- >> -- To post, send email to arches...@googlegroups.com <javascript:>. To >> unsubscribe, send email to archesprojec...@googlegroups.com <javascript:>. >> For more information, visit >> https://groups.google.com/d/forum/archesproject?hl=en >> --- >> You received this message because you are subscribed to the Google Groups >> "Arches Project" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to archesprojec...@googlegroups.com <javascript:>. >> For more options, visit https://groups.google.com/d/optout. >> > > -- -- To post, send email to archesproject@googlegroups.com. To unsubscribe, send email to archesproject+unsubscr...@googlegroups.com. For more information, visit https://groups.google.com/d/forum/archesproject?hl=en --- You received this message because you are subscribed to the Google Groups "Arches Project" group. To unsubscribe from this group and stop receiving emails from it, send an email to archesproject+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
