The example Bob posted looks to be a segment from an X-12 transmission.
BSN*001*023456*20080708*20080813**175
N1*OB*0923
LIN*001*VP*705*SK*500123*UP*04355980512
REF*SAN JOSE*CA*99999*ABC
If so, the transmission length could vary depending on the application
and customer requirements. For the application I am working
on I can have 1 item or more than 10 looping in one transmission.
Back to lurking mode
-------- Original Message --------
Subject: [RBASE-L] - Re: Load versus Gateway issue
From: "Wills, Steve" <[EMAIL PROTECTED]>
Date: Wed, July 09, 2008 10:24 am
To: [email protected] (RBASE-L Mailing List)
Bob, even though it seems you got your fix working, did you say anywhere whether the record in your EDI file were of the same/equal length?
I know you said that they might contain different fields, segments, etc., but I don't recall any comments about the length being static or variable.
Curious,
Steve in Memphis
From: rbase-l@rbase.com [mailto:rbase-l@rbase.com] On Behalf Of ttc.inc@comcast.net
Sent: Wednesday, July 09, 2008 7:05am 07:05
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: Load versus Gateway issue
Thanks James and Emmitt,
Using the AS FORMATTED option in load did the trick.
-Bob
--
Thompson Technology Consultants
LaPorte, IN 46350
219-363-7441
-------------- Original message --------------
From: "Emmitt Dove" <emmitt.w.dove@comcast.net>
> Bob,
>
> More specifically, this requires a fixed format file. If the column you are
> interested in lies in text columns 29-86, you can:
>
> LOAD tablename FROM filename AS FORMATTED USING columnname 29 86
>
> Your table column will receive one row for each line of text, and the excess
> values will be ignored.
>
> Emmitt Dove
> Manager, DairyPak Business Systems
> Evergreen Packaging, Inc.
> dovee@blueridgepaper.com
> emmitt.w.dove@comcast.net
> (203) 643-8022
>
> -----Original Message-----
> From: rbase-l@rbase.com [mailto:rbase-l@rbase.com] On Behalf Of James
> Bentley
> Sent: Tuesday, July 08, 2008 9:28 PM
> To: RBASE-L Mail ing Li st
> Subject: [RBASE-L] - Re: Load versus Gateway issue
>
> Bob,
>
> Per additional posts to this thread try LOAD with the AS FORMATTED option
>
> Jim Bentley
> American Celiac Society
> jamesraybentley@yahoo.com
> tel: 1-504-737-3293
>
>
> --- On Tue, 7/8/08, Bob Thompson wrote:
>
> > From: Bob Thompson
> > Subject: [RBASE-L] - Re: Load versus Gateway issue
> > To: "RBASE-L Mailing List"
> > Date: Tuesday, July 8, 2008, 8:11 PM
> > Thanks,
> >
> > However Gateway command acts the same on a one column table
> > as it does a one column view. It does not load all the
> > data for some
> > reason. I did try temp and permanent tables.
> >
> > It appears that the LOAD command is working, except that it
> & gt; cannot handle the spaces in the text. It sees them as a
> > delimiter and
> > you cannot set the delimiter value in LOAD. GATEWAY does
> > not
> > seem to be working correctly, while it can handle the space
> > OK, it
> > is not loading all the rows for some reason.
> >
> > As a second test, I created a file with GateWay that had
> > 3000 rows
> > and imported the same file back in. It did load all 3000.
> > But this
> > was a simple one column file. It could be that all the *
> > in the file
> > is causing problems with Gateway, even though my character
> > settings
> > do not use it. I will have to investigate further.
> >
> > (This is last release of 7.5 by the way)
> >
> >
> > ----- Original Message -----
> > From: "Walker, Buddy"
> >
> > To: "RBASE-L Mailing List"
> >
> > Sent: Tuesday, July 08, 2008 7:53 PM
> > Subject: [RBASE-L] - Re: Load versus Gateway issue
> >
> >
> > Bob
> > Forgot to add the gateway in between the project and
> > append.
> >
> > Buddy
> >
> >
> > -----Original Message-----
> > From: rbase-l@rbase.com [mailto:rbase-l@rbase.com] On
> > Behalf Of Walker,
> > Buddy
> > Sent: Tuesday, July 08, 2008 8:48 PM
> > To: RBASE-L Mailing List
> > Subject: [RBASE-L] - Re: Load versus Gateway issue
> >
> > Bob
> > Why not load a temporary table that has only the one
> > column.
> >
> > PROJECT TEMPOARARY TmpTableName FROM RealTableName +
> > USI ColumnYouWant WHERE COUNT = 0
> >
> & gt; No w append the temporary table to the real table or insert
> > into the real
> > table from temporary table.
> >
> > Buddy
> >
> >
> >
> >
> >
> >
> > -----Original Message-----
> > From: rbase-l@rbase.com [mailto:rbase-l@rbase.com] On
> > Behalf Of Bob
> > Thompson
> > Sent: Tuesday, July 08, 2008 8:35 PM
> > To: RBASE-L Mailing List
> > Subject: [RBASE-L] - Re: Load versus Gateway issue
> >
> > James,
> > Thanks for the reply.
> >
> > It is not that Gateway does not allow a table, it does not
> > allow to
> > specify
> > what column in the table
> > you want to load. My table has 5 columns, of which I want
> > to load only
> > one
> > column.
> >
> > The file that I am lo ading is not really a CSV file, but
> > that is the
> > closest
> > option I see in Gateway.
> > The file is an EDI text file with typical lines such as:
> >
> > BSN*001*023456*20080708*20080813**175
> > N1*OB*0923
> > LIN*001*VP*705*SK*500123*UP*04355980512
> > REF*SAN JOSE*CA*99999*ABC
> >
> > Each line can and does have a different number of fields.
> > I load the entire line into a single column text field then
> > cursor
> > through
> > the table.
> > EDI has a specified format such that BSN line contains
> > certain data, N1
> > line
> > contains
> > different data, etc. etc. However, not all transactions
> > have the same
> > number of lines,
> > or even the same segments. I will leave it to say that
> > this is the way
> > it > > is done.
> >
> > So one must load the "line" of data into a single
> > column and parse out
> > what
> > is needed.
> > This is all rather easy. However, if I use the Load
> > command to load the
> >
> > file, I do get all
> > the records loaded. However, the LOAD command sees the
> > space in "SAN
> > JOSE"
> > as
> > a delimiter and drops the rest of the data. I.E. the line
> > REF*SAN
> > JOSE*CA*99999*ABC
> > gets loaded into the table only as REF*SAN Everything
> > after the space
> > gets
> > dropped
> > for some reason.
> >
> > If I use GATEWAY, the above line gets loaded perfectly,
> > however only
> > less
> > than
> > half of the file gets loaded. I get no errors and all
> > seems to w ork OK ,
> >
> > just the file
> > does not all load.
> >
> > I tried GateWay on a regular view versus temp, a temp table
> > and a
> > permanent
> > table.
> > They all acted the same, only 1649 rows added. Using the
> > LOAD command
> > on
> > either permanent or temp tables adds all records, but
> > anything with a
> > space
> > malfunctions.
> >
> > -Bob
> >
> >
> > ----- Original Message -----
> > From: "James Bentley"
> >
> > To: "RBASE-L Mailing List"
> >
> > Sent: Tuesday, July 08, 2008 7:15 PM
> > Subject: [RBASE-L] - Re: Load versus Gateway issue
> >
> >
> > > Bob,
> > > to the best of my knowledge a table name is allowed in > > gateway syntax.
> > >
> > > GATEWAY IMPORT CSV D:\TEMP\FNAME.TXT CREATE
> > AAA
> > > GATEWAY IMPORT CSV D:\TEMP\FNAME.TXT APPEND
> > AAA
> > > GATEWAY IMPORT CSV D:\TEMP\FNAME.TXT REPLACE
> > AAA
> > >
> > > If you check the syntax diagram it says
> > "tblview" which means table
> > name
> > > or view name.
> > >
> > > The only problem I see is your are loading a one
> > column table using a
> > CSV
> > > syntax. I find it strange that you would have a
> > "ONE COLUMN" CSV
> > input
> > > file. CSV usually implies multiple columns. Also
> > make sure your
> > values
> > > in the imput file are enclosed in " characters.
> > >
> > > You might want to post about a dozen lines from your
& gt; > input file to see
> > if
> > > we can spot what you problem is.
> > >
> > >
> > > Jim Bentley
> > > American Celiac Society
> > > jamesraybentley@yahoo.com
> > > tel: 1-504-737-3293
> > >
> > >
> > > --- On Tue, 7/8/08, ttc.inc@comcast.net
> > wrote:
> > >
> > >> From: ttc.inc@comcast.net
> >
> > >> Subject: [RBASE-L] - Load versus Gateway issue
> > >> To: "RBASE-L Mailing List"
> >
> > >> Date: Tuesday, July 8, 2008, 6:16 PM
> > >> I am having an issue with Gateway and perhaps
> > someone can
> > >> see the problem:
> > >>
> > >> R>LOAD edisplit FROM podata.001 using edidata
> > >& gt; R>sel count(edidata) from edisplit
> > >> count (edi
> > >> ----------
> > >> 3515 (Correct value)
> > >> R>del rows from edisplit
> > >> R>gateway import csv podata.001 append
> > tempsplit
> > >> R>sel count(edidata) from edisplit
> > >> count (edi
> > >> ----------
> > >> 1649 (Should be 3515)
> > >>
> > >> tempsplit is a temp view of edisplit having the
> > one column
> > >> edidata.
> > >> I do not see where the Gateway import option
> > allows you to
> > >> specify
> > >> a column to load so I had to use the temp view.
> > EdiSplit
> > >> has 5 columns
> > >> but I only want to load one.
> > >>
> > >> ?????
> > & gt;>
> > >> Thanks,
> > >> -Bob
> > >>
> > >>
> > >> --
> > >> Thompson Technology Consultants
> > >> LaPorte, IN 46350
> > >> 219-363-7441
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > --
> > > No virus found in this incoming message.
> > > Checked by AVG.
> > > Version: 7.5.526 / Virus Database: 270.4.6/1538 -
> > Release Date:
> > 7/7/2008
> > > 7:40 AM
> > >
> > >
> >
> >
> >
> >
> > --
> > No virus found in this incoming message.
> > Checked by AVG.
> > Version: 7.5.526 / Virus Database: 270.4.7/1541 - Release
> > Date: 7/8/2008
> > 7:50 PM
>
>
>
>
>
