Ever needed to create a text file from a data source and you have no tools available to interrogate it?
We are doing a cloud Planning Analytics project for a client where they are using an ERP called Finance Plus over a database called Visual Foxpro. Mmmm, I hear you read – why is this relevant for me? Well, Visual Foxpro is a very close relative of dBase – a popular database in the 1980s (even more “why is this relevant” sighs – stay with me!). We tried all sorts of methods (including using MS Access over Foxpro, using the ETL tools in SQL server, the ODEDB and ODBC drivers for Foxpro etc) to get data out of Foxpro, all of which failed. Eventually we decided to just do a text file extract and FTP that to the Planning Analytics server, rather than using the gateway tools provided by IBM for connection to a local database.
This then created a wee problem. How to create text files from Foxpro? A bit of digging and I found a tool called “Flyspeed Data Export“.
Flyspeed Data Export
Allows you to:
- Create multi table exports from a variety of source databases (including ODBC, OLEDB, Oracle, SQL Server, DB2, MS Access, Foxpro, OpenXML, CSV, DBF, BLOB etc).
- Export to a variety of targets, including CSV, TXT, XLS, XML, MS Access, dBase, Rich Text, MS Word, HTML or BLOB).
- You can then create complete table exports of select specific fields (important for me because the source data contained multi line text/memo fields).
- Create “projects” so you can re-open them.
- Schedule the job to run when required.
- Name the output files as required.
- Format the output to use pipes “|”, rather than commas.
- Format date and time as required.
- Set performance options
- Overwrite target or append a date-time stamp
Basically, Flyspeed is a great tool for easily creating text files or databases from almost any data source. No, it doesn’t have ETL or filtering, but it is great at what it does!
Note that to save a project or schedule a job you will need to purchase the professional version (cost in June 2017 was $US95) that also comes with 12 months support.Just in case you’re interested, I purchased the professional version and no, there is no affiliate fee payable for my recommendation!