Accessing data from Excel files directly can be a great way to save time and streamline your process. While many users are already converting Excel files (.xls) to comma separated files (.csv), EMUE can read directly from the Excel file. When accessing Excel directly, EMUE reads the file like a database rather than an input file. This may seem daunting if you have never worked with databases in EMUE, but it is not difficult.
When working with any database with EMUE, including Excel in this case, records are read into a list. Before the data can be read in, the list must be created. The following line of EMUEScript creates the list.
The next line of code retrieves the data from Excel and stores it in the aforementioned list, called ExcelData, which is the first parameter.
The second parameter is the connection string that tells EMUE how to connect to the Excel file. The majority of the connection string will always be the same. The only parts that should be changed are hightlighted in red. The first highlighted piece is the path and file name of the Excel file that EMUE should read. The second highlighted piece, "HDR=Yes" specifies whether the first record in the file is a header or not. If "Yes" is left, then the first record is not retrieved because it is the header. If the value is changed to "No" then no header is assumed.
The third parameter is the query that is used to retrieve data. Most of the time, all of the data will be retrieved. When that is the case, the query can be left like it is except for changing the name of the sheet. If the sheet in the Excel file has been renamed, then that name should appear where "Sheet1" is now. The dollar sign and the brackets should remain part of the query.
GETOLEDBDATA ExcelData "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\EMUE\MyExcel.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';" "SELECT * FROM [Sheet1$]"
Once this line has run, all of the data from the file will be stored in the list and each record can be retrieved individually. Records are retrieved from a list in a very similar manner to how they are retrieved from a file. Instead of using a READ command along with several COPY or COPYFIELD commands, with a list, there will be a GETLISTMEMBER command that retrieves the record and stores each of the fields into variables.
EMUE Code when reading from a file:
COPYFIELD PTID FILE 1 ","
COPYFIELD LastName FILE 2 ","
COPYFIELD FirstName FILE 3 ","
EMUE Code when reading the same from a list:
GETLISTMEMBER ExcelData PTID LastName FirstName
If there are a lot of fields to be retrieved from the file, retrieving them from a list can be a lot easier than using many COPYFIELD commands. The ultimate result is the same - there are variables named PTID, LastName, and FirstName that can be used throughout the script.