Query a CSV file from DbFit

[shaju c]

Hi All,

Is it possible to write sql like statements to query CSV files from DbFit. Ex. select count(*) from filename.csv

We would like to compare data extracted from csv file to a  table.

Thanks

Shaju

[Yavor Nikolov]

Hi,

There is no special feature for that in DbFit.

One way to handle such scenario might be accessing the csv through some database built-in functionality (e.g. there is something in HSQLDB -  http://hsqldb.org/doc/2.0/guide/texttables-chapt.html; and there are also External Tables in Oracle).

But even a more generic solution could be to process the csv file in advance to count the number of records -> and load that count into a database table.

Regards,

Yavor

--
You received this message because you are subscribed to the Google Groups "dbfit" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dbfit+unsubscribe@googlegroups.com.
To post to this group, send email to db...@googlegroups.com.
Visit this group at https://groups.google.com/group/dbfit.
For more options, visit https://groups.google.com/d/optout.

[Mark Matten]

If you want to compare the contents of a file produced by a process (actual results) to a known (expected results) you an try using the FitLibrary CompareFiles fixture. 

I've used this approach a number of times, specially with testing ETL processes. 

Some doco here:

http://butunclebob.com/FitNesse.UserGuide.FitLibraryUserGuide.FileCompareFixture

Sent from my iPhone

To unsubscribe from this group and stop receiving emails from it, send an email to dbfit+un...@googlegroups.com.

[shaju c]

Hi Mark and Yavor,

Thanks. My requirement is to compare csv file records to table in oracle after the ETL job is finished.

Oracle external table is indeed one option where as a one time activity one has to create a table structure with with corresponding data type and length of csv file.

I was wondering if DbFit supports H2 database and how can i configure in DbFit http://www.h2database.com/html/download.html

H2 database is lightweight built on Java. I just installed in my machine and created a simple CSV file and saved in the bin directory of H2. Below is the screen shot.

I am able to query simple sql's like select count(*) from CSVREAD('emp.csv') 

On Thursday, 25 August 2016 01:06:11 UTC+5:30, Mark Matten wrote:

If you want to compare the contents of a file produced by a process (actual results) to a known (expected results) you an try using the FitLibrary CompareFiles fixture. 

I've used this approach a number of times, specially with testing ETL processes. 

Some doco here:

http://butunclebob.com/FitNesse.UserGuide.FitLibraryUserGuide.FileCompareFixture

Sent from my iPhone

On 24 Aug 2016, at 19:25, Yavor Nikolov <nikolo...@gmail.com> wrote:

Hi,

There is no special feature for that in DbFit.

One way to handle such scenario might be accessing the csv through some database built-in functionality (e.g. there is something in HSQLDB -  http://hsqldb.org/doc/2.0/guide/texttables-chapt.html; and there are also External Tables in Oracle).

But even a more generic solution could be to process the csv file in advance to count the number of records -> and load that count into a database table.

Regards,

Yavor

On Wed, Aug 24, 2016 at 8:32 PM, shaju c <sha...@gmail.com> wrote:

Hi All,

Is it possible to write sql like statements to query CSV files from DbFit. Ex. select count(*) from filename.csv

We would like to compare data extracted from csv file to a  table.

Thanks

Shaju

--
You received this message because you are subscribed to the Google Groups "dbfit" group.

To unsubscribe from this group and stop receiving emails from it, send an email to dbfit+un...@googlegroups.com.
To post to this group, send email to db...@googlegroups.com.
Visit this group at https://groups.google.com/group/dbfit.
For more options, visit https://groups.google.com/d/optout.

[shaju c]

Hi All,

 I did a bit of investigation further and here is the word done so far. I was able to query CSV file to get row count and column count which is typical in a ETL project. I had to use rowFixture using H2 Jar file (h2-1.4.192).Here is the code. This gives me the rowcount and column count but I am unable to compare this with the database query output such that the difference between the two is zero.

I tried to use SYMBOLS which are global variables in FIT but can't take them to Table Fixture for comparison.

import fit.ColumnFixture;

import java.sql.*;

import org.h2.tools.Csv;

public class QueryCSV extends ColumnFixture  {

public String fileName; 

private int t;

private int NoOfRows=0;

public int columnCount(){

try{

String file = fileName;

String Path = "//path/"+file+".csv";

ResultSet rs = new Csv().read(Path, null, null);

ResultSetMetaData meta = rs.getMetaData();

t = meta.getColumnCount();

 

 

while (rs.next()) {

for (int i = 0; i < meta.getColumnCount(); i++) {

NoOfRows = rs.getRow();  

}

}

 

return t;  

}catch(Exception e){

System.out.println(e);

return 0;

}

}

 

public int numberOfRows(){

return NoOfRows;

}

} 

[Yavor Nikolov]

Thanks for sharing your progress so far. You may try to refer to a SYMBOL via "<<symbolname" in a DbFit Query table. (DbFit parameters are currently implemented as FitNesse symbols).

To unsubscribe from this group and stop receiving emails from it, send an email to dbfit+unsubscribe@googlegroups.com.

[shaju c]

Hi,

How can i compare row fixture value to a query fixture. Iam able to assign CSV file output to a variable and Table output to a variable but can't find how to compare them. Screen shot attached.

Appreciate your help.

Thanks

[Yavor Nikolov]

Assuming the variable name is Stagerowcount:

|!Query|select count(*) as cnt from ....|
|cnt|

|<<Stagerowcount|

To unsubscribe from this group and stop receiving emails from it, send an email to dbfit+unsubscribe@googlegroups.com.

[shaju c]

Works perfect now! Thanks much.

[Bala G]

Hi,

 I was trying to test the same scenario as described here. I wrote a similar fixture & am able to get the row count & column count out of the CSV file.However, when i try compare the compare the rowcount of Table to the value stored in a variable from previous test table, the value in variable is not getting decoded in the next table. FIn screenshot below :

srcFileRowCount is stored in 1st test table & is decodable in next row is same table. However, the same variable is not decodable in next table.

Can you please help me here

[Yavor Nikolov]

Looks like srcFileRowCount symbol is not defined for some reason.

To unsubscribe from this group and stop receiving emails from it, send an email to dbfit+unsubscribe@googlegroups.com.

[Mark Matten]

What hapoens with the Fit test runner instead of the FitLibrary one?

[shaju c]

Hi,

What is your actual target row count? May be it is 15 and so it displays as 15 surplus.

Thanks

[Bala G]

Actual count is 15 but expected count stored in srcFileRowCount is 258011. Is it correct that it simply highlights <<srcFileRowCOunt is red & displays missing beside it. My question is shouldn't it display the actual count of <<srcFileRowCount  & then display missing beside it ?

something like below :

<<srcFileRowCount 258011 Missing
15 surplus

[Bala G]

HI Mark,

 Fit test runner does the work for me. Thanks for your help

[shaju c]

Hi,

Can you please help me understand the difference between FitLibrary and Fit Test runner. 

How to change to FIT Test runner please.

Thanks

[Bala G]

 Hi,

All i know is that adding below line at top of the tets page makes the test page run using fitlibrary.
 !define TEST_RUNNER {fitlibrary.suite.FitLibraryServer}

Regards,
Balaji Konar

[shaju c]

Hi,

Apologies for the delay in reply. I get following error when i include !define TEST_RUNNER {fitlibrary.suite.FitLibraryServer}

Looks like some jar files are not available? but i am not sure which files. Please help

Exception in thread "main" java.lang.NoClassDefFoundError: fitnesse/components/CommandLine
	at fit.FitServerBridge.args(FitServerBridge.java:106)
	at fit.FitServerBridge.run(FitServerBridge.java:60)
	at fitlibrary.suite.FitLibraryServer.main(FitLibraryServer.java:36)
Caused by: java.lang.ClassNotFoundException: fitnesse.components.CommandLine
	at java.net.URLClassLoader.findClass(Unknown Source)
	at java.lang.ClassLoader.loadClass(Unknown Source)
	at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
	at java.lang.ClassLoader.loadClass(Unknown Source)
	... 3 more

[Yavor Nikolov]

Why are you setting TEST_RUNNER to fitlibrary.suite.FitLibraryServer? Could you try switching to Fit runner as advised in a previous post here? (FIT is the default if you don't set runner, or you may set it explicitly to fit.FitServer http://www.fitnesse.org/FitNesse.UserGuide.QuickReferenceGuide)

In general fitnesse.components.CommandLine is not part of dbfit but rather an additional fixture specific for you (you're somehow referring to it). If you need additional fixtures - you should make sure FitNesse can find them (typically by copying their .jar files under dbfit lib directory and make sure you have "!path lib/*.jar" directive in your tests)

--

[shaju c]

Got it! Thanks I wanted to know the purpose of fitnesse.components.CommandLine.

To unsubscribe from this group and stop receiving emails from it, send an email to dbfit+un...@googlegroups.com.