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 <[email protected] > <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 < >>> [email protected]> 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 [email protected]. To unsubscribe, >>>> send email to [email protected]. 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 [email protected]. >>>> For more options, visit https://groups.google.com/d/optout. >>>> >>> >>> -- >> -- To post, send email to [email protected] <javascript:>. To >> unsubscribe, send email to [email protected] <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 [email protected] <javascript:>. >> For more options, visit https://groups.google.com/d/optout. >> > > -- -- To post, send email to [email protected]. To unsubscribe, send email to [email protected]. 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
