Android Question [Solved] Converting csv file to db file

[Shelby]

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)

 

[klaus]

Here you are, attached the corrected csv file and the converted db file
The problem in your csv file are blank characters in the csv file name which is admitted as the as the SQLite Table name and blank characters in the column names.
I removed the blank characters and it worked.
I will look if I can do something to avoid this.

 

[Shelby]

Oh, I'll keep that in mind, using underscores in the future.
Thanks again

 

[Mahares]

Oh, I'll keep that in mind, using underscores in the future.

We are all bending over backwards to help you, Shelby. Now that klaus fixed you text file and was able to use B4J conversion routine, if you want to check out how it can be imported into a table with DBBrowser, here is the procedure:
1. Open a new database or an existing database in DBBrowser ( your version of DBBrowser is fine).
2. Click 'File'
3. Click 'Import'
4. Click 'Table from CSV'. Look for the csv text file that klaus converted for you on your PC (remember where you saved it on your drive), Click the file when you find it. Then click 'Open' to open the file on the screen. You are then shown a screen with a table name and few other things. You can choose the default table name or change the name of the table.
5. Finally, click 'Ok'. The table is created for you in the database you opened or the database you newly created. It was that simple. It has the same data and structure as the one created via B4J conversion. The reason you had trouble converting the text file in post #18 is because of all those spaces in the column names that klaus pointed out

 

[Shelby]

Each time I use either the Klaus csv converter or the DB Browser, build the new db file, place the new db file file into the files manager with the add files link, the db file seems to make my app display the old db file's number of rows. The old db file gave me 148 rows. My new db file shows 220 rows in the DB Browser and the cvs to sqlite program. There must be something to disallow the new db file to be used. I run the DB Browser or converter with a new name for the db. Then after I remove the db from the IDE, I change the name of the new db file to the old name which shows in the code but once again I only get the 148 records. I thought I wouldn't have to change the name of the db file called in the code but I guess I'll now try to use the new db name from the converters. I'll change the 6 instances of the db file name in the code.
Thanks again for all of you who have participated in trying to get my mind right. I'll report after changing the file name in the code with the handy find/replace option in the edit tab options.
Nope, it didn't work; I changed the code back.

 

[Shelby]

I just found a major discrepancy. When I change the name of the new file to the old name in my project folder, the IDE files manager shows only the old smaller database file to be added. I'll now close the IDE and reboot it (so to speak). I even dragged the new 68kb db file into the file manager panel, but still, my app only gives the old 148 records when I install and open it using the bridge.
I don't know how the files manager pathway is not using the larger db file.

 

[klaus]

Please explain exactly what you have done?
Where and how did you save the new db file?
Without knowing what you have done and how, it is impossible to help you.
It would be much more efficient if you posted a small project showing the problem, so we can see what exactly you have done and give concrete advices !

 

[klaus]

How did you zip your project ?
You zipped only the SS2021.b4a file and not the project !
You MUST use !

 

[Shelby]

Sorry, I didn't understand about using the file dropdown.
Thanks for that.

 

[klaus]

I tested your project.
I get an error because you are trying to access a table which does not exist !!!
I changed this line
tblItems.LoadSQLiteDB(Starter.sql,"SELECT DocNo as Doc, Description, TableNo as ...
to this to see what happens.
tblItems.LoadSQLiteDB(Starter.sql,"SELECT * FROM TableList221",True)
And guess what happend, it works !!!

 

[Shelby]

Wow! I like people who experiment. Thanks
Shelb
Nope, it won't compile. First I got a compiling error:

B4A Version: 10.50
Parsing code. (0.15s)
Java Version: 8
Building folders structure. Error
The process cannot access the file 'C:\Projects\Android3\SS2021\Objects\shell\bin\classes' because it is being used by another process.

Then after closing everything, it won't bring up the app on the phone.
My phone writes: waiting for IDE debugger to connect. Numerous times after closing down all.
I'll press the clean project button and also try compiling with release mode.
Nope: no success
I'll change the lines back from your suggestion where at least the phone displays the old 148 records app.
Yes: it works again with the 148 rows

 

[klaus]

I am sure that you have an old file on your phone!
Put a breakpoint in the Starter module in the line shown below.

When the program stops at that line and you go one step further, what happens ?
Does the program jump to the next line File.Copy(File.DirAssets,. . .
Or does it jump directly to End If ?
If the program jumps to End If, that means that you already have a file and the new one is not copied.
Add this line
File.Delete(Shared & "/Dbs/", "dbirc.db")
before If File.Exists(. . .
to delete the existing file.
Then, before the next run, comment this line to avoid deleting the new file.

 

[Shelby]

Yes the breakpoint drops to line 22
Sorry, the reaction varies
When I run it again it compiles and phone displays 148 app
Line 211 becomes highlighted yellow and breakpoint has a yellow arrow within the red ball

OK now adding line above you suggested.

 

[Shelby]

I ran the compiler after closing and restarting project. Then with bridge activated I click install, then open, but the phone image says waiting for debugging etc. then I get a small box saying
IRC Example isn't responding
x Close app
O Wait
I click wait

In the Starter code that breakpoint ball still has the arrow in it and the line is highlighted yellow
In a few minutes I'll try leaving the changes you gave me but I'll remove the breakpoint; I'll check here before I do that.
ok trying that now

 

[Shelby]

When removing the breakpoint I get the same results as above; compiling fine, phone app installed ok, on open: waiting for debugger to bugger....

 

[klaus]

With this line
File.Delete(Shared & "/Dbs/", "dbirc.db")
set a breakpoint at this line and go step by step.
Does the program execute this line File.Copy(File.DirAssets,. . . ?

 

[Shelby]

I don't know how to determine if that line (23) executes. The code disappears for 10 seconds or so during compilation. Same results with phone where it stops after I click open: waiting for compiler....
When I click the restart circular arrow the compiler lights up the File.delete......line and highlights it yellow not sure if it stops or continues after that I'll view it a few more times and maybe try to have the logs panel open

 

[Shelby]

For some reason, the logs give this:

Error occurred on line: 1989 (Table)

android.database.sqlite.SQLiteException: no such table: tblIRC (code 1 SQLITE_ERROR[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.acquirePreparedStatement(SQLiteConnection.java:1372)

Logging out now; I teach class tonight.
Thanks for your persistent help

 

[klaus]

The error you get is again exactly what I told you in post #49 !!!

I think that you need to learn how to debug with breakpoints.
You should read these chapters:
4.22 Breakpoints
7 Debugging B4A, B4i, B4J
in the B4X IDE Booklet.

When you set a breakpoint and run the program, it stops at the line where the breakpoint is set.

Then you can execute the program step by step with this button

:

The yellow line is the line where the program is waiting.

 

[Shelby]

OK Thanks,
I'll read that tutorial this morning. It's 6 am here. I may have read it already, but back when I read most of the tutorials I had much less experience and my scanning didn't do much for my understanding, just exposure to the overall structure.

 

[klaus]

Here it's 3 PM !