topic Re: shp2sde file attribute loading issues in Data Management Questions

shp2sde file attribute loading issues

BartPittari — Tue, 09 Aug 2011 13:03:38 GMT

I'm trying to automate a routine that imports data from a Shapefile to SDE. The old data in the SDE layer needs to be wiped away each time, so I am using the 'init' option in the shp2sde command. Since Shapefiles notoriously truncate field names, I am trying to use the '-a' option that loads a file map of the names and other info.

shp2sde -o init -l pittarib.polyfeature,shape -f polyfeature -a file=poly_feat_attr.txt -r poly_feat_rej -v -i esri_sde_devtest -s testserver -u pittarib -p mypassword

Doing a lyrinfo command on the shapefile yields this information:

OBJECTID OBJECTID SE_INTEGER 0 -
SDE_FEATUR SDE_FEATURE_DATE SE_DATE 0 -
TEST_LINE_ TEST_LINE_PART_COUNT SE_INTEGER 5 -
TEST_POINT TEST_POINT_PART_COUNT SE_INTEGER 5 -
TEST_THREE TEST_THREE_POINT_COUNT SE_INTEGER 5 -
SDE_POLY_P SDE_POLY_PART_COUNT SE_INTEGER 5 -
SDE_CLOSEP SDE_CLOSEPOLY_ERR_FL SE_STRING 1 -
SDE_MULTIP SDE_MULTIPART_SHP_FL SE_STRING 1 -
SDE_INTERS SDE_INTERSECT_ERR_FL SE_STRING 1 -
AREA AREA SE_DOUBLE 27 8
LEN LEN SE_DOUBLE 27 8
OBJECT_ID_ OBJECT_ID_FK SE_INTEGER 0 -

OBJECTID N 10 - SE_INTEGER 0 -
SDE_FEATUR D 8 - SE_DATE 0 -
TEST_LINE_ N 5 - SE_INTEGER 5 -
TEST_POINT N 5 - SE_INTEGER 5 -
TEST_THREE N 5 - SE_INTEGER 5 -
SDE_POLY_P N 5 - SE_INTEGER 5 -
SDE_CLOSEP C 1 - SE_STRING 1 -
SDE_MULTIP C 1 - SE_STRING 1 -
SDE_INTERS C 1 - SE_STRING 1 -
AREA N 19 8 SE_DOUBLE 27 8
LEN N 19 8 SE_DOUBLE 27 8
OBJECT_ID_ N 10 - SE_INTEGER 0 -

SDE column definition string:
OBJECTID integer, SDE_FEATUR date, TEST_LINE_ integer(5), TEST_POINT
integer(5), TEST_THREE integer(5), SDE_POLY_P integer(5), SDE_CLOSEP
string(1), SDE_MULTIP string(1), SDE_INTERS string(1), AREA double(27,8),
LEN double(27,8), OBJECT_ID_ integer

The attribute info on the SDE Layer in ArcCatalog yields this information:

OBJECT_ID_FK / Data type: Integer / Width: 4 / Precision: 10 / Scale: 0
SDE_FEATURE_DATE / Data type: Date / Width: 36 / Precision: 0 / Scale: 0
TEST_LINE_PART_COUNT / Data type: SmallInteger / Width: 2 / Precision: 3 / Scale: 0
TEST_POINT_PART_COUNT / Data type: SmallInteger / Width: 2 / Precision: 3 / Scale: 0
TEST_THREE_POINT_COUNT / Data type: SmallInteger / Width: 2 / Precision: 3 / Scale: 0
SDE_CLOSEPOLY_ERR_FL / Data type: String / Width: 1 / Precision: 0 / Scale: 0
OBJECTID / Alias: OBJECTID / Data type: OID / Width: 4 / Precision: 0 / Scale: 0
SDE_POLY_PART_COUNT / Data type: SmallInteger / Width: 2 / Precision: 3 / Scale: 0
Shape / Data type: Geometry / Width: 0 / Precision: 0 / Scale: 0
SDE_MULTIPART_SHP_FL / Data type: String / Width: 1 / Precision: 0 / Scale: 0
PITTARIB.POLYFEATURE_SDE.AREA / Data type: Double / Width: 8 / Precision: 38 / Scale: 8
SDE_INTERSECT_ERR_FL / Data type: String / Width: 1 / Precision: 0 / Scale: 0
PITTARIB.POLYFEATURE_SDE.LEN / Data type: Double / Width: 8 / Precision: 38 /Scale: 8
SHAPE / Data type: Geometry / Width: 4 / Precision: 0 / Scale: 0
SHAPE.AREA / Data type: Double / Width: 0 / Precision: 0 / Scale: 0
SHAPE.LEN / Data type: Double / Width: 0 / Precision: 0 / Scale: 0

Based on the format from the online help, I'm guessing the attribute file should be in this form:
<shpCol> [sdeCol] [type] [size] [nDecs] [NOT_NULL]

So, the SHP > SDE fields/info in the text file are as follows:

OBJECTID OBJECTID SE_INTEGER 4 0
OBJECT_ID_ OBJECT_ID_FK SE_INTEGER 8 0
SDE_FEATUR SDE_FEATURE_DATE SE_DATE 36 0
TEST_LINE_ TEST_LINE_PART_COUNT SE_INTEGER 2 0
TEST_POINT TEST_POINT_PART_COUNT SE_INTEGER 2 0
TEST_THREE TEST_THREE_POINT_COUNT SE_INTEGER 2 0
SDE_POLY_P SDE_POLY_PART_COUNT SE_INTEGER 2 -
SDE_CLOSEP SDE_CLOSEPOLY_ERR_FL SE_STRING 1 -
SDE_MULTIP SDE_MULTIPART_SHP_FL SE_STRING 1 -
SDE_INTERS SDE_INTERSECT_ERR_FL SE_STRING 1 -
AREA AREA SE_DOUBLE 27 15 -
LEN LEN SE_DOUBLE 27 15 -

Upon loading, the program returns a fatal error and I have to exit out. Do I have this in the wrong format? Should I use 'N' instead of SE_INTEGER, for example, as the load type? Do I need to use the SDE Layer field information (width, decimals, etc) instead of what I got from the Shapefile?

Re: shp2sde file attribute loading issues

VinceAngelo — Tue, 09 Aug 2011 13:07:54 GMT

Do not attempt to load into the AREA and LEN shape properties. If you use 'sdetable -o describe'
you'll see the column layout that 'shp2sde' sees; this should help.

The types are all equivalent, so you only need 'shpname sdename' in the mapping file. Don't
mention the shape column in the mapping file (-l table,COLUMN handles that).

- V

Re: shp2sde file attribute loading issues

BartPittari — Tue, 09 Aug 2011 16:11:11 GMT

Do not attempt to load into the AREA and LEN shape properties. If you use 'sdetable -o describe'
you'll see the column layout that 'shp2sde' sees; this should help.

The types are all equivalent, so you only need 'shpname sdename' in the mapping file. Don't
mention the shape column in the mapping file (-l table,COLUMN handles that).

- V

Vince,

That file change seemed to work in one of the environments I'm using (SDE 9.3.1)
but when I run it in the other environment (SDE 9.2), I get this:

SDE Code (-53): Invalid column data type
Line 1: Error in parsing ColDef string.
" "OBJECTID
Error, reading column specification file

Not sure if it matters, but the 9.2 version is working off of Solaris and the 9.3.1 is working from Windows.

Re: shp2sde file attribute loading issues

VinceAngelo — Tue, 09 Aug 2011 16:20:11 GMT

Do you have an escape character in the Unix mapping file? If it was edited on Windows
it may be missing a terminal newline (or have embedded CR before the LF).

- V

Re: shp2sde file attribute loading issues

BartPittari — Tue, 09 Aug 2011 16:50:08 GMT

Do you have an escape character in the Unix mapping file? If it was edited on Windows
it may be missing a terminal newline (or have embedded CR before the LF).

- V

That did it!! (thank you vi, lol) - UNIX didn't like the CR (^M as it looks in UNIX).

Thank you. How do I mark this as solving the problem? I'm still trying to get accustomed to the interface.

Re: shp2sde file attribute loading issues

VinceAngelo — Tue, 09 Aug 2011 16:56:29 GMT

Yup, 'vi' is your friend.

They never got the marking capability running on the arcgis.com Forums.

- V

Re: shp2sde file attribute loading issues

BartPittari — Tue, 09 Aug 2011 17:10:01 GMT

Yup, 'vi' is your friend.

They never got the marking capability running on the arcgis.com Forums.

- V

Which explains why I didn't see it. It was pretty visible on the old versions. Do they just mark it as complete?