how do I make Selenium webdriver to read a csv file to perform an action

Question

how do I make Selenium webdriver to read a csv file to perform an action. I have my script to load it in the browser and perform an action how do I make it to read csv file and replace that action with the data in the csv file

Answer 1

The best way to do this would be to use another library that specializes in such parsing, and feed it into memory. A good StackOverflow question regarding this is here.

Answer 2

Remember that WebDriver simply interacts with a browser. It's the language that your writing in that does file IO. I'd suggest creating a CSV class. Since csv is just a text file, you can read it in as a string and iterate through each line. Split by commas and you're good to go. You didn't specify a language so here is an example in C# from our library. Notice I use yield for efficiency. This was something I found somewhere. No need to reinvent the wheel.

public static class CSV
{
    private static bool ignoreFirstLineDefault = false;

    public static IEnumerable<IList<string>> FromFile(string fileName)
    {
        foreach (IList<string> item in FromFile(fileName, ignoreFirstLineDefault)) yield return item;
    }

    public static IEnumerable<IList<string>> FromFile(string fileName, bool ignoreFirstLine)
    {
        using (StreamReader rdr = new StreamReader(fileName))
        {
            foreach (IList<string> item in FromReader(rdr, ignoreFirstLine)) yield return item;
        }
    }

    public static IEnumerable<IList<string>> FromStream(Stream csv)
    {
        foreach (IList<string> item in FromStream(csv, ignoreFirstLineDefault)) yield return item;
    }

    public static IEnumerable<IList<string>> FromStream(Stream csv, bool ignoreFirstLine)
    {
        using (var rdr = new StreamReader(csv))
        {
            foreach (IList<string> item in FromReader(rdr, ignoreFirstLine)) yield return item;
        }
    }

    public static IEnumerable<IList<string>> FromReader(TextReader csv)
    {
        //Probably should have used TextReader instead of StreamReader
        foreach (IList<string> item in FromReader(csv, ignoreFirstLineDefault)) yield return item;
    }

    public static IEnumerable<IList<string>> FromReader(TextReader csv, bool ignoreFirstLine)
    {
        if (ignoreFirstLine) csv.ReadLine();

        IList<string> result = new List<string>();

        StringBuilder curValue = new StringBuilder();
        char c;
        c = (char)csv.Read();
        while (csv.Peek() != -1)
        {
            switch (c)
            {
                case ',': //empty field
                    result.Add("");
                    c = (char)csv.Read();
                    break;
                case '"': //qualified text
                case '\'':
                    char q = c;
                    c = (char)csv.Read();
                    bool inQuotes = true;
                    while (inQuotes && csv.Peek() != -1)
                    {
                        if (c == q)
                        {
                            c = (char)csv.Read();
                            if (c != q)
                                inQuotes = false;
                        }

                        if (inQuotes)
                        {
                            curValue.Append(c);
                            c = (char)csv.Read();
                        }
                    }
                    result.Add(curValue.ToString());
                    curValue = new StringBuilder();
                    if (c == ',') c = (char)csv.Read(); // either ',', newline, or endofstream
                    break;
                case '\n': //end of the record
                case '\r':
                    //potential bug here depending on what your line breaks look like
                    if (result.Count > 0) // don't return empty records
                    {
                        yield return result;
                        result = new List<string>();
                    }
                    c = (char)csv.Read();
                    break;
                default: //normal unqualified text
                    while (c != ',' && c != '\r' && c != '\n' && csv.Peek() != -1)
                    {
                        curValue.Append(c);
                        c = (char)csv.Read();
                    }
                    result.Add(curValue.ToString());
                    curValue = new StringBuilder();
                    if (c == ',') c = (char)csv.Read(); //either ',', newline, or endofstream
                    break;
            }

        }
        if (curValue.Length > 0) //potential bug: I don't want to skip on a empty column in the last record if a caller really expects it to be there
            result.Add(curValue.ToString());
        if (result.Count > 0)
            yield return result;

    }
}

Answer 3

I suggest you to use Excel files instead of csv. limitation while using CSV is when you want to use the delimiter ',' in the data itself e.g. if you you have an address field say Gyandeep Colony, B. No : - 1/5 here the ',' make break your code.

The way i do reading of data is by using my Utility Code

public class ExcelHandler {

public static String getSheetData(String path,String sheetName, int col , int row) {
    Workbook workbook;
    String data = null;
    try {
        workbook = Workbook.getWorkbook(new File(path));
        Sheet sheet = workbook.getSheet(sheetName);
         data  = sheet.getCell(row,col).getContents();

    } catch (BiffException e) {

        e.printStackTrace();
    } catch (IOException e) {

        e.printStackTrace();
    }

    return data;

}


public static String getSheetData(String path, int sheetNo, int col , int row) {
    Workbook workbook;
    String data = null;
    try {
        workbook = Workbook.getWorkbook(new File(path));
        Sheet sheet = workbook.getSheet(sheetNo);
         data  = sheet.getCell(row,col).getContents();

    } catch (BiffException e) {

        e.printStackTrace();
    } catch (IOException e) {

        e.printStackTrace();
    }

    return data;

}

}

I just call these method wherever i want in my code .

Lets take an example

WebElement username = driver.findElement(By.id("username"));
username.sendKeys(Utility.getSheetData("Login.xls",0,1,0));

Happy Coding :)

Answer 4

FileInputStream file = new FileInputStream(new File("C:\\testdata.xls")); 
  HSSFWorkbook workbook = new HSSFWorkbook(file);

  HSSFSheet sheet = workbook.getSheetAt(0);

for (int i=1; i <= sheet.getLastRowNum(); i++){

        String keyword = sheet.getRow(i).getCell(0).getStringCellValue();

        searchbox.sendKeys(keyword);

        searchbox.submit();       

        driver.manage().timeouts().implicitlyWait(10000, TimeUnit.MILLISECONDS);

}

  workbook.close();
  file.close();