Import tables from Excel or Access and data truncates

[ajaeger]

I have a table that I need to import into my SQL database. I have it in Excel as well as Access. A couple of the columns are long strings of text (5000-7000) characters. I see the data correctly in Excel or Access. The SQL table that I'm trying to import into is defined as:

CREATE TABLE [dbo].[X_SS_Notes] (
[ID] [float] NULL ,
[Note1] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Note2] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Note3] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Importing the table (via DTS) from either Excel or Access seems to go fine (no error messages). However, when I look at the fields in SQL, the data truncates around 255 characters.

Am I overlooking something simple?

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[JohnDTampaBay]

Are you using Query Analyzer to SELECT the data from the tables? QA defaults to display only the first 255 characters of a column. You can change this by selecting Tools > Options from the menu, then select the Results tab. Good luck!

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[ajaeger]

I am using QA to view the results and do only see 255 characters. Thanks for the info on it only displaying that many characters - makes sense. However, in my database front end, I am concatenating these 3 notes fields together into one note field (MBR_NOTES) that displays. In that display, I only see the truncated data. For example, assume the three notes are (shortened for readability):

NOTE1='This is what is stored in note 1'
NOTE2='And in note 2, this is what is stored'
NOTE3='Finally, in note 3, I have this'

My SQL is:
MBR_NOTES = NOTE1 + '**' + NOTE2 + '**' + NOTE3

I would expect to see:
This is what is stored in note 1***And in note 2, this is what is stored***Finally, in note 3, I have this

Instead, I'm seeing:
This is what***And in note***Finally, in

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[JohnDTampaBay]

SQL may be padding each column with spaces, so the combined value (with spaces) exceeds the size of the field. What happens when you trim the fields?

MBR_NOTES = RTrim(NOTE1) + '**' + RTrim(NOTE2) + '**' + RTrim(NOTE3)


--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)