Export Delimited File

Background: 

This script gives additional options for anyone wishing to export an IDEA file in a delimited format.

2015-08-15 - Updated the script so that the if you user select to include field names that they are separated by the selected field separator and not the ",".  Also added code to format numbers, it seems that if you had a number like 12.10 it would be stored in the text file as 12.1, this has been fixed.

Documentation: 

IDEA has several limitation when exporting a delimited file.  IDEA will automatically export all character fields encapsulated by "", it doesn't give a choice whether you want to export in an ASCII or Unicode format, also you can't choose which deliminator you wish to use.  This script gets around all these problems.  When opening the script it will default to the currently open file, if none is open it gives you the option to choose one.

The above menu gives you several options and some defaults when exporting a delimited file:

  • Save File - Allows  you to select where you want to save your text file.
  • Inclue Field Names - this allows you to decide if you wish to have the field names as the first row of the export file.
  • Select Fields - this opens an additional dialog in which you can select which fields to export, the default is all fields.
  • Select Format - you can export as an ASCII or a unicode text format, ASCII is the default.
  • Field Separator - this is the character that separates the fields, the default is comma (,).
  • Text Encapsulator - this is the character that surrounds text fields, the default is the double quote (") but you can even use nothing if you don't want to have a text encapsulator.
  • Decimal Separator - this is the character that is used for decimals in numeric fields, the default is the period (.).

You then give an output filename and the file will be created.  The file is created in the results folder of your project folder.

Comments

Hi Brian, I have several ideascript macros I run regularly to combine/manipulate reports into a file for upload into another system. Everything works fine, but the export encapsulates the fields with " and means I need to open each export in notepad and replace the " with blanks to remove them. Not a major issue, but it would be nice to tidy the process up and streamline things.

Is there any way the above can be achieved without the prompt, so the export is produced automatically as part of a macro?

Below is an extract of the existing export part of the ideascript (with some of the non relevant info redacted) for info.

---

File - Export Database: DEL
Function ExportDatabaseDEL
Set db = Client.OpenDatabase("Database.IMD")
Set task = db.ExportDatabase
task.AddFieldToInc "1"
task.AddFieldToInc "2"
task.AddFieldToInc "3"
task.AddFieldToInc "etc"
task.IncludeFieldNames = TRUE
eqn = ""
task.Separators ",", "."
task.PerformTask "\\Path\Exports.ILB\Filename.CSV", "Database", "DEL", 1, db.Count, eqn
Set db = Nothing
Set task = Nothing
End Function

Brian Element's picture

Hi JHDCC, just to let you know that I haven't forgotten about this request.  I just haven't found time to do it as it will take a bit more time to do as I want to create an API for you so you just send the info and the script does the work for you.

Thanks Brian, that's great. Hope it's not too much work for you!

When I select the file, it is not actually selected. How to overcome this problem?