Import after .DAT file conversion 1

[keepingbusy]

Hi All

Having read some of the threads regarding "Reading DAT Files" I have been able to convert one into a .txt file. The fields are all Character type. Please excuse the lengthy post but it may be easier to explain. The file is now in the format of 20 lines, CUSTOMER being the first, here's some data:

CUSTOMER....... A Plant               
INV ADD........ Hall Lane             
LOC ADD........ Union Road Indust. Est
CERT NO........ 4101           
INSP DATE...... February    
NEXT INSP...... August
TEL NO......... 01234 234567
INSPECTOR...... D Jones
QUANTITY....... 24
VISITS......... 2
CONTRACT NO.... 398
COST...........
LOC TEL NO.....
SPECIAL INSTRUC PARENT COMPANY SIGNED
CUSTOMER REF... A103
LAST INSPECTION 02.02.02
TYPE........... 3 FG
(spare)........ 13 DP
(spare)........ 4 W G
(spare)........ 1 FB
CUSTOMER....... A jones
INV ADD........ City Road
LOC ADD........ Union Road Indust. Est
CERT NO........ 4102
INSP DATE...... March    
NEXT INSP...... November
TEL NO......... 01234 678967
INSPECTOR...... J Williams
QUANTITY....... 12
VISITS......... 4
CONTRACT NO.... 405
COST...........
LOC TEL NO.....
SPECIAL INSTRUC PARENT COMPANY SIGNED
CUSTOMER REF... A205
LAST INSPECTION 12.11.03
TYPE........... 3 FG
(spare)........ 13 DP
(spare)........ 4 W G
(spare)........ 1 FB

I'm not bothered about the field names from the DAT file showing, I can remove them once I get the info into a table, so here's my question:

How can I get the above into a table I've named aftl.dbf with field names field01, field02 etc (all of which are character fields)

I've tried the import wizard in VisFox and also used the import and append from commands without success, import from does not recognise the .txt file and append from states "Not a table". The import wizard puts the data into two fields only.

Look forward to any replies
Lee

VisFox Version 6 User / Windows ME

 

[Mike Gagnon]

The import wizard puts the data into two fields only.

I believe you need to pivot the table afetr this step. Take a look at pivot in the help file.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[baltman]

Might want to do some tweaking...

Brian

CLOSE ALL
lcNewRecordIndicatorString="CUSTOMER"
lcDBFName="CustFile"
lnHandle=FOPEN([custfile.txt])
lnSpareCnt=1

CREATE TABLE (lcDBFName) (customer c(30))

DO WHILE not Feof(lnHandle)
  lcString=FGETS(lnhandle)
    lcHeader=LEFT(CHRTRAN(CHRTRAN(LEFT(lcString,15),[.()],[]),CHR(32),[_]),10)
    lcValue=RIGHT(lcString,LEN(lcString)-16)

      IF lcHeader==lcNewRecordIndicatorString
        APPEND BLANK
      ENDIF
          
      IF lcHeader="spare"
       lcHeader=lcHeader+TRANSFORM(lnSpareCnt)
       lnSpareCnt=IIF(lnSpareCnt<3,lnSpareCnt+1,1)
      ENDIF 
      
      IF VARTYPE(&lcHeader)="U"
        ALTER TABLE (lcDBFName) ADD COLUMN &lcHeader c(20)
      ENDIF
  
      REPLACE &lcHeader WITH lcValue
ENDDO
FCLOSE(lnhandle)

LOCATE
BROWSE NOWAIT
[code]

 

[keepingbusy]

Hi Mike

I believe you need to pivot the table after this step. Take a look at pivot in the help file.

I tried this and the following error appeared.

[i]Micorsoft Query has not been installed properly and is needed in order to create and Excel pivot table[/i]

I'll take a look at this as I've never heard of "Pivot" but may come handy in the future (Thanks for your reply)

Hi Baltman
One word - "Perfect". The code ran like a dream and sorted out 5736 records that came from .DAT file. Sometimes seeing the actual code helps a lot, can you tell me where you found it, or is one of those held in the "Help" files that you have to dig deep for?

I am very grateful to you and thanks again both for posting

Kindest regards
Lee

VisFox Version 6 User / Windows ME

 

[baltman]

After a while you can just write things from scratch in a few minutes.

Every data file has a different pattern, just need to make sense of it and apply appropriate logic. I have to do similar things at work all the time.

Glad it made your life easier.

Brian

 

[keepingbusy]

Thanks Brian

As I mentioned, sometimes its more beneficial to see how its done.

It certainly DID make my life easier (Thanks again)

Kindest regards
Lee....


VisFox Version 6 User / Windows ME