Hi guys.
A customer wants to migrate from one of my apps to something new. I’d like to make him and export of all clarion tables to csv for example. Is there a template or tool? I don’t feel like coding set,next,add for every table 
Thanks
I use Capesoft’s File Explorer to do all my import/Export to and from Excel.
Hi Bostjan,
Easy to do with the TopScan.exe utility (it’s in the Clarion \bin map).
It has a File -> Export, where you can set how you want it.
The fields shown, which you can set by Column -> Show/Hide, will be exported.
1 Like
I have no experience with it, but @mikem sells Impex. https://sterlingdata.com/impex/
Umm, seems like I’d still have to select each file /table and export it. I have 300 folders with each folder having the “same” tables/files inside (names, structure for a different “client”). So even if I have “export all” in one click, it would take 300 times to export all tables. This I can take, but clicking each table (30 of them) times 300 is…well, too much…
I think Hanson has a product too. I have never used that one either.
NOV 18,2022 Clarion Live Jim Morgan showed his templates for converting TPS to SQL. They are free with a donation suggested if they save you time.
IIRC it creates a conversion program that reads all the TPS data and writes an SQL script to insert it all. That writes a program to read the TPS files and write the SQL script, so that would be all the TPS data in a text file… It also writes a script to create the DB. This looked easy to do and very automated. It could be rerun multiple times.
So one option to be to convert all the data to a free SQL like SQL Lite, MS SQL Express, Firebird, etc. That would maintain type information, and deal with dates plus have relationships. It would be something they could open in a DB viewer to see it all.
A second option would be to hack that template to write CSV files.
John Hickey/Jim Morgan - Converting TPS to SQL! NOV 18,2022
Not sure if they still sell it but the TPS OBDC driver would let them read your files directly.
1 Like
Tpsodbc would be great but in this case I still deal with dat and k* files
The dct can create a file conversion program. I think you right click on the table.
There are several templates that do conversions, but you will still have a bit of work to script everything.
If csv format is just an option, then you can consider an export to json.
Sure, json is fine, how would that be easier to do than csv?
You might not have to export everything, if you own the copyright of your app, then you only have to export the customers data, not all the record id’s and other fields used to make the app work.
So you could just export their data in one giant csv file where the data is not normalised. Technically you have complied with their request, and you havent given away any knowledge of how your app stores the data internally.
Usually json implementations for Clarion have built-in FILE import/export support.
Also, you can store all your tables in one json file (if total data size is not toooo big).
Yes! Good idea. I will do it with Capesoft jFiles. One line of code to export one table to file.
Hi Bostjan,
1
Another way to convert .DAT files is by using the tool included in Clarion 2.1 called The Clarion Converter CCVT.EXE
Of which you can see a screenshot here.
It asks for the input and output file format and their names. The Source File (with a File Definition) is only requested if the output is in Clarion format.
Formats other than Clarion .DAT are DBase II & III, DIF (Data Interchange Format) and BASIC (CSV).
It can be used interactively or by command line. Interactively you can access the help with F1 that shows the following:
2
By command line the discovered syntax is the following:
CCVT.EXE InputFormat InputFile OutputFormat OutputFile SourceFile InputOwner
For example you can call it as:
CCVT Clarion MyFile.DAT Basic MyFile.CSV
Or if your file has an OWNER attribute
CCVT Clarion MyFile.DAT Basic MyFile.CSV Dummy MyOwner
3
This utility is 16 bit so it will not run natively on 64 bit Windows. You can use it through emulators like VDos or DOSBOX and derivations, although it will run slower.
4
Being called from the command line means you can automate it for all files in a directory with FOR. It even has an option to recursively traverse directories.
For example, in the case of 4DOS in VDosPlus:
FOR %datfile IN (*.DAT) DO CCVT Clarion %datfile BASIC %@LEFT[-4,%datfile].CSV
%datfile gets filled with the name of the current File (eg. MyFile.DAT) and %@LEFT[-4,%datfile] is a special syntax to remove the last four characters (.DAT extension) to be able to pass with the new extension .CSV
The syntax for CMD.exe would be different.
You would see the Converter window working automatically showing the record counter, and flickering on each different file, as it returns to DOS and run CCVT again with the next file.
2 Likes
The DumpLoad template will generate a program to dump all your ISAM files (TopSpeed, Clarion, etc) to CSV files.
@RobertoArtigas GitHub also has templates to convert to SQL. I like going to a format that supports Types so things like Numbers, Dates and Times are explicitly identified with that Type. A template approach can see a LONG has an @D or @T picture in the DCT and format the output as y-m-d versus a Clarion date serial number.
The SQL tools also create a schema that helps understand the data model so hopefully less explaining on your part of 200 files with their relationships and types. You could use one of these SQL tools to generate the schema as your documentation. It is a text file that is human readable or could be run in a database then viewed in the DB tools.
DBase seems like an option and can be opened in Excel. It seems too old to mess with.
The DUMPLOAD template will take all your ISAM tables and EXPORT them as CSV comma delimited files. This template will also IMPORT them and restore your table with previous data. This will give you and additional way of saving data for backup and restore purposes in a TEXT like format.
The EXTENSION template generates all the procedure code automagically for each table into sequencialy numbered CLW modules. It will then add one more module that puts everything together.
2 Likes
To use that without emulators in CMD.exe on a 32 bits Windows:
Create a batch file ForDat.bat with this content:
SET datfile=%1
CCVT Clarion %datfile% BASIC %datfile:~0,-4%.CSV
Then call it in the FOR:
FOR %d IN (*.DAT) DO fordat.bat %d