That worked, Adam, I now have what I need to put together my select statement. I now "get" the database to a much greater extent than before.
Thanks again, Martha On Wednesday, October 2, 2019 at 10:21:35 PM UTC-7, Martha S wrote: > > Thanks, Adam, > > That should get me the last bit of the way. > > Martha > > > > On Wednesday, October 2, 2019 at 4:49:01 PM UTC-7, Adam Lodge wrote: >> >> Martha, >> >> I wish I could help with understanding why json export is working and csv >> isn’t. Unfortunately, I can’t. That said, toward working with your json >> output, you can identify the uuid of nodes/fields that store geometry with >> this sql: >> >> select >> b.name as model_name, >> a.name as node_name, >> a.nodeid >> FROM nodes a >> join graphs b on a.graphid = b.graphid >> where >> datatype = 'geojson-feature-collection' >> order by b.name, a.name >> >> With that information, you can search for that uuid in your json to >> isolate where geometry is stored in the json. >> >> --- >> Adam Lodge >> Farallon Geographics >> >> >> On Oct 2, 2019, at 4:24 PM, Martha S <[email protected]> wrote: >> >> One more bit of information: I was able to export the business data to >> json. Naturally, I did not find '|' in the resulting file. >> >> If this export includes geographies (or lack thereof, which is what I'm >> really after) for this resource model, I might be able to query what I need >> based on the tools you have already provided me here and export that to CSV. >> >> I'm still interested in any further guidance you might offer regarding my >> inability to export business data to csv, even as I pursue this new avenue. >> >> Thanks, >> Martha >> >> >> On Wednesday, October 2, 2019 at 3:37:45 PM UTC-7, Martha S wrote: >>> >>> Alas, Gentlemen, neither effort returned anything. In PGADMIN4, I ran >>> both of the following with no results returned: >>> >>> SELECT >>> >>> a.resourceinstanceid, >>> >>> b.name as card_name >>> >>> FROM tiles a >>> >>> JOIN cards b on a.nodegroupid = b.nodegroupid >>> >>> WHERE 1=1 >>> >>> and tiledata::text like '%|%' >>> >>> >>> set standard_conforming_strings=on; >>> >>> SELECT >>> >>> a.resourceinstanceid, >>> >>> b.name as card_name >>> >>> FROM tiles a >>> >>> JOIN cards b on a.nodegroupid = b.nodegroupid >>> >>> WHERE 1=1 >>> >>> and tiledata::text like E'%xa6%' >>> >>> >>> In the python shell I ran the following with no results returned: >>> >>> >>> from arches.app.models.tile import Tile >>> tiles = Tile.objects.all() >>> for tile in tiles: >>> >>> for value in tile.data.values(): >>> >>> if isinstance(value, unicode): >>> if "|" in value: >>> print tile.resourceinstance_id >>> >>> >>> At least you are agreed, there is no there there. Just for grins, I >>> reran the export business data command and got the same error as shown in >>> the first post in this thread. As a final effort, I ran the following and >>> got more than 10,000 results, since that's all that stayed in the terminal >>> memory: >>> >>> >>> from arches.app.models.tile import Tile >>> >>> tiles = Tile.objects.all() >>> >>> for tile in tiles: >>> >>> for value in tile.data.values(): >>> >>> if isinstance(value, unicode): >>> >>> print tile.resourceinstance_id, value >>> >>> >>> Below is a partial list of the results. I don't see anything suspicious >>> in the values, but I see that these few resources have multiple entries in >>> this list. I don't know if that's because this list list is a complete dump >>> of all 1M+ cards or if there's something to see here. I ran reports on a >>> few of these and found nothing amiss. >>> >>> fd44f81a-14b1-41f9-b33a-d66c66a960ec 33595d18-c941-4a4b-91e8-0fa08e229c4d >>> 52c5f05e-1827-4b06-909b-9a66ff3e6e95 3db588f4-2384-4cbe-b429-5c63630b9b9d >>> 52c5f05e-1827-4b06-909b-9a66ff3e6e95 138B177 25 >>> 52c5f05e-1827-4b06-909b-9a66ff3e6e95 08130495-9729-46fa-8a3b-2dfa97286ab9 >>> 52c5f05e-1827-4b06-909b-9a66ff3e6e95 679152 >>> fd44f81a-14b1-41f9-b33a-d66c66a960ec 0997ad7a-ad94-43b3-b662-8945c6c1b10e >>> fd44f81a-14b1-41f9-b33a-d66c66a960ec fd44f81a-14b1-41f9-b33a-d66c66a960ec >>> 52c5f05e-1827-4b06-909b-9a66ff3e6e95 f5d15b7d-da92-4b63-82a1-8632b361e860 >>> fd44f81a-14b1-41f9-b33a-d66c66a960ec 3db588f4-2384-4cbe-b429-5c63630b9b9d >>> fd44f81a-14b1-41f9-b33a-d66c66a960ec 117B181 568 >>> 99b6ae9f-a061-46f4-b69c-f6f4b43ad69a 6137 E GARRISON DR >>> 99b6ae9f-a061-46f4-b69c-f6f4b43ad69a 209c5206-eb2f-406e-b42e-288f84680a4a >>> 52c5f05e-1827-4b06-909b-9a66ff3e6e95 411 N SPAULDING AVE >>> 52c5f05e-1827-4b06-909b-9a66ff3e6e95 209c5206-eb2f-406e-b42e-288f84680a4a >>> fd44f81a-14b1-41f9-b33a-d66c66a960ec 3939 S URSULA AVE >>> fd44f81a-14b1-41f9-b33a-d66c66a960ec 209c5206-eb2f-406e-b42e-288f84680a4a >>> 29a5a1ce-bfd1-4f8a-90a8-41e76b337558 2608 S ORCHARD AVE >>> 29a5a1ce-bfd1-4f8a-90a8-41e76b337558 ad7fce52-1185-4ad8-bc65-e100de19be96 >>> 52c5f05e-1827-4b06-909b-9a66ff3e6e95 Orange Grove Avenue-Gardner Street >>> Multi-Family Residential Historic District - Contributor >>> 52c5f05e-1827-4b06-909b-9a66ff3e6e95 efd0fd4d-ebcf-4e7d-801f-0dc036a0c1b3 >>> fd44f81a-14b1-41f9-b33a-d66c66a960ec Baldwin Plaza-Sunset Fields >>> Historic District - Contributor >>> fd44f81a-14b1-41f9-b33a-d66c66a960ec efd0fd4d-ebcf-4e7d-801f-0dc036a0c1b3 >>> >>> So, is there a middle ground here, where I can get more than nothing and >>> less than all? Is there a way for me to log the bad records and continue >>> with the export nonetheless? >>> >>> Somehow, I need to correct the issues and successfully export data from >>> the database. >>> >>> Thanks in advance, >>> Martha >>> >>> >>> On Wednesday, October 2, 2019 at 10:40:31 AM UTC-7, Martha S wrote: >>>> >>>> Thank you,to both Adams. >>>> >>>> I will run both suggestions. Both are helpful to my ongoing database >>>> activities. The more approaches in my toolbox, the better. >>>> >>>> It is great to know that the cards can be treated as flat files with >>>> tiledata; I missed that somehow. Iterating through each record with the >>>> ORM >>>> approach in the python shell is a new entry point for me. >>>> >>>> I shall report back later today. >>>> >>>> Martha >>>> >>>> >>>> >>>> On Tuesday, October 1, 2019 at 10:57:40 PM UTC-7, Adam Cox wrote: >>>>> >>>>> Another approach would be to use the django ORM in the python shell. >>>>> >>>>> From your project run >>>>> >>>>> python manage.py shell >>>>> >>>>> Now you can paste this code in >>>>> >>>>> from arches.app.models.tile import Tile >>>>> tiles = Tile.objects.all() >>>>> for tile in tiles: >>>>> for value in tile.data.values(): >>>>> if isinstance(value, unicode): >>>>> if "|" in value: >>>>> print tile.resourceinstance_id >>>>> print value >>>>> >>>>> Adam >>>>> >>>>> On Tue, Oct 1, 2019 at 11:26 PM Adam Lodge <[email protected]> wrote: >>>>> >>>>>> I realized that I inadvertently took this discussion offline to just >>>>>> Martha and I. Here's some missing thread: >>>>>> >>>>>> Martha, >>>>>> >>>>>> (I think) you can issue this sql statement against the Postgres >>>>>> database behind arches to identify the specific resource instance and >>>>>> “card” that contains a given offending character: >>>>>> >>>>>> SELECT >>>>>> a.resourceinstanceid, >>>>>> b.name as card_name >>>>>> FROM tiles a >>>>>> JOIN cards b on a.nodegroupid = b.nodegroupid >>>>>> WHERE 1=1 >>>>>> and tiledata::text like '%|%’ >>>>>> >>>>>> Note that the offending character you search for will be defined on >>>>>> the last line surrounded by wildcards. >>>>>> >>>>>> With the resourceinstanceid value, you can construct a url that will >>>>>> take you strait to the resource editor for that specific resource >>>>>> instance… >>>>>> like this: >>>>>> https://[hostname]/resource/[resourceinstanceid] >>>>>> >>>>>> You can use the card value to navigate to the specific card (or form) >>>>>> that has the field with the offending value. >>>>>> >>>>>> It’s a kinda manual approach to fixing them, but at least you can >>>>>> sniff them out with this approach. >>>>>> >>>>>> Best, >>>>>> Adam >>>>>> >>>>>> --- >>>>>> Adam Lodge >>>>>> Geospatial Systems Consultant >>>>>> Farallon Geographics >>>>>> >>>>>> On Oct 1, 2019, at 7:10 PM, Martha Selig <[email protected]> wrote: >>>>>> >>>>>> Adam, >>>>>> >>>>>> I'm talking about the Arches database, though I am open to any >>>>>> suggestion as to simplifying this task. I am unable to export to CSV >>>>>> because of this symbol, but a flat file would definitely be the easiest >>>>>> way >>>>>> to look for and replace/delete it no matter where it is found.I would >>>>>> like >>>>>> to deal with this issue one time, if at all possible. We're starting a >>>>>> cycle of reviewing, updating, and correcting data and I'm stuck at the >>>>>> starting gate. >>>>>> >>>>>> Tomorrow I'm going to try to query just the records needed for the >>>>>> first pass -- resources that don't have geographies defined -- and see >>>>>> if I >>>>>> can export those somehow. If I'm lucky, none of the records I need will >>>>>> have '|' in them, so I can get a CSV. At least I can get the client >>>>>> going. >>>>>> >>>>>> Any help would be greatly appreciated. That would include, I suppose, >>>>>> some info on modifying the export code to skip to the next entry when >>>>>> this >>>>>> error is triggered. I haven't taken a look at the code to see how >>>>>> straightforward that might be, but if I could log the problem record and >>>>>> keep on going, that would be swell. I wasn't thinking I'd be altering >>>>>> core >>>>>> code but if that keeps me going, why not? >>>>>> >>>>>> Thanks, >>>>>> Martha >>>>>> >>>>>> On Tue, Oct 1, 2019 at 5:59 PM Adam Lodge <[email protected]> wrote: >>>>>> >>>>>>> Are you searching an Arches database, or just table in a given >>>>>>> RDBMS, or a flat file of some sort? >>>>>>> >>>>>>> --- >>>>>>> Adam Lodge >>>>>>> >>>>>>> On Oct 1, 2019, at 2:49 PM, Martha S <[email protected]> wrote: >>>>>>> >>>>>>> Do anyone have a recommendation for the best way to go through the >>>>>>> database of >110,000 records to find all instances of the '|' in any >>>>>>> field >>>>>>> it might occur? I am told this was the delimiter used in .arches files, >>>>>>> so >>>>>>> there could be other instances sprinkled throughout the database. >>>>>>> >>>>>>> Needle in a haystack time. >>>>>>> >>>>>>> Thanks, >>>>>>> Martha >>>>>>> >>>>>>> >>>>>> >>>>>> On Tuesday, October 1, 2019 at 2:49:10 PM UTC-7, Martha S wrote: >>>>>>> >>>>>>> Do anyone have a recommendation for the best way to go through the >>>>>>> database of >110,000 records to find all instances of the '|' in any >>>>>>> field >>>>>>> it might occur? I am told this was the delimiter used in .arches files, >>>>>>> so >>>>>>> there could be other instances sprinkled throughout the database. >>>>>>> >>>>>>> Needle in a haystack time. >>>>>>> >>>>>>> Thanks, >>>>>>> Martha >>>>>>> >>>>>>> On Tuesday, October 1, 2019 at 10:35:47 AM UTC-7, Martha S wrote: >>>>>>>> >>>>>>>> Thank you, Alexi, >>>>>>>> >>>>>>>> I'll just have to hope that's the only "special" character in the >>>>>>>> data. >>>>>>>> >>>>>>>> Martha >>>>>>>> >>>>>>>> On Friday, September 27, 2019 at 5:13:59 PM UTC-7, Martha S wrote: >>>>>>>>> >>>>>>>>> I am trying to export all the data for a particular resource model >>>>>>>>> to CSV for review and modification and ran into an error during the >>>>>>>>> process >>>>>>>>> -- UnicodeEncodeError: 'ascii' codec can't encode character >>>>>>>>> u'\xa6' in position 51: ordinal not in range(128) >>>>>>>>> >>>>>>>>> *My command* >>>>>>>>> python manage.py packages -o export_business_data -d >>>>>>>>> '/hpladata/Projects/Downloads/Historic District Mapping Files' -f >>>>>>>>> 'csv' -c '/hpladata/Projects/Downloads/Historic District Mapping >>>>>>>>> Files/Historic District.mapping' >>>>>>>>> >>>>>>>>> *Here's the full error dump* >>>>>>>>> operation: export_business_data >>>>>>>>> Traceback (most recent call last): >>>>>>>>> File "manage.py", line 29, in <module> >>>>>>>>> execute_from_command_line(sys.argv) >>>>>>>>> File >>>>>>>>> "/usr/local/lib/python2.7/dist-packages/django/core/management/__init__.py", >>>>>>>>> >>>>>>>>> line 364, in execute_from_command_line >>>>>>>>> utility.execute() >>>>>>>>> File >>>>>>>>> "/usr/local/lib/python2.7/dist-packages/django/core/management/__init__.py", >>>>>>>>> >>>>>>>>> line 356, in execute >>>>>>>>> self.fetch_command(subcommand).run_from_argv(self.argv) >>>>>>>>> File >>>>>>>>> "/usr/local/lib/python2.7/dist-packages/django/core/management/base.py", >>>>>>>>> >>>>>>>>> line 283, in run_from_argv >>>>>>>>> self.execute(*args, **cmd_options) >>>>>>>>> File >>>>>>>>> "/usr/local/lib/python2.7/dist-packages/django/core/management/base.py", >>>>>>>>> >>>>>>>>> line 330, in execute >>>>>>>>> output = self.handle(*args, **options) >>>>>>>>> File >>>>>>>>> "/Projects/prod/arches/arches/management/commands/packages.py", >>>>>>>>> line 190, in handle >>>>>>>>> self.export_business_data(options['dest_dir'], >>>>>>>>> options['format'], options['config_file'], options['graphs'], >>>>>>>>> options['single_file']) >>>>>>>>> File >>>>>>>>> "/Projects/prod/arches/arches/management/commands/packages.py", >>>>>>>>> line 770, in export_business_data >>>>>>>>> data = resource_exporter.export(graph_id=graph, >>>>>>>>> resourceinstanceids=None) >>>>>>>>> File >>>>>>>>> "/Projects/prod/arches/arches/app/utils/data_management/resources/exporter.py", >>>>>>>>> >>>>>>>>> line 37, in export >>>>>>>>> resources = self.writer.write_resources(graph_id=graph_id, >>>>>>>>> resourceinstanceids=resourceinstanceids) >>>>>>>>> File >>>>>>>>> "/Projects/prod/arches/arches/app/utils/data_management/resources/formats/csvfile.py", >>>>>>>>> >>>>>>>>> line 194, in write_resources >>>>>>>>> csvs_for_export = csvs_for_export + >>>>>>>>> self.write_resource_relations(file_name=self.file_name) >>>>>>>>> File >>>>>>>>> "/Projects/prod/arches/arches/app/utils/data_management/resources/formats/csvfile.py", >>>>>>>>> >>>>>>>>> line 215, in write_resource_relations >>>>>>>>> csvwriter.writerow({k:str(v) for k,v in relation.items()}) >>>>>>>>> File >>>>>>>>> "/Projects/prod/arches/arches/app/utils/data_management/resources/formats/csvfile.py", >>>>>>>>> >>>>>>>>> line 215, in <dictcomp> >>>>>>>>> csvwriter.writerow({k:str(v) for k,v in relation.items()}) >>>>>>>>> UnicodeEncodeError: 'ascii' codec can't encode character u'\xa6' >>>>>>>>> in position 51: ordinal not in range(128) >>>>>>>>> >>>>>>>>> Any suggestions? >>>>>>>>> >>>>>>>>> Thanks, >>>>>>>>> Martha >>>>>>>>> >>>>>>>> >>>>>> -- >>>>>> -- 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]. >>>>>> To view this discussion on the web visit >>>>>> https://groups.google.com/d/msgid/archesproject/412ef4fb-306b-4961-93f4-f9164065caaa%40googlegroups.com >>>>>> >>>>>> <https://groups.google.com/d/msgid/archesproject/412ef4fb-306b-4961-93f4-f9164065caaa%40googlegroups.com?utm_medium=email&utm_source=footer> >>>>>> . >>>>>> >>>>> >> -- >> -- 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]. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/archesproject/d5d0ea82-d653-4112-848a-0a6f02437e3c%40googlegroups.com >> >> <https://groups.google.com/d/msgid/archesproject/d5d0ea82-d653-4112-848a-0a6f02437e3c%40googlegroups.com?utm_medium=email&utm_source=footer> >> . >> >> >> -- -- 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]. To view this discussion on the web visit https://groups.google.com/d/msgid/archesproject/edc306e0-5659-4fec-b0c4-5aaaae8e103d%40googlegroups.com.
