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.
