Tips > Misc > Data Import/Export
Data Import/Export
Importing data in Omnis Studio is easy and extremely fast. You can import data from a file into a list variable. You simply define the list columns to match the order of the data you wish to import, then tell Omnis Studio to import the data. Once the data is imported into the list variable you can loop through the list and process the data.
This section covers topics relating to importing and exporting data, and includes sample code and demos.Export Data to File
The following sample code makes a list of classes in the current library, prompts the user to enter a file name and location, and then exports the data to the file.
; Create a list of classes in the current library.
Do $clib.$classes.$makelist($ref.$name,$ref.$classtype,$ref.$moddate) Returns List
Do List.$cols.1.$name.$assign('name')
Do List.$cols.2.$name.$assign('classtype')
Do List.$cols.3.$name.$assign('moddate')
; Prompt the user for a file name and location.
Calculate Title as "Save Export Records As"
Calculate cPath as "ClassesList.txt"
; FileOps.$putfilename(path[,prompt,filter,initial-directory,appflags])
Do FileOps.$putfilename(cPath,Title) Returns bContinue
If bContinue
; Proceed with exporting the data to the file.
Begin reversible block
Set print or export file name {[cPath]}
Prepare for export to file {Delimited (tabs)}
End reversible block
; Export the list to the file.
Export data List
End export
Close print or export file
OK message (Icon) {Open the exported file using Excel or another spreadsheet program to check the contents.////[cPath]}
End If
Quit method kTrue
Import Data from File
This method prompts the user to select an import file. The records are then imported into a predefined list variable.
; Make sure the exoprt data method has been run.
Yes/No message (Icon) {You must first run the 'Export Data' demo, before running this Import data demo.////Have you run the 'Export Data' demo?}
If flag true
; Define a list for importing the data.
Do List.$cols.$add('name',kCharacter,kSimplechar,500)
Do List.$cols.$add('classtype',kCharacter,kSimplechar,500)
Do List.$cols.3.$name.$assign('moddate')
; Prompt the user to select the file.
; $getfilename(path[,prompt,filter,initial-directory,appflags])
Calculate Title as "Select the import file"
Do FileOps.$getfilename(cPath,Title,'*.txt',cPath) Returns bContinue
If bContinue
Begin reversible block
Set import file name {[cPath]}
Prepare for import from file {Delimited (tabs)}
End reversible block
Import data List
Close import file
OK message (Icon,Sound bell) {The import list has [List.$linecount] records.}
End If
End If
Quit method kTrue
Importing Data from Excel
When importing multiple columns of data, I generally use a spreadsheet program like to review and cleanup the data, and then save the spreadsheet as a tab delimited text file.
You can then import the tab delimited text file into a list variable defined to match the columns.
There are some things you need to watch out with when importing data from text files saved from an Excel spreadsheet:
- 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. This can be fixed with programming after import.
Calculate Company.PostalCode as jst(ImportedPostalCode,'-6p0') - There is feature in which counters its propensity to strip preceding zeros. It involves formatting the columns with this type of data to . When you open a text file in , the three part wizard asks several questions. On the third page of the wizard, choose for the relevant column.
- Always double check exported and imported dates. They can get messed up by misreading the date columns.
Including Column Names in Export File
You may want to include the column names at the top of your export data file. If all of the columns in your list variable were text columns you could simply add a line at the top of the list variable and calculate each column value to be the column name. However, there will be times when the list has date and number columns, so you need a better solution.
One technique it to create a special column names row variable which has all character type columns and each column value in the row is the column name of the list variable which is being exported.
You then first export the column names row variable, and then the actual export list variable.
The following sample code demonstrates how this would be done.
; Create a list of classes in the current library.
Do $clib.$classes.$makelist($ref.$name,$ref.$classtype,$ref.$moddate) Returns List
Do List.$cols.1.$name.$assign('name')
Do List.$cols.2.$name.$assign('classtype')
Do List.$cols.3.$name.$assign('moddate')
; Prompt the user for a file name and location.
Calculate Title as "Save Export Records As"
Calculate cPath as "ClassesList.txt"
; FileOps.$putfilename(path[,prompt,filter,initial-directory,appflags])
Do FileOps.$putfilename(cPath,Title) Returns bContinue
If bContinue
; Proceed with exporting the data to the file.
Begin reversible block
Set print or export file name {[cPath]}
Prepare for export to file {Delimited (tabs)}
End reversible block
; Prepare a special row variable for exporting the column names.
; Loop the the export list's columns.
Do List.$cols.$first() Returns rCol
While rCol
; Add a column to the row variable.
Do ColNamesRow.$cols.$add(rCol().$name,kCharacter,kSimplechar,1000)
; Set the value in the row to be the column name.
Calculate ColNamesRow.[rCol().$name] as rCol().$name
Do List.$cols.$next(rCol) Returns rCol
End While
; Export the column names row.
Export data ColNamesRow
; Add an empty line to the top of the export list.
Do List.$addbefore(1)
; Export the list to the file.
Export data List
End export
Close print or export file
OK message (Icon) {Open the exported file using Excel or another spreadsheet program to check the contents.////[cPath]}
End If
Quit method kTrue
Click the button in the window to try out the code.
One of the things you have to watch when including column names in the export file, is to not process them as data if you import the file. A technique I use for indentifying column names is to add XML style tags in the line before and after the column headings line.
<columnnames>
Column1Name Column2Name Column3Name ...
</columnnames>
Export Text to File
To export text to a file you can use the FileOps external object.
The following sample code export a string of text to a text file.
; Prompt the user for a file name and location.
Calculate Title as "Save Text as"
Calculate cPath as "TempText.txt"
; FileOps.$putfilename(path[,prompt,filter,initial-directory,appflags])
Do FileOps.$putfilename(cPath,Title) Returns bContinue
If bContinue
; oFileOpsExt.$createfile(cFile-path [,cFile-type,cCreator-type,bCreateres])
Do oFileOpsExt.$createfile(cPath) Returns FlagOK
If FlagOK
Calculate Text as 'Hello World!'
Do oFileOpsExt.$writefile(Text) Returns FlagOK
End If
Do oFileOpsExt.$closefile()
OK message (Icon,Sound bell) {The text '[Text]' has been exported to the file.}
End If
Quit method kTrue
Import Text from File
To import text from a file you can use the FileOps external object.
The following sample code imports the contents of a text file into a character variable.
; Prompt the user to select the file.
; $getfilename(path[,prompt,filter,initial-directory,appflags])
Calculate Title as "Select a text file to import"
Do FileOps.$getfilename(cPath,Title,'*.txt',cPath) Returns bContinue
If bContinue
; oFileOpsExt.$openfile(cFile-path [,bReadonly])
Do oFileOpsExt.$openfile(cPath,bReadonly) Returns FlagOK
If FlagOK
Do oFileOpsExt.$readfile(Text) Returns FlagOK
End If
Do oFileOpsExt.$closefile()
OK message (Icon,Sound bell) {The file has been imported into a character variable.////There variable contains [len(Text)] characters.}
End If
Quit method kTrue