Compare two dimensional arrays and write to a single cell in excel in java

Question

I am doing data migration testing on my eCommerce site .

Scenario : Need to compare simple product listing for each parent product in old and new site .

I have a web page listing all simple product skus and price that are related to a parent product (configurable product ) .

Each row displays a product sku and its price

I need to get data in to an array from old and new page and then compare . Then add the additional \ missing skus and price to a cell in excel sheet .

I use selenium +java for this (Java1.8 ,sele 2.52) What i did is as follows :

1) got data from 2 dynamic tables table1(old) and table2(new) 2) took the bigger array and wrote big for loops to compare bigger with smaller

Does any one suggests a better way here ?

Answer 1

public static void GetContentFromURL1(String url1) throws IOException {
    System.out.println(url1);
    // Open Url1
    driver.get(url1);

    // Get the no of rows of data
    List<WebElement> rows = driver
            .findElements(By.xpath("html/body/div[2]/div[3]/div[2]/div[3]/div/form/table/tbody/tr"));
    // print no of rows of data
    System.out.println("URL 1 : Total number of rows [ Items in the table ] :" + rows.size());

    // Get Skus , ItemName and Price row wise and add to
    // ArrayList->contentOnSite1
    for (int dataRowOnWebsiteTble = 2; dataRowOnWebsiteTble < rows.size(); dataRowOnWebsiteTble++) {
        // get no of skus listed
        int noOfSkusListed = rows.size() - 2;// one row for header and one
                                                // for footer

        // Get Sku value from table - display in console - add it to Array
        // List
        String Sku = driver.findElement(By.xpath("html/body/div[2]/div[3]/div[2]/div[3]/div/form/table/tbody/tr["
                + dataRowOnWebsiteTble + "]/td[3]")).getText();
        // System.out.println(Sku);
        contentOnSite1.add(Sku);

        arrayRow++;
    }

    // Display the arraylist contents to console
    System.out.println(Arrays.toString(contentOnSite1.toArray()));

    // write to excel - write contents of arraylist to a single cell in
    // excel against corresponding url
    writeToExcel(2, UrlIndex, Arrays.toString(contentOnSite1.toArray()));

}

I wrote similar code to get contents from second url .

Lastly the below method to remove all common items and write to excel sheet

private static void CompareTwoListsandPrintResulttoExcelSheet(List contentOnSite1, List contentOnSite2) {
    if (contentOnSite1.size() > contentOnSite2.size()) {
        ListDiff = new ArrayList(contentOnSite1);
        ListDiff.removeAll(contentOnSite2);
    } else {
        ListDiff = new ArrayList(contentOnSite2);
        ListDiff.removeAll(contentOnSite1);

    }
    if (ListDiff.isEmpty()) {
        result = "Pass : Data and no of items match in two urls";
    } else {
        result = "Fail : Data and no of items does not match in two urls";
    }
    // write to excel - write contents of arraylist to a single cell in
    // excel against corresponding url
    writeToExcel(6, UrlIndex, Arrays.toString(ListDiff.toArray()));

}

Answer 2

I would do something like this...

for (each product)
{
    // navigate to old site
    // verify product name
    // scrape all product SKUs and create a comma delimited string
    String oldSkus = "sku1,sku2,sku3";
    // scrape all prices and create a comma delimited string
    String oldPrices = "$1.00,$2.00,$3.00";

    // navigate to new site
    // verify product name
    // scrape all product SKUs and create a comma delimited string
    String newSkus = "sku1,sku2,sku3";
    // scrape all prices and create a comma delimited string
    String newPrices = "$1.00,$2.00,$4.00";

    // compare old and new SKUs and write to CSV
    // compare old and new prices and write to CSV
}

I would write a line for each comparison to a CSV file using | as a separator. Actual is from the new site and Expected is from the old. You can then import this file into Excel. Some sample lines are below (with header).

Product Name|Validation|Result|Expected|Actual
Product1|Compare SKUs|PASS|sku1,sku2,sku3|sku1,sku2,sku3
Product1|Compare prices|FAIL|$1.00,$2.00,$3.00|$1.00,$2.00,$4.00
Product2|Compare SKUs|PASS|sku1,sku2,sku3|sku1,sku2,sku3
Product2|Compare prices|FAIL|$1.00,$2.00,$3.00|$1.00,$2.00,$4.00

Once you have this imported into Excel, you can quickly filter to show only failed tests and start investigating. I use a method very similar to this on a regular basis to validate products are loaded properly onto our eComm sites.