Hi Martha, I think you're looking for the wrong character. In your initial post the stack trace indicated the "broken bar" character. see http://www.fileformat.info/info/unicode/char/a6/index.htm
Try searching for that character instead. Cheers, Alexei Director of Web Development - Farallon Geographics, Inc. - 971.227.3173 On Wed, Oct 2, 2019 at 4:49 PM Adam Lodge <alo...@fargeo.com> 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 <martha.se...@lacity.org> 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 <alo...@fargeo.com> 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 <marth...@lacity.org> 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 <alo...@fargeo.com> 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 <marth...@lacity.org> 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 arches...@googlegroups.com. To unsubscribe, >>>>> send email to arches...@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 arches...@googlegroups.com. >>>>> 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 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. > 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 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. > To view this discussion on the web visit > https://groups.google.com/d/msgid/archesproject/6035AE74-94AB-4CB3-AF85-8B73B3D9892F%40fargeo.com > <https://groups.google.com/d/msgid/archesproject/6035AE74-94AB-4CB3-AF85-8B73B3D9892F%40fargeo.com?utm_medium=email&utm_source=footer> > . > -- -- 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. To view this discussion on the web visit https://groups.google.com/d/msgid/archesproject/CA%2BZLqy8T%2BNoFxeV3j42U_dYz0y0BP1HKF6cF9nxz_e%2BBeDpymQ%40mail.gmail.com.
