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