How to read TPS File in ODBC

ODBC PowerPack CSV Driver
,
1

Intrroduction

TPS (TopSpeed) files are proprietary database files often used in legacy systems. While reading these files directly can be challenging, tools like the ZappySys CSV Driver combined with a TPS-to-CSV conversion utility simplify the process. This guide explains converting TPS files into CSV format and reading them using the ZappySys CSV Driver.

Prerequisites

  1. ODBC PowerPack: Download and install the ZappySys ODBC PowerPack from the Customer Download Area or the trial version.
  2. Java Runtime: Ensure Java is installed on your system. Download it from Oracle or OpenJDK.
  3. TPS-to-CSV Utility: Download the binary JAR file for the TPS-to-CSV converter from here.

Steps

Step 1: Prepare the TPS-to-CSV Conversion Utility*

  1. Download the tps-to-csv.jar file from the TPS-to-CSV utility page:
    TPS-to-CSV Utility.

  2. Place the downloaded JAR file in a folder (e.g., C:\temp).

  3. Ensure Java is installed and accessible via the command line by running:

    java -version

Step 2: Convert TPS File to CSV Using the Command Line

To convert a TPS file (table.tps) to a CSV file (table.csv):

  1. Open a command prompt or PowerShell window.
  2. Run the following command:
 java -jar c:\temp\tps-to-csv.jar -s c:\temp\table.tps -t c:\temp\table.csv -sep
  • -s: Specifies the source TPS file path.
  • -t: Specifies the target CSV file path.
  • -sep: Sets the column delimiter (e.g., 03 for a custom delimiter).

Step 3: Use ZappySys ODBC CSV Driver to Read the Converted Data

  1. Open the ODBC Data Source by typing “ODBC” in the search box and launching the ODBC Data Source.
    Open ODBC Data Source

  2. To gain access for yourself or other users, go to the User DSN or System DSN. Go to the System tab for SQL Server Integration and add a new System DSN. Click the “Add” button.
    ZappySys ODBC Driver - Open UI

  3. From the driver list, select ZappySys CSV Driver and press Finish.
    Select CSV Driver

  4. Go to the Preview tab and use the following query to read the data directly from the TPS file:

    SELECT * FROM $ 
WITH(
   Data='cmd:>powershell -executionpolicy bypass -Command "java -jar c:\temp\tps-to-csv.jar -s c:\temp\table.tps -t c:\temp\table.csv -sep 03;more c:\temp\table.csv"',
   ColumnDelimiter='\x0003',
   IgnoreBlankLines=1,
   SkipEmptyRecords=1
)
  • Data: Specifies the command to invoke the TPS-to-CSV conversion and read the resulting CSV.
  • ColumnDelimiter: Sets the delimiter used in the CSV file (\x0003 in this example).
  • IgnoreBlankLines: Skips blank lines in the data.
  • SkipEmptyRecords: Prevents empty records from being read.
  1. Now press the preview data button and you will see the result:
    Final result
    Final result1540×449 32.3 KB

Video Tutorial

Conclusion

Converting and reading TPS files becomes straightforward by combining the TPS-to-CSV utility and ZappySys CSV Driver. This method leverages the power of Java for conversion and ZappySys tools for seamless integration with modern ETL workflows. If you encounter any issues or need further assistance, please get in touch with our support team via chat on our website

References