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]
> <javascript:>> 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] <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.
