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?

Add Thread Group and then right click Add > Post Processor > BeanShell PostProcessor as shown below.
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();

About the Author

9 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();

    Reply
  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.

    Reply
  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

    Reply
  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.

    Reply
  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.

    Reply
    • There are a couple ways to accomplish this, you can use one of the post processors, like json extractor or a regex to obtain the value, saving it in a variable.

      Then ammend the script above by pulling in the variables at begining…
      Var1 = vars.get(“TheVariableNameFromPostProcessor”);

      Then in your print statement, something like this
      println(var1 + “,” + Var2 + “,” + Var3);

      The output will be your variables, separated by comma.

      Reply
  6. Hii All

    I think that the following code may help problems to be sorted out.

    ** Make sure that before you execute the following code, you have to use regular expresssion extractor for that particular thing that you want to print, and you have call that expression in the code, eg: expression is “67hkval=(.+?)” , regular expresssion extractor name is val and the expression called is ${val_g1}

    Code:

    FileWriter f = new FileWriter (“path”, true);
    BufferedWriter b = new BufferedWriter (f);
    String s = (“${val_g1}”);
    b.write(“Val:” + “${val_g1}”);
    b.write(System.lineSeparator());
    log.info(“val” + s);
    b.close();
    f.close();

    Reply

Leave a Comment

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

Hamster - Launch JMeter Recent Test Plans SwiftlyDownload for free
+
Share via
Copy link