How to write data to Excel/CSV in Jmeter using BeanShell scripting?

You might have faced issues in writing data to excel/csv in JMeter. In this blog post, we will see How to write data to Excel/CSV in Jmeter using BeanShell scripting? By using Regular Expression Extractor, you can extract data from the response. To write extracted data to excel/csv in Jmeter, you need to use BeanShell PostProcessor sampler.

How to extract data from the response?

You can refer my slideshare presentation on how to extract data from the response.

How to write data to excel/csv in JMeter using BeanShell PostProcessor?
Add Thread Group and then right click Add > Post Processor > BeanShell PostProcessor as shown below.
[auto_thumb width=”150″ height=”150″ link=”” lightbox=”true” align=”center” title=”How to write data to Excel CSV in Jmeter using BeanShell scripting – Add BeanShell PostProcessor” alt=”How to write data to Excel CSV in Jmeter using BeanShell scripting – Add BeanShell PostProcessor” iframe=”false” frame=”true” crop=”true”]http://qainsights.com/wp-content/uploads/2013/09/How-to-write-data-to-Excel-CSV-in-Jmeter-using-BeanShell-scripting-Add-BeanShell-PostProcessor.jpg[/auto_thumb]
E.g. to write Order Reference Number to CSV/Excel file, paste below code to your BeanShell scripting as shown below.
refnum = vars.get("Reference_Number");
log.info(refnum);  // if you want to log something to jmeter.log file
// Pass true if you want to append to existing file
// If you want to overwrite, then don't pass the second argument
f = new FileOutputStream("yourpath/result.csv", true);
p = new PrintStream(f);
this.interpreter.setOut(p);
print("Reference Number is," + refnum);
f.close();

[auto_thumb width=”150″ height=”150″ link=”” lightbox=”true” align=”center” title=”How to write data to Excel CSV in Jmeter using BeanShell scripting – BeanShell PostProcessor Sample Code” alt=”How to write data to Excel CSV in Jmeter using BeanShell scripting – BeanShell PostProcessor Sample Code” iframe=”false” frame=”true” crop=”true”]http://qainsights.com/wp-content/uploads/2013/09/How-to-write-data-to-Excel-CSV-in-Jmeter-using-BeanShell-scripting-BeanShell-PostProcessor-Sample-Code.jpg[/auto_thumb]

Thanks for visiting QAInsights! Check out our new portal Testifications.com to get updates on Software Testing related certifications. We’re always posting interesting articles on QAInsights. I request you to subscribe so you don’t miss out anything.

Searching for QA Jobs Openings, check it at www.QAJobOpenings.com

Want to start a blog like this, please visit SpidyDomain to buy cheap web hosting and register domain.

Subscribe to our QAInsights YouTube Channel | Subscribe our free weekly newsletter or Google feeds

5 thoughts on “How to write data to Excel/CSV in Jmeter using BeanShell scripting?”

  1. Hi naveen thanks for your such a wonderful topic..

    Please help me,

    I need a value of a object which is displayed at the end of the thread. Its Name displayed in “Send Parameters With the Request” is displayed as data and its value will always a new number. What is want is to get this value exported to CSV each time . Method is type “GET”. Please let me know how can i get the value in CSV . Currently i am using it as

    refnum = vars.get(“data”);
    log.info(refnum); // if you want to log something to jmeter.log file
    // Pass true if you want to append to existing file
    // If you want to overwrite, then don’t pass the second argument
    f = new FileOutputStream(“D:/apache-jmeter-2.9/bin/result.csv”);
    p = new PrintStream(f);
    this.interpreter.setOut(p);
    print(“Invoice,” + refnum);
    f.close();

  2. Thanks Naveen,
    But these code doesn’t work with with .xls file.
    Can you suggest me how I can write to an Pure Excel File directly using BeanShell Scripting.

  3. Hi Naveen ,
    I am trying to append some data to the file by using your code.
    refnum = vars.get(“data”);
    log.info(true);
    but its showing an error .
    since i am new to JMeter i dont know what to do.So can you help me for this.

    thanks
    Abhinav

  4. Hi Naveen,

    I came across your blog, as I was stuck up in following scenario. Seeking your help here.

    We are working on web services automation project using JMeter 4.0 In our response, JMeter returns data in json format but we would like to store only specific data (Account ID or Account inquiry fields) from that json into csv file but it stores data in csv file in unformatted format. Looking for a workaround on this.

    We are using following code:

    import java.io.File;
    import org.apache.jmeter.services.FileServer;

    Result = “FAIL”;
    Responce = prev.getResponseDataAsString():

    if(Responce.contains(“data”))
    Result = “PASS”;

    f = new FileOutputStream(“C:/Users/Amar.pawar/Desktop/testoup.csv”,true);
    p = new PrintStream(f);

    p.println(vars.get(“/ds1odmc”) + “,” + Result):

    p.close();
    f.close():

    Following error is getting encountered:

    Error invoking bsh method: eval In file: inline evaluation of: “import java.io.File; import org.apache.jmeter.services.FileServer; Result = “FA . . . ” Encountered “:” at line 5, column 42.

    We are looking for saving specific data in CSV (or txt) instead of complete output in unformatted format. Please help.

  5. Hi Naveen,

    I came across your blog, as I was stuck up in following scenario. Seeking your help here.

    We are working on web services automation project using JMeter 4.0 In our response, JMeter returns data in json format but we would like to store only specific data (Account ID, Customer ID or Account inquiry fields) from that json into csv file but it stores data in csv file in unformatted format. Looking for a workaround on this.

    We are using following code:

    import java.io.File;
    import org.apache.jmeter.services.FileServer;

    Result = “FAIL”;
    Responce = prev.getResponseDataAsString():

    if(Responce.contains(“data”))
    Result = “PASS”;

    f = new FileOutputStream(“C:/Users/Amar.pawar/Desktop/testoup.csv”,true);
    p = new PrintStream(f);

    p.println(vars.get(“/ds1odmc”) + “,” + Result):

    p.close();
    f.close():

    Following error is getting encountered:

    Error invoking bsh method: eval In file: inline evaluation of: “import java.io.File; import org.apache.jmeter.services.FileServer; Result = “FA . . . ” Encountered “:” at line 5, column 42.

    We are looking for saving specific data in CSV (or txt) instead of complete output in unformatted format. Please look into the matter & suggest.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

New Year. New Hope. Learn TruWeb. 78% OFF for Limited Time of my Udemy course..BUY NOW