Data Import Export_Tips

Addition of this tip was recommended by Chris Banford after a list server discussion on importing text from a file.

Importing data in Studio is real easy and extremely fast. You can import any data into a list variable. You simply define the list columns to match the order of the data you wish to import, then tell Studio to import the data.

If you are importing a text file, you can set up a list variable with a single column, and import the file to list. Each line in the text file becomes a line in the list variable. I'm pretty sure the blank lines are skipped.

You can then loop through the list and process the lines extracting the data you need.

When importing multiple columns of data, I generally use a spreadsheet program like Excel to review and cleanup the data. Then 'Save as...' text format file type, which is Tab delimited.

You can then import the text file as tab delimited into your list defined to match the Excel columns and then process the list to add the data to your data file.

Note1: Earlier versions of Excel (4.0) only handles 255 characters per column and doesn't keep carriage returns. I haven't tested this on later versions of Excel.

Note2: (by Craig Lewis)Excel suppresses leading zeroes in fields that it regards as numbers. Thus Americal postal codes (aka ZIP codes) for the Eastern Coastal states are changed. Example: 00123 becomes 123. Of course, this can be fixed with programming after import--and should be. I usually use: Calculate Company.PostalCode as jst(ImportedPostalCode,'-6p0')

Note2b: (by Richard Ure) There is a valuable feature in Excel which counters its propensity to strip preceding zeros. It involves formatting the column(s) with this type of data to "Text". When you open a text file in Excel, the three part Wizard asks several questions. On the third page of the Wizard, choose "text" for the relevant column.

Alternatively, if you are in the habit of copying from an Omnis screen report and pasting into an open Excel worksheet (kTabs, conveniently
provided by Omnis), make sure the relevant column is formatted to text before doing the paste.

Note3: Always double check exported and imported dates. They can get messed up by Excel misreading the date columns.

OBJECT CLASS 'oDataImportExport'

In my own application, I created an object class called 'oDataImportExport' which handles all my importing and exporting of data. It has public methods called $exportData and $importData. You send the object class method the right parameters and it does the work for you.

See the methods $exportData and $importData for further details.

$exportList

$importFile

Parsing Data

If you are importing strings of data which need to be parsed, take a look at the strtok() function.

I used strtok() in the 'oFunctions' object class. The methods $retListFromCSVString and $retListFromCSVMultilineString both use the strtok() to parse Comma Separated Values and add them to a list. You can take a look at those methods in this TIPS library.

The sample code below shows the $CSVStringReturnList method.

There are a number of string functions which might help you with parsing data.

StudioTips Documentation - Copyright 2006 Vencor Software