Data From Excel

Import From Excel:

Import from excel function is used to get data from an excel file.

Sheetkraft contains the data from excel option which can be accessed by the Import From Button present in the SheetKraft Toolbar ( See figure).

Case 1: Importing from a single file:

The following guidelines describe how data can be imported from a particular workbook using Data From Excel.

Step 1:

Click on the Import From Button and select the Excel option to open this dialogue box.

Step 2:

Click on the browse button as shown in the above picture and if the file location is relevant to the workbook’s location, click on the checkbox and browse for the relevant workbook from where the data is to be imported.

Step 3:

Select the sheet from where the data is to be imported, you can change the sheet by clicking on the change sheet option and then choose the range as shown below:

Pro Tip: Choose the range to import in the RegionDown/RegionAcross/RegionRight format. This is useful when the data keeps changing. Sheetkraft, by default, imports the data in the RegionDown format.

NOTE:

  • Data unorganized: This function is used when you want to import the data as it is without any changes in the import data.

  • If the excel file has blank rows in the data, select the option to discard blank rows in selection in the UI.

  • You can also add a limit to the number of rows to be imported, let’s say that you want only 5 rows to be imported, tick on the import a maximum of ___ lines in the UI.

  1. If the headers of the table which are imported are needed to be a part of the table, choose the include option in the column has headers section. A pop up as shown below will be displayed, click on yes.

  • The Column Specification section consist of the following: Match Text: It contains the text that you want to match with the column header. Match Type: This helps to depict the type of the match text. This further contains the following:

    • Full: This matches the entire text from the match text column.

    • Number: This match type helps to provide numbers to each column.

    • Prefix: This match type helps to match the prefix added in the match text column.

    • Contains: This match type is selected when the match text contains a part of the column header.

    • Regex: This match type is selected when the match text has a regex.

    • File Name: This helps to provide file name.

    • File Path: This helps to provide file Path.

    • Row: This helps to provide the row number.

Match Index: This helps to make the field required, optional, All Headers or All values. You can choose it by the dropdown option provided.

If Blank: This contains the content that will be displayed if the cell is blank. The default content that is displayed is #VALUE.

If Error: This contains the content that will be displayed if the cell has an error. The default content that is displayed is #N/A.

Header: This shows the header of the column. You can edit the header according to your convenience.

  • In Column Specification area, we can move/alter columns using the following buttons:
    “    +”: This helps to add columns from the imported data, you can also add file name, file path and row numbers.
    “    + All”: You can use this to add ALL the columns from the imported data, this does not include the file name/file path/row numbers.
    “    X All”: This button helps to remove all the columns from the imported data.
    “    X Select”: You can use this button to select any specific columns and remove them.
    “    Select”: This contains a dropdown list which has All, None and Invert. All is used to select all the columns in the column specification, None is used to deselect the selections and Invert is used to invert the arrangement of the selected columns.
    “    UP and DOWN”: These two buttons are present next to select, it helps to move the columns up and down according to the need.

  • You can get the sheet name, file name, file location through the DataFromExcel UI. In the Column Specifications area, click on the ‘+’ and choose the required headers, it would be imported along with the other columns.

The picture below represents how the table would look like.

Case 2: Importing from multiple sheets:

Follow Step 1 and Step 2 from Case 1.

Step 3:

Select the range which has the sheet names specified in the “Specify sheet(s) by name or range option.”

Step 4:

In the range section, choose the range from any one of the sheets in the region down format and click on OK .

NOTE:

  1. This kind of import can only be used when all the sheets have the same columns of data, the rows can vary in every sheet and will not affect the import.

  2. At times, sheet names keep changing in the file. In such cases, use the number 1 instead of the sheetname in the
    _DataFromExcel.SK     formula.


Case 3: Importing From Multiple Workbooks:

Follow Step 1 from Case 1.

Step 2:

Use the GetAllFiles option to get the relevant file locations that are to be imported.

Step 3:

In the UI of DataFromExcel, change the import values from a single file to multiple files and choose the range of file locations which was obtained in step 2.

Step 4:

Follow step 3 from Case 1 if there is only one sheet which is to be imported.

NOTE:

  1. This type of import will only be successful if all the workbooks have the same Columns with data.