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 <[email protected]> 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/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 [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/CA%2BZLqy8T%2BNoFxeV3j42U_dYz0y0BP1HKF6cF9nxz_e%2BBeDpymQ%40mail.gmail.com.
