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/774e67a8-6492-48d4-83cd-0aba959439ea%40googlegroups.com.
