Android Question [Solved] Converting csv file to db file

Shelby

Well-Known Member
Licensed User
Hi All,
I've wasted lot of time trying to find a workable tool to covert a larger than the original database edited Excel worksheet csv file into a db file for my SQLite project. I've tried Klaus's tool at https://www.b4x.com/android/forum/threads/csv-to-sqlite-program.87061/ but it's too advanced for me to use. I've tried the fishcode tool suggested by Rboeck at https://fishcodelib.com/DBMigration.htm and I didn't succeed there either. I've tried the google solution from sqlitetutorial.net. I simply want to convert my edited Excel csv file>.db by renaming the existing db file and replacing it in my project files folder. What can I do? Thanks
P.S. my project compiles fine and here it is.

Help me Obie One Klausnobie (Star Warrior)
 

Attachments

  • SS2021 - Copy.zip
    1.8 KB · Views: 245
Last edited:

Shelby

Well-Known Member
Licensed User
The running with the breakpoint didn't seem to give any new problems as I slowly ran the various lines. I was watching the left pane as the tutorial showed. Should I try the breakpoint on a different module besides the starter module? I don't know exactly when it happened, but yesterday after either adding a line you suggested or doing the breakpoint exercise my app will no longer display after I click the open icon/link on the phone via B4A bridge. I wonder if one uses an autobackup at this time?
 
Last edited:
Upvote 0

Shelby

Well-Known Member
Licensed User
Reading again post #51, I just ran the breakpoint again on the now line22 If File.exists(Shared......;
The debugging stopped at the next line as you asked, rather than skipping to End if
Also during that process, the app won't display after I click open. I have now commented out the line of File.Delete.... but I ran it many times without the commenting out of that line.
 
Last edited:
Upvote 0

Shelby

Well-Known Member
Licensed User
Everything seems to work except for the phone to display the app table. When I click install and then click open on the progression of the phone, the app jumps back to the B4A bridge display. In a few minutes, I'll try the emulator.
 
Upvote 0

Shelby

Well-Known Member
Licensed User
Running my emulator, the logs give error:

Error occurred on line: 1989 (Table)

android.database.sqlite.SQLiteException: no such table: tblIRC (code 1): , while compiling: SELECT DocNo as Doc, Description, TableNo as
, IRCPage As [IRC Page], PDFPage As [PDF Page] FROM tblIRC

at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)

Line 1989: I'll use the link here to show the sub with line 1989...
B4X:
Public Sub LoadSQLiteDB(SQLite As SQL, Query As String, AutomaticWidths As Boolean)
    Dim Curs As Cursor
    (This is line 1989:) Curs = SQLite.ExecQuery(Query)
   
    cAutomaticWidths = AutomaticWidths
    mNumberOfColumns = Curs.ColumnCount
    innerClearAll(mNumberOfColumns)
   
    Dim Headers(mNumberOfColumns) As String
    Dim ColumnWidths(mNumberOfColumns) As Int
    Dim HeaderWidths(mNumberOfColumns) As Int
    Dim DataWidths(mNumberOfColumns) As Int
    Dim cColumnDataType(mNumberOfColumns) As String
    Dim col, row As Int
    Dim str As String
    For col = 0 To mNumberOfColumns - 1
        cColumnDataType(col) = "TEXT"
        Headers(col) = Curs.GetColumnName(col)
        If AutomaticWidths = False Then
            ColumnWidths(col) = 130dip
            HeaderWidths(col) = 130dip
            DataWidths(col) = 130dip
        Else
            HeaderWidths(col) = cvs.MeasureStringWidth(Headers(col), Typeface.DEFAULT, cTextSize) + ExtraWidth
            DataWidths(col) = 0
            For row = 0 To Curs.RowCount - 1
                Curs.Position = row
                str = Curs.GetString2(col)
                If str <> Null Then
                    DataWidths(col) = Max(DataWidths(col), cvs.MeasureStringWidth(str, Typeface.DEFAULT, cTextSize) + ExtraWidth)
                End If
            Next
            ColumnWidths(col) = Max(HeaderWidths(col), DataWidths(col))
        End If
    Next
    SetHeader(Headers)
    SetColumnsWidths(ColumnWidths)
   
    For row = 0 To Curs.RowCount - 1
        Dim R(mNumberOfColumns), str As String
        For col = 0 To mNumberOfColumns - 1
            Curs.Position = row
            str = Curs.GetString2(col)
            If str <> Null Then
                R(col) = str
            Else
                R(col) = ""
            End If
        Next
        AddRow(R)
    Next
   
    Curs.Close
   
'    Log(Data.Size)
End Sub
 
Upvote 0

Shelby

Well-Known Member
Licensed User
I don't know the content of my database. Where do I find the name of this illusive table? Is it in my files folder in the Project folder? I guess I change the code where it has tblIRC to that new name. Remember I named the new db file the same name as the original db file. I ignorantly thought this db file did all that I need.
Thanks
 
Upvote 0

Shelby

Well-Known Member
Licensed User
OK, I'll try to see if the newest name (maybe tablelist221 post #49) is the one to use. Thanks again
I'll be gone for 4 to 8 hours, leaving in 30 minutes or so.
 
Last edited:
Upvote 0

klaus

Expert
Licensed User
Longtime User
I don't know the content of my database.
Where did you get it from ?
If you don't know its content what do you want to do with it?

To look at unknown databases I use the SQLite2009Pro browser.
There are for sure others on the internet, I have been using the SQLite2009Pro browser for some years now.
 
Upvote 0

Shelby

Well-Known Member
Licensed User
Great, I'll try the one you use; that is if I can't locate the name without it. My construction project was cancelled for today.
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
My construction project was cancelled for today
Shelby: I looked at the project you attached in post #48 and made all the necessary corrections for you. Unzip it and extract the project and run it . It will work.
The problems were many:
1.You were not using the correct table name.
2. You were not using the correct column names
3. You were assigning 5 columns to the flexible table when it should have been four.
Please test it. It works for me now after I made the corrections. Of course, if it works for you, you can clean up all the comments I have. You are lucky, you have had klaus helping you all along. He is in good mood today because his vaccine shot probably went well yesterday. Realistically, a similar thread should not have more than 5 to 8 posts for it to be solved already. Good luck
 

Attachments

  • ShelbyConstructionProject.zip
    100.4 KB · Views: 157
Upvote 0

Shelby

Well-Known Member
Licensed User
What? I can't believe your generous attitude. Thank you. I just installed the SQLite2009Pro Browser that Klaus recommended and was getting ready to see how to use it.
You are one of my heroes here.

When I used an incognito tab (Chrome browser) I was able to save the zip file and am extracting now.

Thanks again

Unfortunately, it won't display on my phone.

I'll try to find the new name of an unknown database as Klaus pointed out to me.
 
Last edited:
Upvote 0

Mahares

Expert
Licensed User
Longtime User
it won't display on my phone.
I assume you downloaded the file to your PC and then extracted the project as it is done with all the projects you download from the forum. Once, you extracted the project and opened it via B4A IDE, just launch it to your phone or tablet. Make sure your phone is connected to the IDE via B4A Bridge or USB cable. The first page will have data like this screenshot here.
Attached a screenshot of the first page when the project runs. Don't run it on an emulator. Use a nice reputable phone or tablet.
The project I attached for you in post #71 works. You can take it to the bank.
 

Attachments

  • FirstPageDisplay.png
    FirstPageDisplay.png
    29 KB · Views: 141
Last edited:
Upvote 0

Shelby

Well-Known Member
Licensed User
Yes, I performed all those steps as you pointed out. I have not tried my emulator.
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
Yes, I performed all those steps as you pointed out
If you are able to compile other projects you have on your computer, you should be able to do the same thing here. It is the same process. Why don't you tell us what type of phone and also include a screenshot of what the screen on your device looks like when you compile the program and run. All I can tell you is that I ran it on 2 separate tablet and both worked.
 
Last edited:
Upvote 0

Shelby

Well-Known Member
Licensed User
Oh, I'll try another project with one of my other two apps on the computer; thanks for the idea.
Klaus mentioned about my phone having an old file it's using, but I'm confused half the time as some much new information is coming in.
The phone's a Samsung S9. It only recently won't display the table. I'll work on the images you suggested. What city do you live in? I'm in San Diego, CA
The display pops off and has no image after the click of the open button. My display goes back to the B4A display window.
 
Last edited:
Upvote 0

Shelby

Well-Known Member
Licensed User
Two other apps compile properly and display fine on the S9.

Using the project you sent earlier, I still am wondering about the table name on line 66 of the Main module. When I substitute other database names for the TableList221, they don't help. I think I see some other database names in the SQLite3 Management Studio Browser I downloaded and installed, but I can't understand where the IDE looks for those databases.
Here's the line I'm referring to.

B4X:
tblItems.LoadSQLiteDB(Starter.sql,"SELECT DescriptionSubjectLookup, TableNumber as [Table], PageIRC As [IRC Page], PgPDF As [PDF Page] FROM TableList221",True) 'WHERE BRKRNO = '" & Starter.gWhNo & "'" WHERE WHNO = '" & Starter.gWhNo & "'",True)
 
Last edited:
Upvote 0

Shelby

Well-Known Member
Licensed User
Here's a short video of my phone displaying my project. I couldn't attach the video so I zipped it. I now see it's horribly focussed so let me know if you want me to try another clearer one. It's taken by my wife's iPhone, emailed to my computer, and saved there.
 

Attachments

  • IMG_3127.zip
    431 KB · Views: 138
Upvote 0

Mahares

Expert
Licensed User
Longtime User
Here's the line I'm referring to.
No, it is not a good video. Don't' worry about it. I think you are using a different database and program from what I sent you
I can clean the project again by removing all the extra commented lines and export it again if you want. Let me know
 
Upvote 0

Shelby

Well-Known Member
Licensed User
It's true that I used my project instead of yours but the result is the same. Maybe later I'll shoot another video with yours.
 
Upvote 0
Top