How to read a csv file - Part 2 - multiple columns

Problem

You want to read a csv file into an array. The csv file contains multiple columns such as below:

SNO,TITLE,URL
1,Google Search,google.com
2,Youtube Video,youtue.com
3,Faceook,facebook.com
4,Twitter,twitter.com
5,Amazon Homepage,amazon.com

Note:

  • csv files are "comma separated values" files
  • As the name suggests, each column is delimited by comma
  • csv files are plain text file
  • If you have Microsoft Excel installed, you can easily open a csv file by double-click on the file. The csv file will be opened in Microsoft Excel.

Solution

We read the entire csv file, split them into lines, split each line into columns, then store each column value into array.

The underlying language of TagUI is javascript. As such, you can use many of the javascript functions right inside TagUI script.

Sample Code

array_title = []
array_url = []
csv_file = 'data.csv'
load '+csv_file+' to lines

array_lines = lines.split('\n')

for (n=0; n<array_lines.length; n++) 
{
  items = array_lines[n].split(',')
  array_title[n] = items[1].trim()
  array_url[n] = items[2].trim()
}

The list of title and url are now contained in the arrays array_title and array_url respectively..

Explanation

Line 1: Create an empty array array_title to store the list of titles
Line 2: Create an empty array array_url to store the list of url
Line 3: Specify the filename
Line 4: Read the entire csv into the string variable lines using the load command.
Line 6: Split the string variable lines into an array of individual lines using the javascript function split()
Line 8:
  • Loop through each line using for loop
  • You can find out the number of lines in the csv file using array_lines.length
  • Line 10: Split each line into an array of columns using the javascript function split()
    Line 11:
  • Store each title into the array array_title
  • Strip away any whitespace from both ends of a string using the javascript function trim(). Whitespace in this context is all the whitespace characters (space, tab, no-break space, etc.) and all the line terminator characters (LF, CR, etc.)
  • Line 12: Store each url into the array array_url

    Output

    You can display the content of the arrays array_title and array_url using the following code:

    for (n=0; n<array_lines.length; n++) 
    {
      echo n+". title = " + array_title[n]
      echo n+". url = " + array_url[n]
    }
    

    The output is as follows:

    0. title = TITLE
    0. url = URL
    1. title = Google Search
    1. url = google.com
    2. title = Youtube Video
    2. url = youtue.com
    3. title = Faceook
    3. url = facebook.com
    4. title = Twitter
    4. url = twitter.com
    5. title = Amazon Homepage
    5. url = amazon.com
    
    

    Note that javascript array starts from 0. The first item (array item 0) is the column heading.

    Variations

    If you just want to process the data, you may want to start from 1 as follows:

    for (n=1; n<array_lines.length; n++) 
    {
      echo n+". title = " + array_title[n]
      echo n+". url = " + array_url[n]
    }
    

    The output will then be as follows:

    1. title = Google Search
    1. url = google.com
    2. title = Youtube Video
    2. url = youtue.com
    3. title = Faceook
    3. url = facebook.com
    4. title = Twitter
    4. url = twitter.com
    5. title = Amazon Homepage
    5. url = amazon.com
    

    Related Articles

    Other Resources

    TagUI Tutorials

    Questions / Comments

    If you have any questions or comments on the codes above, you can leave your comments below, or post your questions in the RPA Community Forum.

    Comments (0)

    There are no comments posted here yet

    Leave your comments

    Posting comment as a guest. Sign up or login to your account.
    Attachments (0 / 3)
    Share Your Location