Understanding the Excel Driver and Container Dialog Boxes version 4.6.6.6.


Let's review the Dialog Boxes of the Excel Driver and Container. On the Driver screen, we have the same actions from the toolbar that we saw when detailing the TXT Driver Dialog Box. On the left side, we enter the component's name, its descriptor, and its provider.


And on the right side, we have the option to whether the driver will be active or not, and the "not replace object" which is executed when making a copy from a backup of another environment where you have the piece for this, and if this option is selected, the data won't be replaced if the source environment has the same driver as the destination environment. We use this functionality to maintain development, testing, and production environments. In the directory tab, we can select the location of the directories where the files are.


We have, in the same way as we saw in the text file driver, the option to read the data locally or from the server. Now let's check the Excel Container. In the toolbar, we have the same options seen in other containers.


However, we highlight two different actions. The "delete temp" and here the "process". When we preview an Excel spreadsheet within the properties directory, a copy of this spreadsheet is saved.


So, the "delete temp" executes a process that deletes these files from within the properties directory. And the "kill process" is used when previewing, for some reason, the display process is stuck. And then this "kill" releases this process.


So, these two actions are just environment maintenance actions. Speaking of the properties of the main tab, we have here the name and the descriptor. On the right side, we have the checkbox to keep the component active.


Then, next, we have the actions. Among them, we have "read", to read data from an Excel spreadsheet. "Copy", to copy Excel files between one driver and another, or between one directory and another.


"Move", to move Excel files between drivers. "Delete", to delete the Excel file within a driver. "Test", to test if an Excel file exists.


"List", to list all files within a driver. "Wait", to wait for the appearance of an Excel file in the driver. The "parameters" button will be activated if we use the "wait" options, where we can specify certain time parameters that the process will wait for the appearance of an Excel spreadsheet.


Going to the name or path tab, we will initially select the source driver. In type, we choose whether we will read data from a single file or from a set of files. "Appointment files", we select either the file name, when reading from a single file, or at least a path if we want to select a group of files.


Next to it, if I select the "all files" option, I will read all files from the path. The "all files" option should always be used together with the "set of files" option. It doesn't make sense to read a set of files if the selection is for only one file and not for a set of other files.


"Subdir", if I want to read not only the files from the driver but also from all the subdirectories below that driver. "Order", if I'm reading a set of files, I can determine the order in which these files will be displayed. And the "Excel file driver target" option is enabled when performing actions involving two directories or two drivers, in the case of "copy" or "move".


The first two, the "Microsoft Excel xlsx using oledb" and the "Microsoft Excel 2003 xlsx using oledb", are executed when we are going to make a reading via a component called "Microsoft Interop" from Excel. Attention, to use these two options, you need to have Excel installed on the machine. The "Direct Access Using Table Format", I will read the Excel spreadsheet that is in a table format.


This type of reading can also be done via Excel Interop, with Excel installed. However, if I select the "Use Native Connection with Excel" option, the reading will be done through a native PIS component, and there is no need to have Excel installed on the server. The "Mapping Cell Range" option includes all the options mentioned in the "Direct Access Using Table Format", the exception being that the mapping of cells is done by different ranges from the spreadsheet, and the previous option is only if the spreadsheet is in table format.


If I select the first or second option, we have below two ways of reading. Either I read a whole worksheet from the spreadsheet, or I execute a query to perform this reading. It is precisely in the "Query" tab, "Excel Query", that we specify what I will read if I have selected the first two mapping options.


If I execute via a worksheet, in this highlighted area here in the video, I specify the name of the worksheet. If I want to read through a query, in this blank area below, I specify the query that will be executed in the spreadsheet. "Mapping Table" is enabled if we choose the third option, "Direct Access Using Table Format".


And here, in the "Preview" button, we can view the Excel spreadsheet to select the areas. But remember, this access to the spreadsheet only works if we are using the installation on a single machine. If we are in the Client-Server architecture, this access will not be allowed.


So, we can't open the spreadsheet. That's why it's nice to have the spreadsheet that is on the server, a copy of it, locally, so you can do the mapping through drag and drop. Still, if you don't have access to the spreadsheet, the range can be entered manually.


But of course, if we click on "Preview", the two "Range Cells" buttons allow you to copy and paste that range you selected in the spreadsheet. And mapping by table format, you specify two ranges, the header and the data. If I don't know the amount of data I'm going to read, in the "Data Range", you only select the first data row and by selecting the "Get Data at First Empty Row" option, it will read until it finds the first empty row.


Now, clicking on "Mapping Cells", I map specific areas within the spreadsheet. In this case, I don't need to have the spreadsheet in table format. The "Mapping Cells" is done if the option we chose for mapping is "Mapping Cell Ranges".


The "Preview Excel", by clicking the "Preview" button, I can see the spreadsheet being read. The "Excel Preview Table" is the preview screen that we have seen in other containers, specifically the text file and Microsoft SQL Server containers. All buttons have the same functionality.


And finally, in "Metadata", I can see the metadata format that is saved for that container. Thank you, and until the next video.