topic Re: Importing/Exporting long text fields in enterprise SDE geodatabase in Data Management Questions

Importing/Exporting long text fields in enterprise SDE geodatabase

BenjaminVang-Johnson — Thu, 14 Jun 2012 14:52:20 GMT

Hello. I have a feature class stored in a SDE geodatabase. The database is sql server 2008. I am using ArcGIS 10. The feature class contains a text field with 8000 character limit. I have to do some editing of this text field and find it very difficult to do so in ArcGIS where the text is presented in a single line. My text is too long to edit in this format.

So, I can think of two ways that I could edit efficiently, but I don't know how to accomplish either.

1) Find a way to edit within ArcMap that presents the text in a wrapped, paragraph format. Is this possible? Anyone know how to do this? Something like a HTML popup format would be perfect, if I could edit in it.

2) Export the attribute in another format (such as Excel), make the edits, then join back to the original feature class and update the text field. This was the approach I initially took. However, I ran into a problem. When I export to Excel, the longest fields show up as "########". I can fix this by changing the field formatting to General in Excel. Then I can view the text and make my edits. The major problem comes when I try to view the Excel in ArcMap. The entire field shows up as a BLOB, and then I can't update the text in the feature class. In fact, none of the records show up; it displays as an empty table. I then tried importing the revised Excel as a GDB table first, but when I do that the records do show but the long text field is a BLOB. Anyone know a way around this?

Thank you!

Re: Importing/Exporting long text fields in enterprise SDE geodatabase

JoeBorgione — Thu, 14 Jun 2012 15:11:14 GMT

I just googled max character length sql and one listing says it'll store 1 billion characters; another says that text variables can store up to 2gb of text data. Yikes! To me this is a case of just because you can, it doesn't mean you should.

It's hard for me to concieve a text field more than 50 characters wide; perhaps you could write some kind of script that adds a newline after X number of characters if you were to export the records to a text file.

Re: Importing/Exporting long text fields in enterprise SDE geodatabase

VinceAngelo — Thu, 14 Jun 2012 16:00:47 GMT

Do you want wide VARCHAR or NVARCHAR columns or CLOB/NCLOB columns?

The only wide strings I've worked with were ~400 character URLs. Once you
get into strings that require newlines for readability, you're really crossing
into the CLOB realm.

If you write your own editing app, you can pretty much use whatever format
or display widget you want. If you want to use existing tools, you may need
to come up with new validation and/or filter utilities to make sure the edited
text conforms to requirements (there's nothing quite like having escape
sequences embedded in your strings to prevent pattern matching).

I'm sure the 'sdequery' utility could export your data (key + long string) to
a text file (pipe- or tab-delimited), and 'sdeupdate' could then merge the
edited file back (provided it's not versioned). I even added some classes to
export CLOBs into files (one file per row), and to convert the file collections
back into CLOBs (or strings, through CLOBs). I don't have any filter classes
that will convert hard returns and other escape values into spaces on import,
but I do have tools to detect their presence.

- V