Import from Text or CSV


Import from text/csv function is used to get data from text or csv files.
Sheetkraft contains the Data from Text/csv option which can be accessed by the Import From Button present in the Sheetkraft Toolbar (See figure).

importfromtext

The required data can be imported from the Text file.
The following guidelines describe how data can be imported from a particular text file using Data from Text/csv.
The data from the text/csv function helps to import data from a single file or from multiple files.

Case 1: Importing data from single file using Data from Text/csv function:


Step 1:

Click on the Import From button and select the Text/csv option to open this dialogue box.

importfromtext

Step 2:

Select the single file in the dropdown provided in the UI of data from the text function. Refer below image.

importfromtext

Step 3:

Now click on the browse option to provide the path of the text file that you want to import and also a check box is available to get the relative path of the selected file.

importfromtext
Once the file path is given, the function automatically imports the data in Raw Text File Preview area.

Step 4:

Now we can edit the data which is imported according to our need with help of options available in the Structure field.

  • There is a check box available to discard the initial lines, you will have to enter how much line you want to remove in the given box.

importfromtext

  • There are different radio buttons available in File contains fields which help us to edit the data or get data in the appropriate format.

    Delimited tabular data: This button helps us to split data with the help of delimiting factors used in text files. There are various delimiting factors available in the dropdown list which you can select while importing data.

    importfromtext

    • In the Column Specification area we can alter/move the column according to our need. The various features in the column specification area are as follows:

    Match Text: It contains the text that you want to match with column header.
    Match Type: This depicts the type of text in the match text column and also helps to validate enter text. It contains a dropdown list with the following functions:
    a) Full match type: It helps to match the text entered in the match text column.
    b) Number match type: provide numbers to each column.
    c) Prefix match type: It helps to match the prefix added in the match text column.
    d) Contains match type: Ithelps check the match text in the column.
    e) Regex match type: It helps to add regex expressions in the match text.
    f) File name match type: Ithelps to provide file name.
    g) File Path match type: It helps to provide file path.
    h) Row match type: It helps to provide the row number.

    Match Index: This helps to make fields required or optional by selecting any one of it from the dropdown list. Format: This helps to add format function to the text entered or any date added. It contains a dropdown list with the following functions and they are General, Text, Trim Text, Number, m/d/yyyy, d/m/yyyy.
    If blank helps to give cell value as “#value” if it is blank.
    If Error helps to give cell value as “#N/A” if there is a certain error.
    Header helps to show the header of the column.

    importfromtext

    • Now in the Column Specification area, we can alter/move the column according to our needs in the following ways:
    “+” button: This helps to add any column from the imported data, filename, filepath and also row no.
    “+ All” button: This helps to add all the imported columns in just one click in the column specification area.
    “x All” button: This helps to remove all the columns which are imported in the column specifications area.
    “ X Select”: If you want to remove any single column then select any column which you want to remove and click on this button.
    “Select” button: This contains a dropdown list - “All” which helps to select all the columns. “None” helps to deselect all the selections. “Invert” helps to reverse all the selection.
    “UP and Down” symbol button: This helps to move columns up and down according to our needs.

    importfromtext

    • Enabling One Value Per Line radio button helps to get each line of the text file in a single cell.
    Fixed Width Tabular Data helps to get a certain range of data from input text files.
    Text in Some Other Form helps to get data from text files with undefined data format.

  • Also there is a check box available to import maximum no of lines from a text file. There is a box available to enter the maximum number of lines you want to import.

importfromtext

  • If the File has a column header, there is a check box available to include or exclude the column header.

importfromtext

Step 5:

After applying all the relevant filters to get the required input data from the text file. Click on the Next button to apply the data from the text function.

Output of Import from Text from Single File.

importfromtext


Case 2 : Importing data from Multiple files using Data from Text/csv function


Step 1:

Use the Get All Files function to get the relative path of all the text files. Click on More button and select the Get All Files option to open this dialogue box.

importfromtext

Now Get All files dialogue box will open and use the browse option to get the relative path of the folder that consists of all the text file.

importfromtext

This function also provides text files from the current folder or all sub folders by selecting the appropriate radio button. There are various check boxes available which help to give File Name, File Path, File Size, Creation Time and Modification Time. According to our need, we can mark the appropriate check boxes for the implementation. Now click on the OK button to the relative path of the text files. Refer image below.

importfromtext

Step 2:

Click on the Import From button and select the Text/csv option to open this dialogue box. Select the multiple files in the dropdown provided in the UI of data from the text function. Refer below image.

importfromtext

Step 3:

Now click on browse option to provide the cell reference of cell containing the path of all the Text Files which is obtained from the Get All Files function.

importfromtext

Once the file path is given, the function automatically imports the data in the Raw Text File Preview area.

Step 4:

Now we can edit the data which is imported according to our need with help of options available in the Structure field.

  • There is a check box available to discard the initial lines, you will have to enter how much line you want to remove in the given box.

importfromtext

  • There are different radio buttons available in File contains field which help us to edit the data or get data in the appropriate format.
    Delimited tabular data radio button help us to split data with the help of the delimiting factor used in text files. There are various delimiting factors available in the dropdown list which you can select while importing data.

    importfromtext

  • Now in the Column Specification area, we can alter/move the column according to our need. The various features in the Column specification area are as follows:

Match Text contains the text that you want to match with column header. Match Type depicts the type of text in the match text column and also helps to validate enter text. It contains a dropdown list with the following functions: a) Full match type helps to match the text entered in the match text column.
b) Number match type provides numbers to each column.
c) Prefix match type helps to match the prefix added in the match text column.
d) Contains match type helps check the match text in the column.
e) Regex match type helps to add regex expressions in the match text.
f) File name match type helps to provide file name.
g) File Path match type helps to provide file path.
h) Row match type helps to provide the row number.

Match Index helps to make fields required or optional by selecting any one of it from the dropdown list. Format helps to add format function to the text entered or any date added. It contains a dropdown list with the following functions and they are General, Text, Trim Text, Number, m/d/yyyy, d/m/yyyy.
If blank helps to give cell value as “#value” if it is blank.
If Error helps to give cell value as “#N/A” if there is a certain error.
Header helps to show the header of the column.

importfromtext

  • Now in the Column Specification area, we can also alter/move the column according to our needs in the following ways:
    “+” button: This helps to add any column from the imported data, filename, filepath and also row no.
    “+ All” button: This helps to add all the imported columns in just one click in the column specification area.
    “x All” button: This helps to remove all the columns which are imported in the column specifications area.
    “ X Select”: If you want to remove any single column then select any column which you want to remove and click on this button.
    “Select” button: This contains a dropdown list - “All” which helps to select all the columns. “None” helps to deselect all the selections. “Invert” helps to reverse all the selection.
    “UP and Down” symbol button: This helps to move columns up and down according to our needs.

    importfromtext

    • Enabling One Value Per Line radio button helps to get each line of the text file in a single cell. \
    • Fixed Width Tabular Data helps to get a certain range of data from input text files. \
    • Text in Some Other Form helps to get data from text files with undefined data format.
  • Also there is a check box available to import maximum no of lines from a text file. There is a box available to enter the maximum number of lines you want to import.

    importfromtext

  • If the File has a column header, there is a check box available to include or exclude the column header.

    importfromtext

Step 5:

After applying all the relevant filters to get the required input data from the text file. Click on the Next button to apply the data from text function.

Output of Import from Text from Multiple Files.

importfromtext