Re: [Arches] Re: Creating .Arches files

[Vincent Meijer]

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  > 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 ATTRIBUTENAMEATTRIBUTEVALUE  GROUPID
>> HERITAGE_RESOURCE_4  HERITAGE_RESOURCE.E18NAME.E41Hot 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.E41Adamagan 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 
>>>  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 

Re: [Arches] Re: Creating .Arches files

[Adam Cox]

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
 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  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 

[Arches] Re: Creating .Arches files

[Lucinda Fletcher-Jones]

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 

[Arches] Re: Creating .Arches files

['Lucy FJ' via Arches Project]

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:
>>>
>>>
>>> 
>>>
>>> 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. 
>>>
>>>
>>> 
>>>
>>>
>>>
>>> Thank you very much, Lucy
>>>
>>>
>>>

-- 
-- 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.

[Arches] Re: Creating .Arches files

[Adam Cox]

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:
>
>
> 
>
> 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. 
>
>
> 
>
>
>
> Thank you very much, Lucy
>
>
>

-- 
-- 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.