vividus icon indicating copy to clipboard operation
vividus copied to clipboard
verified publisher icon vividus-framework

Extend ability to work with loaded data from UI in .CSV file like with table in DB

Open KimKupreichyk opened this issue 4 years ago • 2 comments

It would be great to have possibility of working with loaded file data from UI (.CSV format) like with table in DB:

Used steps:

  1. Go to UI part for file generation.
  2. When I set browser cookies to the API context
  3. When I wait until HTTP GET request with URL pattern ... exists in proxy log
  4. When I capture HTTP GET request with URL pattern ... and save URL to story variable urlReport
  5. When I issue a HTTP GET request for a resource with the URL '${urlReport}'
  6. When I save response body to the STORY variable 'Response'

Expected result: add possibility to work with downloaded file like with Database via SQL query (with columns and parameters) instead of regular expressions for data capturing from loaded file.

Example of downloaded file: BM0612M (21).csv

Data when saving response body to the STORY variable: Saving a value 'Col_A,Col_B,Col_C,Col_D,Col_E,Col_F,Col_G,Col_H,Col_I,Col_J,Col_K,Col_L,Col_M 1,2,3,4,Taiwan,6,43.06,18731544.07,18731587.13,0,102021,, 1,2,3,4,Taiwan,6,2153,936584336,936586489,0,102021,, 1,2,3,4,Taiwan,6,418.02,71326200.17,71326618.19,0,102021,, 1,2,3,4,Taiwan,6,20901,3566330392,3566351293,0,102021,, 1,2,3,4,Taiwan,6,452.13,65783818.08,65784270.21,40133,102021,-0.21,65784270 1,2,3,4,Taiwan,6,3374.64,130895517.84,130898892.48,24065,102021,-0.48,130898892 1,2,3,4,Taiwan,6,0,1758071.04,1758071.04,78,102021,-0.04,1758071 1,2,3,4,Taiwan,6,0,51873742.92007,51873742.92007,2120,102021,0.07993,51873743 1,2,3,4,Taiwan,6,32153.64009,2668913.06042,2701066.70051,147,102021,0.29949,2701067 1,2,3,4,Taiwan,6,319107.52003,9031548.36001,9350655.88004,399,102021,0.11996,9350656 1,2,3,4,Taiwan,6,0,1594348.29944,1594348.29944,118,102021,-0.29944,1594348 1,2,3,4,Taiwan,6,0,8635495.85,8635495.85,18809,102021,0.15,8635496 1,2,3,4,Taiwan,6,0,-6892637,-6892637,1822,102021,0,-6892637 1,2,3,4,Taiwan,6,0,1764199.404,1764199.404,339,102021,-0.404,1764199 1,2,3,4,Taiwan,6,0,-7000,-7000,2,102021,0,-7000 1,2,3,4,Taiwan,6,0,-61985,-61985,116,102021,0,-61985 1,2,3,4,Taiwan,6,0,2873906.64,2873906.64,237,102021,0.36,2873907 1,2,3,4,Taiwan,6,0,-4286,-4286,33,102021,0,-4286 1,2,3,4,Taiwan,6,0,3283128,3283128,704,102021,0,3283128 1,2,3,4,Taiwan,6,0,72410,72410,462,102021,0,72410 1,2,3,4,Taiwan,6,0,84650,84650,48,102021,0,84650 1,2,3,4,Taiwan,6,0,-3283128,-3283128,704,102021,0,-3283128 1,2,3,4,Taiwan,6,0,-72410,-72410,462,102021,0,-72410 1,2,3,4,Taiwan,6,0,-84650,-84650,48,102021,0,-84650 1,2,3,4,Taiwan,6,0,-995653,-995653,1036,102021,0,-995653 1,2,3,4,Taiwan,6,0,4435239,4435239,869,102021,0,4435239 1,2,3,4,Taiwan,6,0,126876,126876,493,102021,0,126876 1,2,3,4,Taiwan,6,14779837,118843,14898680,2,102021,0,14898680 1,2,3,4,Taiwan,6,0,25134,25134,55,102021,0,25134 1,2,3,4,Taiwan,6,1600.6498,1885154.68025,1886755.33005,32710,102021,-0.33005,1886755 1,2,3,4,Taiwan,6,0,253391,253391,327,102021,0,253391 1,2,3,4,Taiwan,6,0.42008,393767.12525,393767.54533,0,102021,0.45467,393768 1,2,3,4,Taiwan,6,0,263226881,263226881,42192,102021,0,263226881 1,2,3,4,Taiwan,6,0,2867352,2867352,390,102021,0,2867352 1,2,3,4,Taiwan,6,0,8461890,8461890,7530,102021,0,8461890 1,2,3,4,Taiwan,6,15136526,5934,15142460,2,102021,0,15142460 1,2,3,4,Taiwan,6,0,1594348.29944,1594348.29944,172,102021,-0.29944,1594348 ' into the story variable 'Response'

Example of sql query for data from example below: When I execute SQL query select Col_M from ${table} where Col_A=1 and Col_E='Taiwan' and Col_G=452.13 and Col_J=40144 against ${dbKey} and save result to STORY variable dbCol_M Result: Col_M=65942396

KimKupreichyk avatar Jan 20 '22 09:01 KimKupreichyk

Hi @KimKupreichyk, please follow the next steps:

  1. Add support of in-memory database as described here https://docs.vividus.dev/vividus/latest/user-guides/use-in-memory-db-to-manage-test-data.html
  2. Properties snipper
db.connection.testh2db.driver-class-name=org.h2.Driver
db.connection.testh2db.url=jdbc:h2:mem:test;\
  DB_CLOSE_DELAY=-1;
  1. Scenario snippet
When I create temporary file with name `$name` and content `
id,name,age
1,hello,13
2,world,14
3,today,26
` and put path to SCENARIO variable `csvPath`
When I execute SQL query `CREATE TABLE parampampam AS SELECT * FROM CSVREAD('${csvPath}');` against `testh2db`
When I execute SQL query `SELECT * FROM parampampam;` against `testh2db` and save result to scenario variable `dataSet`

uarlouski avatar Jan 21 '22 07:01 uarlouski

@KimKupreichyk the guide create by @uarlouski has been added to the official documentation: https://docs.vividus.dev/vividus/latest/user-guides/execute-sql-against-csv.html#_execute_sql_against_in_memory_csv

valfirst avatar Jan 21 '22 13:01 valfirst