HOWTO: Use SQL Pass Through to Connect to ASCII Text Files

Step-by-Step Example

1. Create a comma-delimited text file for use as the data source for the ODBC driver by typing each of the following commands in the Command window:

      SET DEFAULT TO home()+"samples\data"
      USE customer
      COPY TO customer.csv DELIMITED  && Creates comma-delimited Customer file
      USE
   					

2. Set up a Text File data source. In Windows 3.1x, do this through the ODBC Administrator in the Visual FoxPro program group or by using the ODBC icon in the Windows Control Panel. In Windows 95, do this through the 32 ODBC Administrator located in the ODBC program group or the Control Panel. In Windows NT, do this through the 32 ODBC Administrator in the Visual FoxPro program group or the ODBC icon in Control Panel.
3. Use the following steps to set up a Data Source for the Visual FoxPro table saved as a comma-delimited file in Step 1:
   1. Open the ODBC or 32 Bit ODBC Administrator icon.
   2. From the Add list, select Microsoft Text Driver, and click OK. The ODBC Text Setup dialog box will appear.
   3. Type FoxPro Text Test in the Data Source Name, and then click the Select Directory button.
   4. Select the Vfp\Samples\Data directory, and click OK.
   5. Click the Options button, and then click the Define Format button.
   6. In the Define Text Format dialog box, select the Customer.csv file.
   7. Click the Guess button to create a Data Type, Name, and Width for each column of the selected file. Select each column, starting with F1, from the list. For each item in the list with a Data Type of Char, change the Width from 255 to the correct column width (if known). If the correct column width is not known, set each to 254 (the value used for the purposes of this example). Either way, as each column width is set, click Modify. The column width for each Char column must be set to a value less than 255 to enable the column to appear in Visual FoxPro as Character rather than Memo data.
   8. When you finish, click OK in the Define Text Format dialog box, and then click OK in the OCBC Text Setup dialog box. Then click Close in the Data Sources dialog.
4. Place the following code in a program (.prg) file that you will run to allow connection to the "FoxPro Text Test" data source.

      * Start of Program to connect to "FoxPro Text Test" Data Source
   
      LOCAL lcSource, lnConHand, lnSQLRes
   
      * Set initial variable values
      STORE 0 TO lnConHand, lnSQLRes
      lcSource = "FoxPro Text test"  && Text Data Source Name from ODBC Dialog
   
      * Connect to Text Data Source
      WAIT Window "Attempting connection ..." NOWAIT
      lnConHand = SQLCONNECT(lcSource)
      IF lnConHand > 0
         WAIT Window ;
          "Connected to Text File Driver - Handle:" ;
          + STR(lnConHand,2,0) TIMEOUT 2
      ELSE
         WAIT Window ;
           "Unable to connect to Text File ..." TIMEOUT 2
      ENDIF
   
      * Bring data into cursor called CustText
      IF lnConHand>0
         lnSQLRes=SQLEXEC(lnConHand, "SELECT * FROM Customer.csv","CustText")
         BROWSE NOWAIT
         lnSQLRes=SQLDISCONNECT(lnConHand) && Remove to leave connection open
      ENDIF
   
      * End of program
   					

Notes

   [customer.csv]
   ColNameHeader=False
   Format=CSVDelimited
   MaxScanRows=25
   CharacterSet=OEM
   Col1=F1 Char Width 254
   Col2=F2 Char Width 254
   Col3=F3 Char Width 254
   Col4=F4 Char Width 254
   Col5=F5 Char Width 254
   Col6=F6 Char Width 254
   Col7=F7 Char Width 254
   Col8=F8 Char Width 254
   Col9=F9 Char Width 254
   Col10=F10 Char Width 254
   Col11=F11 Char Width 254
   Col12=F12 Float
				

   [customer.csv]
   ColNameHeader=False
   Format=CSVDelimited
   MaxScanRows=25
   CharacterSet=OEM
   Col1=CUST_ID Char Width 6
   Col2=COMPANY Char Width 40
   Col3=CONTACT Char Width 30
   Col4=TITLE Char Width 30
   Col5=ADDRESS Char Width 60
   Col6=CITY Char Width 15
   Col7=REGION Char Width 15
   Col8=POSTALCODE Char Width 10
   Col9=COUNTRY Char Width 15
   Col10=PHONE Char Width 24
   Col11=FAX Char Width 24
   Col12=MAXORDAMT Float