• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1928
  • Last Modified:

TXT File to VFP Table

Hi all. I'm trying to figure out how to take a text file and create and populate a vfp table.
Here's the idea:

config.txt contains the definition to create a table and populate it.

main.prg checks for the existence of the table during startup, and if it does not exist, parses config.txt to create it.

The table is quite small, containing just four 128 character fields.

I can check for the existence of the table, and I'm sure I need to use filetostr to parse the .txt file, however I can't figure out the syntax (I guess) needed in the text file for this to work.
0
formadmirer
Asked:
formadmirer
1 Solution
 
jharkinsCommented:
Attach a copy of config.txt please.
0
 
pcelbaCommented:
Well this depends on format of the config.txt. If it contains SQL command like:
CREATE TABLE MyTable ( ;
Column1 char(128), ;
Column2 char(128), ;
Column3 char(128), ;
Column4 char(128))

Open in new window

then everything you need to do is to execute this code by DO config.txt command (it will be compiled into an .FXP file and execute as any other .PRG file). Maybe it will be necessary to compile the .TXT file as the first step when executing this command under VFP Run-time module.

If you just need to read the whole config file into a variable use FILETOSTR as you mentioned:
YourVariable = FILETOSTR("config.txt")

Open in new window


Once the file is ported to a variable you may process the variable and again depends on the file format... So let suppose the file contains the CREATE TABLE command again. In such case you may execute it by:
EXECSCRIPT(YourVariable)

Open in new window


Use path together with filename if your file is placed outside the VFP default folder.

Many other ways of table definition exist and above is showed just one of them.
0
 
CaptainCyrilCommented:
To check for file existence:

cFileName = "C:\import\myfile.text"
IF FILE(cFilename)
ENDIF

To import a file with fixed width (fields have to be the exact same size as the text)
IMPORT FROM &cFileName TYPE SDF

If there is a header in the file that you wish to discard
GO TOP
DELETE

If there is a delimiter and the file is not fixed width:
IMPORT FROM &cFileName TYPE DELIMITED WITH <char>
where char is the character that is delimiting the fields.

I could not look them up in Help so please check syntax correctly.
0
 
pcelbaCommented:
I would do it this way:
The text file should contain just column names, data types, and sizes:
Column1 char(128)
Column2 char(128)
Column3 char(128)
Column4 char(128)

Open in new window

And you may use following procedure to create the table:
PROCEDURE CreateTable
LPARAMETERS lcTableName, lcStructureFile

LOCAL lcStru, llSuccess, lcCmd, loEx AS Exception

IF FILE(m.lcStructureFile)
  
  *-- Adjust the table structure description
  lcStru = FILETOSTR(m.lcStructureFile)
  lcStru = STRTRAN(m.lcStru, CHR(13)+CHR(10), ",")
  lcStru = ALLTRIM(m.lcStru, ",", " ")
  
  *-- Add some necessary parts
  lcCmd = "CREATE TABLE " + m.lcTableName + " (" + m.lcStru + ")"
  
  TRY
    *-- Try to create the table
    EXECSCRIPT(m.lcCmd)
    llSuccess = .T.
  CATCH TO loEx
    WAIT WINDOW "Error: " + loEx.Message
  ENDTRY
  
ELSE
  WAIT WINDOW "File " + m.lcStructureFile + " does not exist"
ENDIF

RETURN m.llSuccess

Open in new window


Calling sample:
? CreateTable("xxx", "config.txt")  && returns .T. if table was created
0
 
formadmirerAuthor Commented:
This solution worked beautifully for my needs - thank you!!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now