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 <[EMAIL PROTECTED]> wrote: > From: Bob Thompson <[EMAIL PROTECTED]> > Subject: [RBASE-L] - Re: Load versus Gateway issue > To: "RBASE-L Mailing List" <[email protected]> > 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 > 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" > <[EMAIL PROTECTED]> > To: "RBASE-L Mailing List" > <[email protected]> > 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 > > Now 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 loading 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 work 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" > <[EMAIL PROTECTED]> > To: "RBASE-L Mailing List" > <[email protected]> > 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 > 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] > <[EMAIL PROTECTED]> wrote: > > > >> From: [EMAIL PROTECTED] > <[EMAIL PROTECTED]> > >> Subject: [RBASE-L] - Load versus Gateway issue > >> To: "RBASE-L Mailing List" > <[email protected]> > >> 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 > >> 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. > >> > >> ????? > >> > >> 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
