Importing from a report format 2

[EBOUGHEY]

I import this file all of the time through conversion software but now it just died on me. I need to import this data into a dbf file. The beginning of the row and where the data starts for each record is always consistent.
Attached is a sample. I'm currently working in programmer's editor and designing just a label format while I wait for an easier way!...

Sample of data (each field on one row):

CUST NO ˜--------....................................................... 3793
NAME ˜-----------------------------------. SHIRLEY FLOYD
ADDRESS ˜-------------------------..................... 2402 LANE ST
...CITY..... ˜---------------......................................... DURHAM
ST˜--................................................................... NC
ZIP ˜-----............................................................. 27707
RES. PHONE# ˜--------------........................................... 919-688-4617
SERIAL NUMBER ˜-----------------..................................... 1G3AJ51W8KG332294
YEAR MK VEHICLE DESCRIPTION
---- -- --------------------
89 OL 4 DR S SEDAN
LST DATE˜--------....................................................... 10/30/96

 

[EBOUGHEY]

I meant for each field is consistent... After the LST DATE~ info, another record starts.

 

[DSummZZZ]

Probably one of the fastest solutions would be to read through the file and parse it. You could always use FILETOSTR() to read in the entire file, then parse the lines, but this is probably just as good/easy/fast/readable.....:

USE MyTable IN 0
nHandle = FOPEN('SomeFile.txt')
IF nHandle < 0
   *... error
ELSE
   DO WHILE !FEOF(nHandle)
      cTempStr = FGETS(nHandle)
      DO CASE 
         CASE LEFT(cTempStr, 7) = 'CUST NO'
            STORE RIGHT(cTempStr, 9) TO m.CustNo 

         CASE LEFT(cTempStr, 4) = 'NAME'
            STORE RIGHT(cTempStr, 30) TO m.CustName
         
         CASE ........
         .
         .
         .
         .
       ENDCASE 
       INSERT INTO MyTable FROM MEMVAR
   ENDDO  {!FEOF() }
ENDIF

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[DSummZZZ]

Let me add another line:

   .
   .
   ENDDO  {!FEOF() }
ENDIF
[COLOR=red]
CLOSE ALL
[/color]

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[EBOUGHEY]

I have quite a few records. 39000 to import. I escaped the program to see what it was doing and each name is populating 19 times. Can you see what's wrong with my code? Or is it just because I didn't let it finish? (oh and still working on the lengths to import...

CLOSE ALL
CLEAR
SET SAFETY OFF

USE rr-in IN 0
nHandle = FOPEN('univer01.cap')
IF nHandle < 0
*... error
ELSE
DO WHILE !FEOF(nHandle)
cTempStr = FGETS(nHandle)
DO CASE
CASE LEFT(cTempStr, 7) = 'CUST NO'
STORE RIGHT(cTempStr, 9) TO m.CustNo

CASE LEFT(cTempStr, 4) = 'NAME'
STORE RIGHT(cTempStr, 30) TO m.CustName

CASE LEFT(cTempStr, 7) = 'ADDRESS'
STORE RIGHT(cTempStr, 30) TO m.CustAdd

CASE LEFT(cTempStr, 4) = ' CITY'
STORE RIGHT(cTempStr, 30) TO m.CustCity

CASE LEFT(cTempStr, 2) = 'ST'
STORE RIGHT(cTempStr, 30) TO m.CustState

CASE LEFT(cTempStr, 3) = 'ZIP'
STORE RIGHT(cTempStr, 30) TO m.CustZip

CASE LEFT(cTempStr, 11) = 'RES PHONE #'
STORE RIGHT(cTempStr, 30) TO m.CustPhone

CASE LEFT(cTempStr, 13) = 'SERIAL NUMBER'
STORE RIGHT(cTempStr, 30) TO m.CustVin

CASE LEFT(cTempStr, 27) = 'YEAR MK VEHICLE DESCRIPTION'
STORE RIGHT(cTempStr, 30) TO m.CustYearmake

CASE LEFT(cTempStr, 8) = 'LST DATE'
STORE RIGHT(cTempStr, 30) TO m.CustActive

ENDCASE
INSERT INTO rr-in FROM MEMVAR
ENDDO {!FEOF() }
ENDIF
CLOSE ALL
CLEAR
SET SAFETY ON

 

[EBOUGHEY]

It's okay. I just wrote a little script to remove all the dupes while cleaning up the data...

Thank you so much for your help. It is invaluable and I can use this program now instead of the conversion program I had...

I am a happy camper!


Thanks again!!!

 

[EBOUGHEY]

Oops! Problem....

Here is a sample of the output. Remember I was saying that it duplicated? Well it's putting wrong info in some of the fields too. You can tell what it's doing but I don't know how to fix the code...

"3793","","","","","","","","","","","","",0,"",""
"3793","SHIRLEY FLOYD","","","","","","","","","","","",0,"",""
"3793","SHIRLEY FLOYD","2402 LANE ST","","","","","","","","","","",0,"",""
"3793","SHIRLEY FLOYD","2402 LANE ST","DURHAM","","","","","","","","","",0,"",""
"3793","SHIRLEY FLOYD","2402 LANE ST","DURHAM","NC","","","","","","","","",0,"",""
"3793","SHIRLEY FLOYD","2402 LANE ST","DURHAM","NC","27707","","","","","","","",0,"",""
"3793","SHIRLEY FLOYD","2402 LANE ST","DURHAM","NC","27707","9196884617","","","","","","",0,"",""
"3793","SHIRLEY FLOYD","2402 LANE ST","DURHAM","NC","27707","9196884617","1G3AJ51W8KG332294","","","","","",0,"",""
"3793","SHIRLEY FLOYD","2402 LANE ST","DURHAM","NC","27707","9196884617","1G3AJ51W8KG332294","L 4 DR S SEDAN","","","L","",0,"",""
"3793","SHIRLEY FLOYD","2402 LANE ST","DURHAM","NC","27707","9196884617","1G3AJ51W8KG332294","L 4 DR S SEDAN","10/30/96","","L","",0,"",""
"3793","SHIRLEY FLOYD","2402 LANE ST","DURHAM","NC","27707","9196884617","1G3AJ51W8KG332294","L 4 DR S SEDAN","10/30/96","","L","",0,"",""
"11379","SHIRLEY FLOYD","2402 LANE ST","DURHAM","NC","27707","9196884617","1G3AJ51W8KG332294","L 4 DR S SEDAN","10/30/96","","L","",0,"",""
"11379","GEORGE STEVENS","2402 LANE ST","DURHAM","NC","27707","9196884617","1G3AJ51W8KG332294","L 4 DR S SEDAN","10/30/96","","L","",0,"",""
"11379","GEORGE STEVENS","5307 PEPPERCORN ST","DURHAM","NC","27707","9196884617","1G3AJ51W8KG332294","L 4 DR S SEDAN","10/30/96","","L","",0,"",

 

[craigsboyd]

This is a hack to be sure and something better could be done, but given what you already have I think your problem would be fixed with something like this:

CLOSE ALL
CLEAR
SET SAFETY OFF

USE rr-in IN 0
nHandle = FOPEN('univer01.cap')
IF nHandle < 0
   *... error
ELSE
   DO WHILE !FEOF(nHandle)
      cTempStr = FGETS(nHandle)
      DO CASE
         CASE LEFT(cTempStr, 7) = 'CUST NO'
            STORE RIGHT(cTempStr, 9) TO m.CustNo

         CASE LEFT(cTempStr, 4) = 'NAME'
            STORE RIGHT(cTempStr, 30) TO m.CustName

         CASE LEFT(cTempStr, 7) = 'ADDRESS'
            STORE RIGHT(cTempStr, 30) TO m.CustAdd
            
         CASE LEFT(cTempStr, 4) = '   CITY'
            STORE RIGHT(cTempStr, 30) TO m.CustCity

        CASE LEFT(cTempStr, 2) = 'ST'
            STORE RIGHT(cTempStr, 30) TO m.CustState
            
        CASE LEFT(cTempStr, 3) = 'ZIP'
            STORE RIGHT(cTempStr, 30) TO m.CustZip
            
        CASE LEFT(cTempStr, 11) = 'RES PHONE #'
            STORE RIGHT(cTempStr, 30) TO m.CustPhone

        CASE LEFT(cTempStr, 13) = 'SERIAL NUMBER'
            STORE RIGHT(cTempStr, 30) TO m.CustVin

        CASE LEFT(cTempStr, 27) = 'YEAR MK VEHICLE DESCRIPTION'
            STORE RIGHT(cTempStr, 30) TO m.CustYearmake

        CASE LEFT(cTempStr, 8) = 'LST DATE'
            STORE RIGHT(cTempStr, 30) TO m.CustActive
	       	INSERT INTO rr-in FROM MEMVAR
	       	m.CustNo = ""
	       	m.CustName = ""
	       	m.CustAdd = ""
	       	m.CustCity = ""
	       	m.CustState = ""
	       	m.CustZip = ""
	       	m.CustPhone = ""
	       	m.CustVin = ""
	       	m.CustYearmake = ""
	       	m.CustActive = ""
       ENDCASE
   ENDDO  {!FEOF() }
ENDIF
CLOSE ALL
CLEAR
SET SAFETY ON

http://www.sweetpotatosoftware.com

 

[baltman]

The issue is the insert before the line is built.

CASE LEFT(cTempStr, 8) = 'LST DATE'
  STORE RIGHT(cTempStr, 30) TO m.CustActive
           
ENDCASE

if !empty(m.CustActive)
 INSERT INTO rr-in FROM MEMVAR
 m.CustActive=[]
endif
[code]

 

[EBOUGHEY]

thanks. i'll have to try it when I get home. I did it the hard way since I still couldn't get it to come out completely right.

I'll let you know as soon as I test it.

Elena

 

[EBOUGHEY]

Didn't work... I'm still having problems. Here is my current code. I also tried Craig's way and it didn't work. I'll send a sample of the output in a minute.

CLOSE ALL
CLEAR
SET SAFETY OFF

USE rr-in IN 0
zap
nHandle = FOPEN('univer.cap')
IF nHandle < 0
*... error
ELSE

DO WHILE !EOF(nHandle)
cTempStr = FGETS(nHandle)
WAIT WINDOW 'Getting Records!' NOWAIT
DO CASE
CASE LEFT(cTempStr, 7) = 'CUST NO'
STORE RIGHT(cTempStr, 7) TO m.CustNo
CASE LEFT(cTempStr, 4) = 'NAME'
STORE RIGHT(cTempStr, 30) TO m.Name
CASE LEFT(cTempStr, 7) = 'ADDRESS'
STORE RIGHT(cTempStr, 35) TO m.Address
CASE LEFT(cTempStr, 4) = 'CITY'
STORE RIGHT(cTempStr, 20) TO m.City
CASE LEFT(cTempStr, 2) = 'ST'
STORE RIGHT(cTempStr, 3) TO m.State
CASE LEFT(cTempStr, 3) = 'ZIP'
STORE RIGHT(cTempStr, 5) TO m.Zip
CASE LEFT(cTempStr, 5) = 'PHONE'
STORE RIGHT(cTempStr, 14) TO m.Phone
CASE LEFT(cTempStr, 13) = 'SERIAL NUMBER'
STORE RIGHT(cTempStr, 18) TO m.Vin
CASE LEFT(cTempStr, 6) = 'RETAIL'
STORE RIGHT(cTempStr, 3) TO m.Retail
CASE LEFT(cTempStr, 27) = 'YEAR'
STORE RIGHT(cTempStr, 14) TO m.Year
CASE LEFT(cTempStr, 8) = 'LST DATE'
STORE RIGHT(cTempStr, 8) TO m.LastActive

ENDCASE
if !empty(m.LastActive)
INSERT INTO rr-in FROM MEMVAR
m.LastActive=[]
endif

ENDDO {!FEOF() }
ENDIF

*WAIT WINDOW 'Getting Unique Records!' NOWAIT

*INDEX ON CUSTNO UNIQUE TO T
*COPY TO T
*ZAP
*APPEND FROM T


WAIT WINDOW 'Done!' NOWAIT

FCLOSE(nHandle)
CLEAR
SET SAFETY ON

 

[baltman]

I changed your code a bit. Not that your !EOF doesn't work in this case. It needed to be !FEOF.

SET DEFAULT TO JUSTPATH(SYS(16))
CLOSE ALL
CLEAR
SET SAFETY OFF

CREATE TABLE [rr-in] (CustNo c(20), name c(20), address c(20), city c(20),;
	state c(10), zip c(10), phone c(15), vin c(20), retail c(20), year c(20),;
	lastactive c(20))

m.LastActive=[]
m.CustNo=[]
m.Name=[]
m.Address=[]
m.City=[]
m.State=[]
m.Zip=[]
m.Phone=[]
m.Vin=[]
m.Retail=[]
m.Year=[]
m.LastActive=[]

nHandle = FOPEN('univer.cap')

IF nHandle < 0
   *... error
ELSE
   DO WHILE !FEOF(nHandle) 
      cTempStr = FGETS(nHandle)
     ?cTempStr 
      WAIT WINDOW 'Getting Records!' NOWAIT
      DO CASE 
         CASE LEFT(cTempStr, 7) = 'CUST NO'
            STORE ALLTRIM(CHRTRAN(RIGHT(cTempStr, 7),[.-˜],[])) TO m.CustNo 
         CASE LEFT(cTempStr, 4) = 'NAME'
            STORE ALLTRIM(CHRTRAN(RIGHT(cTempStr, 30),[.-˜],[])) TO m.Name
         CASE LEFT(cTempStr, 7) = 'ADDRESS'
            STORE ALLTRIM(CHRTRAN(RIGHT(cTempStr, 35),[.-˜],[])) TO m.Address       
         CASE LEFT(cTempStr, 4) = 'CITY'
            STORE ALLTRIM(CHRTRAN(RIGHT(cTempStr, 20),[.-˜],[])) TO m.City
        CASE LEFT(cTempStr, 2) = 'ST'
            STORE ALLTRIM(CHRTRAN(RIGHT(cTempStr, 3),[.-˜],[])) TO m.State           
        CASE LEFT(cTempStr, 3) = 'ZIP'
            STORE ALLTRIM(CHRTRAN(RIGHT(cTempStr, 5),[.-˜],[])) TO m.Zip
        CASE 'PHONE' $ LEFT(cTempStr, 25) 
            STORE ALLTRIM(CHRTRAN(RIGHT(cTempStr, 14),[.-˜],[])) TO m.Phone
        CASE LEFT(cTempStr, 13) = 'SERIAL NUMBER'
            STORE ALLTRIM(CHRTRAN(RIGHT(cTempStr, 18),[.-˜],[])) TO m.Vin           
        CASE LEFT(cTempStr, 6) = 'RETAIL'
            STORE ALLTRIM(CHRTRAN(RIGHT(cTempStr, 3),[.-˜],[])) TO m.Retail
         CASE LEFT(cTempStr, 27) = 'YEAR'
            STORE ALLTRIM(CHRTRAN(RIGHT(cTempStr, 14),[.-˜],[])) TO m.Year
        CASE LEFT(cTempStr, 8) = 'LST DATE'
            STORE ALLTRIM(CHRTRAN(RIGHT(cTempStr, 12),[.-˜],[])) TO m.LastActive
			INSERT INTO rr-in VALUES (m.CustNo, m.Name, m.Address , m.City, m.State, m.Zip, m.Phone,;
         	   m.Vin, m.Retail, m.Year,m.LastActive)
			   m.LastActive=[]
        ENDCASE 
      ENDDO
ENDIF 

WAIT WINDOW 'Done!' NOWAIT

FCLOSE(nHandle)
CLEAR
SET SAFETY ON

 

[EBOUGHEY]

Final Code that worked perfectly! Thanks a bunch!!!!!

Elena

SET DEFAULT TO JUSTPATH(SYS(16))
CLOSE ALL
CLEAR
SET SAFETY OFF

CREATE TABLE [rr-in] (CustNo c(20), name c(30), address c(40), city c(25),;
state c(3), zip c(10), phone c(15), vin c(20), retail c(20), year c(20),;
lastactive c(20))

m.LastActive=[]
m.CustNo=[]
m.Name=[]
m.Address=[]
m.City=[]
m.State=[]
m.Zip=[]
m.Phone=[]
m.Vin=[]
m.Retail=[]
m.Year=[]
m.LastActive=[]

nHandle = FOPEN('univer01.cap')

IF nHandle < 0
*... error
ELSE
DO WHILE !FEOF(nHandle)
cTempStr = FGETS(nHandle)
?cTempStr
WAIT WINDOW 'Getting Records!' NOWAIT
DO CASE
CASE LEFT(cTempStr, 7) = 'CUST NO'
STORE ALLTRIM(CHRTRAN(RIGHT(cTempStr, 7),[.-˜],[])) TO m.CustNo
CASE LEFT(cTempStr, 4) = 'NAME'
STORE ALLTRIM(CHRTRAN(RIGHT(cTempStr, 30),[.-˜],[])) TO m.Name
CASE LEFT(cTempStr, 7) = 'ADDRESS'
STORE ALLTRIM(CHRTRAN(RIGHT(cTempStr, 35),[.-˜],[])) TO m.Address
CASE LEFT(cTempStr, 4) = 'CITY'
STORE ALLTRIM(CHRTRAN(RIGHT(cTempStr, 20),[.-˜],[])) TO m.City
CASE LEFT(cTempStr, 2) = 'ST'
STORE ALLTRIM(CHRTRAN(RIGHT(cTempStr, 3),[.-˜],[])) TO m.State
CASE LEFT(cTempStr, 3) = 'ZIP'
STORE ALLTRIM(CHRTRAN(RIGHT(cTempStr, 5),[.-˜],[])) TO m.Zip
CASE 'PHONE' $ LEFT(cTempStr, 25)
STORE ALLTRIM(CHRTRAN(RIGHT(cTempStr, 14),[.-˜],[])) TO m.Phone
CASE LEFT(cTempStr, 13) = 'SERIAL NUMBER'
STORE ALLTRIM(CHRTRAN(RIGHT(cTempStr, 18),[.-˜],[])) TO m.Vin
CASE LEFT(cTempStr, 6) = 'RETAIL'
STORE ALLTRIM(CHRTRAN(RIGHT(cTempStr, 3),[.-˜],[])) TO m.Retail
CASE LEFT(cTempStr, 27) = 'YEAR'
STORE ALLTRIM(CHRTRAN(RIGHT(cTempStr, 14),[.-˜],[])) TO m.Year
CASE LEFT(cTempStr, 8) = 'LST DATE'
STORE ALLTRIM(CHRTRAN(RIGHT(cTempStr, 12),[.-˜],[])) TO m.LastActive
INSERT INTO rr-in VALUES (m.CustNo, m.Name, m.Address , m.City, m.State, m.Zip, m.Phone,;
m.Vin, m.Retail, m.Year,m.LastActive)
m.LastActive=[]
ENDCASE
ENDDO
ENDIF

WAIT WINDOW 'Done!' NOWAIT

FCLOSE(nHandle)
CLEAR
SET SAFETY ON