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.
