Re: [Arches] Re: Error Exporting Business Data from Arches 4.4.2

[Adam Cox]

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  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  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  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  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 
 execute_from_command_line(sys.argv)
   File 
 "/usr/local/lib/python2.7/dist-packages/django/core/management/__init__.py",
 line 364, in 

[Arches] Re: Error Exporting Business Data from Arches 4.4.2

[Adam Lodge]

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  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  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  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 
>>> 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 

[Arches] Re: Error Exporting Business Data from Arches 4.4.2

[Martha S]

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 
>> 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 
>> 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 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/692c67b9-a481-40d8-bc2a-9a04fb1da65b%40googlegroups.com.

[Arches] Re: Error Exporting Business Data from Arches 4.4.2

[Martha S]

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 
> 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 
> 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 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/ad9f6944-9e07-4a8c-aa9a-0fcf49766e97%40googlegroups.com.

Re: [Arches] Error Exporting Business Data from Arches 4.4.2

[Alexei Peters]

Hi Martha,
It looks like somewhere you have a "broken bar" character in your data (
https://www.fileformat.info/info/unicode/char/00a6/index.htm)

If you can find it and remove it then that might solve the problem.
Hope that points you in the right direction.
Cheers,
Alexei

Director of Web Development - Farallon Geographics, Inc. - 971.227.3173


On Fri, Sep 27, 2019 at 5:14 PM 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 
> 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 
> 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 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/d6d3e95f-b82c-4815-a40b-0e3805594ffe%40googlegroups.com
> 
> .
>

-- 
-- 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%2BZLqy_TEmRTskSV1biCJPxaDdXyNQePfEe4BKn%2BxeKiXxqoqw%40mail.gmail.com.