Re: Data being truncated when converting from a flat file

[Margaret-Anne Bett <Margaret-Anne_Bett@LEARNINGTREE.COM>]

(See attached file: log.txt)

I've attached part of the log file, showing 3 vendors.

In the first one the data in 'address3' is truncated to 'New Ci' during the
load.
The second vendor loads fine.
'Address1' is truncated to '309-1228 Ol' on the third vendor.

As you can see, it happens on different fields on different vendors!

Margaret-Anne




ANDERSD2 <ANDERSD2@OAKWOOD.ORG> on 29/06/2001 17:36:30

Please respond to sqr-users@list.iex.net

To:   SQR-USERS@list.iex.net
cc:    (bcc: Margaret-Anne Bett/LearningTree/GB)
Subject:  Re: Data being truncated when converting from a flat file



I see you have a SHOW command.  Could you attach a snippet of your LOG so we
can see what the variable actually holds.  Also, if you would, add
additional SHOW statements so we can see the values of the variables after
you UNSTRING the initial variable.

Thanks,
d

-----Original Message-----
From: Margaret-Anne Bett [mailto:Margaret-Anne_Bett@LEARNINGTREE.COM]
Sent: Friday, June 29, 2001 11:52 AM
To: SQR-USERS@list.iex.net
Subject: Re: Data being truncated when converting from a flat file




I've amended the code to only populate 2 tables - Vendors and Vendor
Addresses.

(See attached file: AP_Vend_Conv.sqr)

Margaret-Anne




ANDERSD2 <ANDERSD2@OAKWOOD.ORG> on 29/06/2001 16:38:20

Please respond to sqr-users@list.iex.net

To:   SQR-USERS@list.iex.net
cc:    (bcc: Margaret-Anne Bett/LearningTree/GB)
Subject:  Re: Data being truncated when converting from a flat file



yes, attach, copy&paste, retype, whatever  :)

-----Original Message-----
From: Margaret-Anne Bett [mailto:Margaret-Anne_Bett@LEARNINGTREE.COM]
Sent: Friday, June 29, 2001 11:22 AM
To: SQR-USERS@list.iex.net
Subject: Re: Data being truncated when converting from a flat file


I'm using '|' to delimit the file.  I'm new to this user group so I'm not
sure
where to post sample data and code.  Should I just attach it to my email?

Margaret-Anne




Jamie Harris <JHarris@FCC.CC.MD.US> on 29/06/2001 15:54:51

Please respond to sqr-users@list.iex.net

To:   SQR-USERS@list.iex.net
cc:    (bcc: Margaret-Anne Bett/LearningTree/GB)
Subject:  Re: Data being truncated when converting from a flat file



What delimiter are you using to unstring?  Are you able to post any code or
sample data?  That may help us find the problem.

-----------------------------------------------------
Jamie Harris
Junior Programmer/Analyst/Software Integrator
Office of Information Technology
Frederick Community College
-----------------------------------------------------

>>> Margaret-Anne_Bett@LEARNINGTREE.COM 6/28/2001 12:04:08 PM >>>
I'm using the unstring command to split the data into various data fields.
I
then use rtrim to remove any spaces from the end of the data.  There are no
special characters.

Thanks for all the suggestions so far, but I'm still having problems!

Margaret-Anne




Rhonda Hudgins <RHudgins@GENENCOR.COM> on 26/06/2001 12:49:13

Please respond to sqr-users@list.iex.net

To:   SQR-USERS@list.iex.net
cc:    (bcc: Margaret-Anne Bett/LearningTree/GB)
Subject:  Re: Data being truncated when converting from a flat file



How are you defining your input (flat) file?   If you are using "substr"
to move the information to a field check the format.
 for example:  LET $Address                = substr ($RECIN, 48, 35)   This
starts in column 48 of the input record and includes 35 characters.


Thanks!

Rhonda Hudgins
@}---'----,----
Genencor International, Inc
716/256-5266
rhudgins@genencor.com



                    Margaret-Anne Bett
                    <Margaret-Anne_Bett@LEARNIN        To:
SQR-USERS@list.iex.net
                    GTREE.COM>                         cc:
                    Sent by: "Discussion of            Subject:     Re: Data
being truncated when converting from a flat file
                    SQR,Brio Technology's
                    database reporting
                    language"
                    <SQR-USERS@list.iex.net>


                    06/25/2001 08:20 PM
                    Please respond to sqr-users






The data will definitely fit into the fields - the text file field has 30
characters and the database is 55.  A lot of the time the data is truncated
to
just one character.

Margaret-Anne




cshelton <cshelton@INDIANA.EDU> on 25/06/2001 22:30:40

Please respond to sqr-users@list.iex.net

To:   SQR-USERS@list.iex.net
cc:    (bcc: Margaret-Anne Bett/LearningTree/GB)
Subject:  Re: Data being truncated when converting from a flat file



Have you verified that the fields that are being truncated are defined
large enough in the database to be able to accept the largest values from
your flat files?

I haven't worked much with SQL server, but in Sybase (the grandparent of
SQL server) if you attempt to insert a value too large to fit in a column,
the database will silently truncate your value to the size of the column
in the database.

chris

On Mon, 25 Jun 2001, Margaret-Anne Bett wrote:

> PeopleSoft 8.13
>   Microsoft Sequel Server 2000
>   Windows 2000
>
> I am trying to convert data from a flat file into the PS_VENDOR_ADDR
table.
> Several of the data fields are being truncated.  This happens on
different
> fields on different vendors, but is consistent each time I run the
conversion
> SQR.  There doesn't seem to be any logic as to which field is truncated
on
which
> vendor.  I have show statements in the SQR which return the correct
value.
>
> Margaret-Anne
>
>

Text - character set unknown