Yes, Load is working fine with the as formated option. The answer to your question is yes, just about every record is a different length.
-Bob -- Thompson Technology Consultants LaPorte, IN 46350 219-363-7441 -------------- Original message -------------- From: "Wills, Steve" <[EMAIL PROTECTED]> > 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: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of > [EMAIL PROTECTED] > 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" > > > 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. > > [EMAIL PROTECTED] > > [EMAIL PROTECTED] > > (203) 643-8022 > > > > -----Original Message----- > > From: [email protected] [mailto:[EMAIL PROTECTED] 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 > > [EMAIL PROTECTED] > > 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: [email protected] [mailto:[EMAIL PROTECTED] 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: [email protected] [mailto:[EMAIL PROTECTED] 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 > > > > [EMAIL PROTECTED] > > > > tel: 1-504-737-3293 > > > > > > > > > > > > --- On Tue, 7/8/08, [EMAIL PROTECTED] > > > wrote: > > > > > > > >> From: [EMAIL PROTECTED] > > > > > > >> 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 > > > > > > > > > > > >
