TXT File to VFP Table

Posted on 2012-09-06
Last Modified: 2012-09-08
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.
Question by:formadmirer
    LVL 3

    Expert Comment

    Attach a copy of config.txt please.
    LVL 41

    Expert Comment

    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:

    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.
    LVL 27

    Expert Comment

    To check for file existence:

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

    To import a file with fixed width (fields have to be the exact same size as the text)

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

    If there is a delimiter and the file is not fixed width:
    where char is the character that is delimiting the fields.

    I could not look them up in Help so please check syntax correctly.
    LVL 41

    Accepted Solution

    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 to create the table
        llSuccess = .T.
      CATCH TO loEx
        WAIT WINDOW "Error: " + loEx.Message
      WAIT WINDOW "File " + m.lcStructureFile + " does not exist"
    RETURN m.llSuccess

    Open in new window

    Calling sample:
    ? CreateTable("xxx", "config.txt")  && returns .T. if table was created

    Author Closing Comment

    This solution worked beautifully for my needs - thank you!!

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: (http://msd…
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    This video discusses moving either the default database or any database to a new volume.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now