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" <[EMAIL PROTECTED]> > 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 > > > > >
