Android Question Question on csv.

[Anastasios Michaelides]

Hello to all

I want to build an application for pricing products depend on product dimensions(width, height).
So I will have on product page 2 fields one for each dimension, the user will put the dimensions and the app must take the exact price form a csv, or table and presented to the user.
Can you please guide me in witch way is this possible?

Thank you in advance

 

[Mahares]

You are better served if you create and use a SQLite database. Here is a small example that creates a table after you have created and initialized the database and displays the price and product from the table.

txt="CREATE TABLE IF NOT EXISTS  tblSpecs (PRODUCT TEXT PRIMARY KEY, WIDTH REAL, HEIGHT REAL, PRICE REAL)"
    SQL1.ExecNonQuery(txt)  'Create tabl
    txt="INSERT INTO tblSpecs  VALUES(?,?,?,?)"
    SQL1.ExecNonQuery2(txt, Array As String("Window",64,48,456.45))
    SQL1.ExecNonQuery2(txt, Array As String("Front Door",36,72,2134.14))   
    SQL1.ExecNonQuery2(txt, Array As String("Kitchen Door",32,72,1194.75))
   
    txt="SELECT PRODUCT, PRICE FROM tblSpecs WHERE WIDTH =? AND HEIGHT=?"
    Cursor1=SQL1.ExecQuery2(txt,Array As String(36,72))

    For i=0 To Cursor1.RowCount-1
        Cursor1.Position=i
        Log(Cursor1.GetString("PRODUCT") & "  " & Cursor1.GetDouble("PRICE") )  'displays 2134.14
    Next

 

[eurojam]

If you are not familiar with databases - which is a good solution like Mahares pointed out - you can use B4A collection structures like a map in combination with our own type:

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    'These variables can only be accessed from this module.
Type Specs ( PRODUCT As String, WIDTH As Double, HEIGHT As Double, PRICE As Double)
Private SpecsMap As Map
End Sub

Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
    'Activity.LoadLayout("Layout1")
    SpecsMap.Initialize
    Dim s As Specs
    s.PRODUCT="Window"
    s.HEIGHT=64
    s.WIDTH=55
    s.PRICE=566
    SpecsMap.Put(s.PRODUCT,s)
   
End Sub

to store the map you can use the keyValueStore Class which is based on a sqlite database, but you don't need to know sql.
->https://www.b4x.com/android/forum/t...ple-and-efficient-key-value-data-store.26317/

 

[Anastasios Michaelides]

Hello to all, thank you for your help Mahares and eurojam.
I will try your solutions but I want to ask you something, my pricelists for every product includes
about 500+ prices per dimension, is there a way to import that prices in the application from a csv to
the database?

Thank you in advance

 

[Mahares]

You can put your data in an Excel spreadsheet and then convert to a csv file. Then, you can import the data from the text file into a SQLite table. See attached text file as a sample of your data. If you still need help, someone will come to your rescue.

 

[Anastasios Michaelides]

I am asking a lot if I ask you for an example code with 2 fields (width, height) and the the csv file(dimensions.txt).
Just to guide me because I don't have any experience on sql databases .

Thank you in advance

 

[eurojam]

you can use SQLite Manager for example, which is a free AddOn for Firefox (https://addons.mozilla.org/de/firefox/addon/sqlite-manager/) there is an Importtool to import csv to a sqlite Database.

 

[Anastasios Michaelides]

I am lost can I have an example code please...

 

[imbault]

send your your CSV file, i will setup a tiny example

 

[Anastasios Michaelides]

Thank you very much for your effort you are life saver.

 

[imbault]

Ok, but give some info, here are the 2 first lines of your files. (I'm not a magician)

,700,750,800,850,900,950,1000,1050,1100,1150,1200,1250,1300,1350,1400,1450,1500,1550,1600,1650,1700,1750,1800,1850,1900
500,180,183,186,189,192,195,198,200,203,206,209,212,215,218,221,224,227,230,233,235,238,241,244,247,250

 

[Anastasios Michaelides]

Sorry I missed that, the first horizontal line is the width and the first vertical line is the height of the window, all the other are the prices.

Thank you

 

[Mahares]

Here is attached a full functioning project that allows you to:
1. Create a SQLite database.
2. Create a table with 3 columns: Height, Width, Price
3. Parse and import the text file that has the data into the table. The text file was added to the assets via file manager as dimensions.txt and then copied to the writable part of the device.
4. Open the activity, enter a height and a width, then click RUN to display the corresponding price.

 

[imbault]

Thanks @Mahares for @Anastasios Michaelides, just a crazy day, I was supposed to do it

Regards

 

[Anastasios Michaelides]

Mahares you are legend....thank you very much for your help, I will try it the soonest and I will get back to you.

A very nice night to all!

 

[Anastasios Michaelides]

Hello to all, the example working very good(thanks once again) when the user actually enders a actual value from the csv(example: 1000 witdh and 1000 height).
But when the user enders another value like between the cel values 1000-1050, the value 1025 the price is not presented.
Is there a way when the user puts a width or height between the cell values(example 1005-1010, 1025) the program to display the closest price to the dimension?

Thank you in advance

 

[DonManfred]

Define a sql query which can find entries between a value....

 

[Anastasios Michaelides]

thanks for the reply, I will do that from sglite manager?

 

[Anastasios Michaelides]

I am sorry but it is possible to have an example because as I said before I am not familiar with sql and I have not find anything similar to my problem in to forum.
I know that I am asking a lot but I can not find a solution on my own.

Thank you in advance

 

[Mahares]

If you are using the project I posted, replace this line in Sub btnGo_Click:

txt="SELECT HEIGHT, WIDTH, PRICE FROM " & DBTableName & " WHERE HEIGHT =? AND WIDTH=?"

with this one:

txt="SELECT HEIGHT, WIDTH, PRICE FROM " & DBTableName & " WHERE HEIGHT= (SELECT HEIGHT FROM " & DBTableName _
    & " WHERE HEIGHT >=? LIMIT 1) AND WIDTH = (SELECT WIDTH FROM " & DBTableName & " WHERE WIDTH >=? LIMIT 1)"

Here is what it will do: If you have a match for the height and width, it will display the correct price. If the height and width are between data points in the table, the price displayed will be the next higher price on the table. For instance, if you enter a height of 1025 and a width of 1365, you will get a price of 281, which is the closet higher price where H=1050 and W=1400.