How to import CSV or Text file into Excel
“Import text/csv file” is a file instruction under Excel Group that allows to import text or CSV file with data separated using specific delimiter. Following image shows an example of data into the CSV file and data into the Excel file after importing through “Import text/csv file” instruction.
Steps to configure “Import Text/CSV file” instruction
Section – Source and target files
1. Open a process and turn on design mode
2. Load file manipulation step in your process
3. Click on “Click here to select an instruction“. An instruction selector window will open. Expand an Excel Group an select “Import Text/csv file” instruction
4. Click on Link on the source side to link the source text file
5. Click on Link button on the target side to link the target Excel file
6. Expand the worksheet drop down list and select a worksheet
7. Configure the advanced range address by selecting the starting cell address and then choose Fit to the source data to import all the records
Section – Delimiter and header parameters
8. Choose the delimiter that separate the data in to the source file. Most common delimiters are Comma and Tab. If there is a custom delimiter used in the source file to separate data, then select the Custom option and enter the delimiter in the text box
9. Enter the number of lines to be skipped from the source file when importing the file into the Excel file. This option is helpful when source text / CSV file is auto-generated by other system and file contains header information
10. Tick the “Source data has header rows” check box – If source Text / CSV file contains header. If you tick the Source data has header rows check box, another check box “Copy headers to target” will appear that allows to copy the header in the target Excel file
Section – Bad input tolerance level
11. Choose the bad input tolerance level option. This option allows you to control the bad data in the source file to be imported into the Excel file. Please refer to this article on how to configure bad input tolerance level.
12. Select the date format that is in the source text / CSV file, from the drop down list.
13. Click on Save button to save the step details.
14. Click on Run button to run the step.
After running the step successfully, CSV data will be imported into the target Excel file as shown in image at the top.