Data pulling from Excel sheet/.CSV sheet

asishsatapathy

Can some body tell me, how to data pulling form excel/.csv sheet and pass into the application.

RobinLRandall

Does the following link help:

http://www.eggheadcafe.com/community/aspnet/3/10043285/convert-csv-file-into-htm.aspx

RR

narayan

Asish,

There are 2 ways you can go about this:
1) Use Excel and work on excel like a database. See http://sahi.co.in/w/working-with-excel-sheets
Or
2) Save the Excel as a csv file and work on the csv file.

I strongly recommend the second way, for these reasons
1) Excel 2007 seems not to work with the 1st approach.
2) csv files are plain text files and can be checked in to subversion or other source control softwares. Diffs and versioning work meaningfully on them and it is easy to see how and why something was changed by another team member.
3) You can always open a csv file in excel to do further processing if needed.

Regards,
Narayan

asishsatapathy

I have written the below script for data pulling from excel sheet,
still the script

narayan

Look at the logs, and on the Sahi console and see if there are errors.
Another point is, escape the \ in datafile

var datafile="jdbc:excel:c:\\Testdata.xls";

shatrughan

Hi Narayana,

According to your suggestion:

There are 2 ways you can go about this:
1) Use Excel and work on excel like a database.
Or
2) Save the Excel as a csv file and work on the csv file.

I strongly recommend the second way, for these reasons
1) Excel 2007 seems not to work with the 1st approach.
2) csv files are plain text files and can be checked in to subversion or other source control softwares. Diffs and versioning work meaningfully on them and it is easy to see how and why something was changed by another team member.
3) You can always open a csv file in excel to do further processing if needed.

I have save the excel file as csv file, but when i save the excel file as csv file the extension of file change from (.xls) to (.csv.xlsm).

I have tried to fetch the data from that file but got the message "--Stopped Playback: FAILURE--".


function googlesearch($train)
{
_setValue(_textbox("q"), $train);
_click(_submit("Google Search"));
}

googlesearch("irctc");
_click(_link("IRCTC Online Passenger Reservation Site."));
var $db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:\\sahi_20090304\\sahi\\copy of Book1.csv.xlsm","","");

$rs = $db.select("select * from [Sheet1$]");

_setValue(_textbox("userName"), $rs[1]["NAME"]);

_setValue(_password("password"), $rs[1]["PASSWORD"]);


var $length1 = $rs.length;


for (var $i = 0; $i < $rs.length; $i++)

{
_setValue(_textbox("userName"), $rs[$i]["NAME"]);

_setValue(_password("password"),$rs[$i]["PASSWORD"]);

_click(_submit("Login"));
_click(_link("Log Out"));
_navigateTo("irctc.co.in/");

}

Regards,
Shatrughan

aspatel80

Hi shatrughan,

I think the file should not have spaces. Try replacing spaces with underscore or keep single word filename.
You can also use excel 2003 document as your database (.xls). Refer to post : http://sahi.co.in/forums/viewtopic.php?id=684

Regards,
Ashvin.

shatrughan

Hi Ashvin,

Thanks for responding.

I have tried with

var $db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:\\Test.csv","","");

but it is not working for me.I am still getting the error --Stopped Playback: FAILURE--.

i have also tried with

var $db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:Driver={Microsoft Text Driver (*.txt; *.csv)}; + DBQ=C:\\Test.csv; Extensions=csv,txt","","");

but have not Succeeded.

Regards,
Shatrughan

suresh

Hi Shatrughan,

As Narayan mentioned above is correct.... and We can retrieve the data from excel-2007 also.
As above, your script contains syntax errors, Please try to use the following code and execute as mentioned below.

Note: Below script is retrieved the data from excel 2007.
Note: If ur working on excel 2003, the extentions is ".xls" and excel 2007, the extention is ".xlsx"

Script:

function googlesearch($train)

{

_setValue(_textbox("q"), $train);

_click(_submit("Google Search"));

}

googlesearch("irctc");


_click(_link("IRCTC Online Passenger Reservation Site."));


var $db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:\\irctc_testdata2.xlsx;","","");

$rs = $db.select("select * from [Sheet1$]");

var $length1 = $rs.length;


for (var $i = 0; $i < $rs.length; $i++)

{
_setValue(_textbox("userName"), $rs[$i]["NAME"]);

_setValue(_password("password"),$rs[$i]["PASSWORD"]);


_click(_submit("Login"));

_click(_link("Log Out"));

_click(_link("Click here"));

}


Please let me know if ur facing any problem.


Rgds,
Suresh

nayan1111

Hi Suresh,

I have tried that code, it works for excel but through error with .CSV file.

I have tried that code to connect with CSV

function googlesearch($train)
{
_setValue(_textbox("q"), $train);

_click(_submit("Google Search"));
}

googlesearch("irctc");

_click(_link("IRCTC Online Passenger Reservation Site."));

var $db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:\\Test.csv","","");

$rs = $db.select("select * from [Test$]");

_assertEqual("braj1999", $rs[1]["NAME"]);

// _assertEqual("shatru10", $rs[0]["NAME"]);

_setValue(_textbox("userName"), $rs[1]["NAME"]);

_setValue(_password("password"), $rs[1]["PASSWORD"]);

var $length1 = $rs.length;

for (var $i = 0; $i < $rs.length; $i++)

{
_setValue(_textbox("userName"), $rs[$i]["NAME"]);

_setValue(_password("password"),$rs[$i]["PASSWORD"]);

_click(_submit("Login"));
_click(_link("Log Out"));
_navigateTo("irctc.co.in/");
}

Now i am able to fetch the data from .CSV file but I have to open the CSV file all the time.
When I close the file, tool raise the error.
Can u please help me to resolve the issue.

Regards,
Nayan

LaxshmiVijay

Hi

I am facing an issue with the above subject.

var $length1 = $rs.length is returning number 16 always. This excel contains 25 columns and 1 Row.

I am using another excel sheet for another script, however that is working fine. This has 2 columns and 2 Rows.

Can anybody please tell me any limitation with columns?