GetExcelData

S	A	W	WS
ü	ü

GetExcelData <List Name> <Path to Spreadsheet> <Query> [<Version> [<ContainsHeader> [<IMEXValue>]]]

<Path to Database> is a literal string or a variable that contains a literal string

<Version> is a literal string or a variable that contains a literal string of value "Excel2003" , "Excel2007" or "Excel2010"

<ContainsHeader> is a literal string or a variable that contains a literal string of value "WithHeader" or "WithoutHeader"

<IMEXValue> is a literal string or a variable that contains a literal string of value 0 or 1

This command uses an OLEDB connection to retrieve data from an Excel Spreadsheet and store it into a list in EMUE. If the list already has members, then this data is added to the list, using the current sorting options.

<Path to Spreadsheet> contains the path and name of the spreadsheet that will be queried.

<Query> contains the query that will be run against the spreadsheet, or if a sheet name alone is provided, all data will be retrieved from that sheet. If the sheet name contains spaces, it must be enclosed in square brackets with a dollar sign. An example would be " [Data Sheet$]". If you are using a query, you may use the column header values in the select statement or if there are no column headers you can use f1, f2, fx, etc. where x is the column position you want to read. If your column headers have spaces or special characters they must be enclosed in square brackets. Periods are not allowed in column names. This will generate an error. If you are using a date value in a WHERE clause, Excel requires the date to be enclosed in pound signs.

<Version> corresponds to the version of Excel that is being used and it should be "Excel2003" , "Excel2007" or "Excel2010". If no value is provided for the version of Excel, EMUE will attempt to use the file extension to determine version.

<Contains Header> indicates whether or not the file has a header record that should be treated as a header and not retrieved. This header record can be used to identify selected columns in the query. If <Contains Header> is set to "WithoutHeader," the first record in the file will not be skipped. If no value is provided for <ContainsHeader>, a header record will be assumed. If "WithHeader" is used, the number of header records to be skipped may be provided following a colon. If no numeric value is provided, then EMUE will skip one header record. It is important to note that if the query includes a sort or requests distinct data, then additional header records should not be skipped with this parameter. Instead, the additional rows should be retrieved and then skipped using the SetListPosition command. Skipping more than one header row on a data set that includes a WHERE clause or a DISTINCT will cause unpredictable results.

<IMEXValue> contains either 1 or 0. If this value is set to 1, the driver will read data as text regardless of its format in Excel. The default value for <IMEXValue> is 1.

Since the name of the worksheet is used in the query in place of a table name, the worksheet name must be known, or referenced based on its position. For example, the first worksheet can be referenced with *Worksheet1 and the second sheet can be referenced by *Worksheet2. Note that when using this moniker in a query, the user should still provide standard Excel notation for the sheet which includes brackets and a dollar sign. That is, to select everything from the first worksheet, the query would be "Select * from [*Worksheet1$]"

Note: The ability to directly and efficiently connect to and read from an Excel file will work in the following versions of Excel: Excel 2003, Excel 2007, Excel 2010.

Note: The same purpose can be accomplished with the GetODBCData and GetOLEDBData commands. This command is intended to be easier to use because the user does not need to specify the connection string.

Note: Please make sure the Excel file you are reading from is closed by all users when executing this command. Otherwise, an error will be generated and EMUE will not read the file because another user will have a lock on the file.

This command retrieves all data in the MCDPatients sheet and stores it in a list called PatientList.

GetExcelData PatientList "C:\EMUE\Adjustments\Patients.xls" "MCDPatients" "Excel2003" "WithHeader" "1"

This command retrieves a set of distinct records from the Charges sheet and stores them in a list called ChargeList.

GetExcelData ChargeList "C:\EMUE\ChargeEntry\Charges.xlsx" "SELECT DISTINCT * FROM [Charges$] " "Excel2007" "WithoutHeader"

This command retrieves all records from the default sheet name and stores them in a list called IncomingList.

GetExcelData IncomingList "C:\EMUE\IncomingPatients\incoming.xlsx" "SELECT * FROM [Sheet1$]" "Excel2010"

This command retrieves columns 1, 2, 6 and 7 from the default sheet name and stores them in a list called Data.

GetExcelData Data "C:\EMUE\InputFiles\WriteOff.xlsx" "SELECT f1,f2,f6,f7 FROM [Sheet1$]" "Excel2010"

The following retrieves a list of patient IDs from the second worksheet in an Excel spreadsheet.

GetExcelData PatientList "C:\EMUE\Patients\PatientList.xlsx" "SELECT PTID from [*Worksheet2$]" "Excel2010"

GetExcelData Providers &FullInputFileName "*Worksheet3" "Excel2010" "WithHeader"

The following retrieves all data from the third worksheet of the input file except for the three header records.

GetExcelData Providers &FullInputFileName "*Worksheet3" "Excel2010" "WithHeader:3"

The following retrieves all data from the worksheet called Input Data in the inputfile.

GetExcelData Providers &FullInputFileName "[Input Data$]" "Excel2010" "WithHeader"

The following retrieves all transactions where the TransactionDate is 02/21/2017.

GetExcelData Transactions "C:\EMUE\DailyTransactions\InputProcessing\DailyTransactions.xlsx" "Select * from [*Worksheet1] where TransactionDate = #02/21/2017#" "Excel2010" "WithHeader"