OpenTable()

Summary

Opens a table from a file on disk and creates a view.

 

Changes

Added ExcelX class and Delimiter option to Version 2016. Added PostgreSQL class to Version 2018.

 

Syntax

view_name = OpenTable(string desired_view_name, string table_type, array table_spec [, array options])

 

Argument	Contents
desired_view_name	The name you want to use for the view
table_type	The type of table to be opened: "DBASE", "FFA", "FFB", "CSV", "EXCEL", "EXCELX", "ODBC", "ODBCQ", "ORCL", "ACCESS", "MSSQLD", or"PGSQLD"
table_spec	An array of strings indicating the file to be opened and other information that varies, based on the type of table, as follows:

 

For dBASE Files

Element	Type	Contents
1	String	The name of the dBASE or FoxPro data file
2	String	The name of the dBASE or FoxPro index file; or null if the index file has the default name or if there is no index file

 

For FFA, FFB, and CSV Files

Element	Type	Contents
1	String	The name of the data file
2	String	The name of the dictionary file; or null if the dictionary file has the default name or (CSV files only) if there is no dictionary file

 

For Excel Files

Element	Type	Contents
1	String	The name of the workbook (.XLS/.XLSX) file
2	String	The name of the worksheet (should have $ appended) or named range

 

For ExcelX Files

Element	Type	Contents
1	String	The name of the workbook (.xlsX) file
2	String	The name of the worksheet (should have $ appended) or named range

  

For ODBC Sources

Element	Type	Contents
1	String	The name of the ODBC data source.
2	String	The name of the ODBC table.
3	String	The name of a field in the ODBC table containing unique values for each record (optional)
4	String	The name of the schema that the ODBC table belongs to (optional, for some ODBC data sources, since the schema is always null)

 

For ODBCQ Sources (ODBC SQL Query)

Element	Type	Contents
1	String	The name of the ODBC data source
2	String	The full SQL select statement

 

For ORCL (Oracle) Tables

Element	Type	Contents
1	String	The name of the Oracle service
2	String	The name of the Oracle table
3	String	The name of a field in the Oracle table containing unique values for each record (optional)
4	String	The name of the owner of the Oracle table

 

For ACCESS (Microsoft Access) Tables

Element	Type	Contents
1	String	The name of the Microsoft Access database
2	String	The name of the Microsoft Access table
3	String	The name of a field in the Microsoft Access table containing unique values for each record (optional)

 

For MSSQLD (Microsoft SQL Server) Tables

Element	Type	Contents
1	String	The name of the Microsoft SQL Server database in the form server_name|database_name where server_name is the name of the SQL Server instance and database_name is the name of the database
2	String	The name of the Microsoft SQL Server table
3	String	The name of a field in the table containing unique values for each record (optional)
4	String	The name of the schema of the table

 

For PGSQLD (PostgreSQL) Tables

Element	Type	Contents
1	String	The name of the PostgreSQL database in the form server_name|database_name where server_name is the name of the PostgreSQL instance and database_name is the name of the database, e.g. "localhost|myDb"
2	Options array	An array with the following items: Option Type Contents Table String The name of the PostgreSQL table KeyField String The name of a field in the table containing unique values for each record (optional) Schema String The name of the schema of the table, e.g. "public" uid String The user name of a user who has access to the database, e.g., "postgres" pw String The password for the user who has access to the database, e.g., "postgres"

 

Options

Option	Type	Contents
Delimiter	String	Single character to use to separate fields in CSV files, such as "|" for pipe-delimited text files; default is a comma
Read Only	Boolean	"True" to open read only, "False" (default) otherwise
Shared	Boolean	"True" to open shared, "False" (default) otherwise; "False" sets the Exclusive mode

 

Returns

A string containing the actual name of the view.

 

Notes

• The options array is an optional argument.

• The Delimiter option only works with the CSV table_type. Only a string with one character is valid. If you use a space delimiter and you have string field values with spaces, you will need to enclose those string values with single or double quotes.

• The file types include FFA for fixed-format text (ASCII), FFB for fixed-format binary, and CSV for comma-separated text (values).

• The index file name for a dBASE file is an optional string that contains the name of an .MDX (dBASE) or .CDX (FoxPro) index file.

• If no index file name is specified, OpenTable() attempts to open an .MDX file with the same base file name, and failing that, tries to open a .CDX file of the same base file name.

• If an index file name is specified, OpenTable() only attempts to open index files of that name (although if the file is specified without an extension, it tries first .MDX and then .CDX).

• If an index file does not exist (whether one is specified or not) OpenTable() still succeeds but opens the dbase file without any index file.

• The dictionary filenames for FFA, FFB, and CSV files are optional. If none is specified, OpenTable() attempts to open a dictionary file with the same base file name and an extension of .DCT (for FFA files), .DCB (for FFB files), or .DCC (for CSV files).

• When opening FFA, FFB and CSV files, please, make sure that if a dictionary file existst with th same name, the

• For Excel, named ranges are specified as is but worksheets should have a dollar sign ($) appended to their names.

• For Excel, you must have an ODBC Excel datasource set up, or an Excel ODBC driver installed.

• ExcelX provides native support for .xlsx files.

• For ODBC, Oracle, and Microsoft Access tables and ADO recordsets, the unique_field (if specified) should be a field in the named table that has a unique value for each record. If unique_field is null, OpenTable() opens the table in read-only mode and the values cannot be updated. For best performance, the unique_field should be indexed in the ODBC source. If the unique_field is not indexed, updating values in an ODBC source may be very slow.

• For ODBC tables the data source can be a User DSN, a System DSN or a File DSN.

• In opening a table, various class-specific restrictions may cause limitations. For example, CSV string fields will be limited to 255 characters. The full list is in the Table Limits topic in the program help.

• For backward compatibility, OpenTableEx() may be used as an alternate function name.

• If a dictionary filename  is not provided and no dictionary file with the same name exists, the first line in a CSV file is considered to be the list of field names.

• If a dictionary filename is provided the field names in first line are ignored and the structure defined in the dictionary files is used.

Example

folder = RunMacro("G30 Tutorial Folder")

view = OpenTable("My First View", "DBASE", {folder + "Customer.dbf"}, {{"Shared", "True"}})

 

// The following example opens a File DSN setup with the Microsoft Excel Driver

//  The database is an Excel file that has a data sheet named "My Customers", note that a $ sign is appended to the table name

view_name = OpenTable("Customers","ODBC", {"test1.dsn", "My Customers$"})

 

// The following example opens an  Excel file that has a data sheet named "My Customers", note that a $ sign is appended to the table name

view_name = OpenTable("Customers", "EXCELX", {"c:\\my_folder\\customers.xlsx", "clients$"}, {{"Shared", "True"}})

 

// The following example opens a CSV file delimited by "|"

// It first deletes an existing dictionary file to force using the current field names in the first line

folder = "c:\\my_folder\\"

file_name = "customers.csv"

dcc_file = GetFileNamePart(file_name , "Title") + ".dcc"

if GetFileInfo( folder + dcc_file  ) != null then do

    DeleteFile(folder + dcc_file )

end

new_table = OpenTable("New Table", "CSV", { folder + file_name , }, {{"Delimiter", "|"}})

 

 

// The following example opens a FFB file

// a test.DCB file must exist to be used as the data dictionary.  An error will occur if test.DCB is not found.

view_name = OpenTable("Customers","FFB", {"test.BIN", })

 

 

 

Error Codes

Error Type	Meaning
Error	Input desired_view_name is null; or input table_spec array has a wrong format (e.g., it has insufficient elements for the specified table_type, or some of its elements are not strings); or a file opening/reading error has occurred, e.g., the user does not have the right permission, or the file is in a bad format; or in case of ODBC, a general ODBC error has occurred, e.g., the datasources and/or the actual databases are not set up properly with matching usernames and passwords
NotFound	The specified table_type is not recognized; or some of the required data or dictionary files cannot be found (note that if an index file is not found, OpenTable() still succeeds and does not signal NotFound); or in case of ODBC, the datasource, table or field (if specified) cannot be found

 

See Also

Function	Summary
CreateTable()	Creates an empty table and opens it as a view
ModifyTable()	Modifies the file structure of a dBASE, fixed-format text or fixed-format binary table
CloseView()	Drops a view from the system
GetViewTableInfo()	Gets information about a table