SQL Server: How to Migrate Data From Top Speed Database Files

I had never heard about Top Speed Database. But, my today’s task was, “to migrate data from TPS files to SQL Server2008”. I start collecting information but unfortunately there was no detailed information regarding these TPS files. But thanks to my team lead who always have some solution for such interesting tasks.

TPS Database File is an ISAM type file developed by the TopSpeed corporation primarily for use with the Clarion Development platform which is currently developed and distributed by Soft Velocity www.softvelocity.com.

To shift data from tps files, first we have to open these files, reformat some columns (Date and Time) and then we can shift data to some flat files.

Top Speed Database Scanner is used to open these files

Click on FILE - - >OPEN, to open a single tps file. Once the file is open you can select column of your choice from VIEW. Columns with data types date and time are stored separately in tps files. And before shifting we have to convert these date and time columns to some proper format other wise these date and time will look like simple integer.

For this purpose click on  COLUMN and then FORMAT DATABASE COLUMN, in picture field use @D17 or @D18 to format date column data, and then click OK button. Next select time column (if exists) and again select FORMAT DATABASE COLUMN. This time in picture field use @T3. Press OK button and you are done with formatting.

To export to a text file, move your pointer to FILE, and click EXPORT. Quote Strings is checked by default, unchecked it and also use a CHARACTER as separator. In my case I used pipe | sign. Now, for “Export File” provide name and path to valid text file and click OK button to export.

On SQL Server side, use import utility to import, use Flat File Source as data source and make necessary changes according to following screen shot, at choose a database source page.