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)

 

[mangojack]

Just to clarify ... you have a Excell CSV file that you want to convert to SQL DataBase file for inclusion into your 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 have just tried this tool ... It could not be any more simpler ... (apart from you may have to install B4J if not already installed)

1. Click on "Load CSV" button , locate your Excell CSV file. (On your PC !)
2. Click on "Convert" button.... the DB file will be created for you , in the same folder as your CSV file.
3. Copy / Include this db file to your B4A project ( Files Tab > Add Files)

(The CSV file can be edited to ensure the First Row contains you DB Column Names ,Data Typw etc.)

Should you need to further edit your Database before adding it to your project , this is a handy tool

DB Browser for SQLite

sqlitebrowser.org

PS: I could not run your project , Starter service and more missing etc .. File > Export as Zip.

 

[Shelby]

Great, thanks Jack. I just got out of class and will try those steps in the morning. California time here now 9 pm.
Yes you were exactly right on your analysis.

 

[Shelby]

There is a problem, I had installed B4J but my project is written in B4A. When I bring up my empty B4J IDE, I can't load my project; I guess it's because it's written in B4A. Perhaps I can bring up a sample B4J project from a forum member's displayed project. I'll wait to see if anyone suggests something else but perhaps find a sample in some other thread here in the forum.
Thanks again Jack

 

[hatzisn]

Hi @Shelby, in B4J you will load the B4J program. The fact is that you do not need to do so. DB Browser for SQLite already supports import from CSV. Just download it and go in File > Import > Table from CSV.

 

[klaus]

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.

How did you try it?
What do you find too advanced ?
I added a standalone exe file in the thread, link above.

 

[Shelby]

OK, Thanks to you both. I have the DB Bowser which I've never been able to utilize. Also, I'll look at the results of the link above from you Klaus. I don't remember what was too advanced since it was too advanced, but I'll try again, and report.

 

[mangojack]

OK, Thanks to you both. I have the DB Bowser which I've never been able to utilize.

@Shelby ... you do not need to run your B4A project with Klaus's Tool . It will Not work.

Klause's B4J project / Tool has 1 purpose. Converting your Excell CSV File to a SQL Database so you can then add it / include it, in your B$A Project.

Please re read my post #2

Also as suggested by @hatzisn the DB Broswer tool can also do this ( I was unaware)

 

[Shelby]

I got the DB Browser to build a new db file but so far my project IDE doesn't seem to be able to find the new db although I changed the name of the old db file and named the new file with the original name within my project folder. Now I'm trying to find where my code looks to locate my db file. My starter tab code shows it will look into the File.DirAssets area if File.Exists = False.

 

[Shelby]

Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.
    Public sql As SQL
    Dim DBFileDir = File.DirDefaultExternal As String
    Public DBFileName = "dbirc.db" As String
    Public Shared As String
    Public rp As RuntimePermissions
    
End Sub

Sub Service_Create
    'This is the program entry point.
    'This is a good place to load resources that are not specific to a single activity.
    Shared = rp.GetSafeDirDefaultExternal("")
    File.MakeDir(Shared,"/Dbs/")
    If File.Exists(Shared & "/Dbs/", "dbirc.db") = False Then
        File.Copy(File.DirAssets, "dbirc.db", Shared & "/Dbs/", "dbirc.db")
    End If
End Sub

Sub Service_Start (StartingIntent As Intent)
    Service.StopAutomaticForeground 'Starter service can start in the foreground state in some edge cases.
End Sub

Sub Service_Task#Region  Service Attributes
    #StartAtBoot: False
    #ExcludeFromLibrary: True
#End RegionRemoved
    'This event will be raised when the user removes the app from the recent apps list.
End Sub

'Return true to allow the OS default exceptions handler to handle the uncaught exception.
Sub Application_Error (Error As Exception, StackTrace As String) As Boolean
    Return True
End Sub

Sub Service_Destroy

End Sub

 

[Shelby]

How did you try it?
What do you find too advanced ?
I added a standalone exe file in the thread, link above.

I downloaded both your CsvToSQLite.zip files and have them in my B4.....folder. I forgot what discouraged me when I tried to use them so I'll attempt that again, now.
Voila! I can buy you a beer there in the tools>options area (green cross-browser). I guess it functions like the DB Browser.

 

[Mahares]

I got the DB Browser to build a new db file

Shelby, you are confusing yourself and everyone else. I don't know if you solved your problem or not, but here is the process:
Remove this line from your code: Dim DBFileDir = File.DirDefaultExternal As String
You need to add the db file: dbirc.db to the assets folder. In your B4A IDE on the right side pane. Click on the ‘Add Files’ button on the bottom of that pane. Then search for that db file in your PC where you created it with your DBBrowser and then add it to the assets that way.

 

[Shelby]

Oh yeah, I forgot the files window adding files. Thanks for that tip, Mahares.

 

[Shelby]

I'm getting the error code pointing to line 26 of my code above on post #10. It's only since the past changing of the db file and so on.
B4A Version: 10.50
Parsing code. Error
Error parsing program.
Error description: service_task#region is not a valid identifier.
Error occurred on line: 28 (Starter)
Sub Service_Task#Region Service Attributes

What I did to eliminate the error was to edit out that sub with the appostrophes (i.e. ')

 

[klaus]

I guess it functions like the DB Browser.

No, the CsvToSQLite only converts a csv file into a SQLite database.
The database is saved in the same folder as the csv file.
You cannot change any entry value, you can only select the column types if you want them different from the default types suggested in the program.

 

[Shelby]

Mahares, you gave me the key reminder to add files not only to my project folder but the add files panel in the Windows manager window. Thanks again.

 

[Shelby]

No, the CsvToSQLite only converts a csv file into a SQLite database.
The database is saved in the same folder as the csv file.
You cannot change any entry value, you can only select the column types if you want them different from the default types suggested in the program.

Oh, right, like MangoJack said.
[ Klause's B4J project / Tool has 1 purpose. Converting your Excell CSV File to a SQL Database so you can then add it / include it, in your B$A Project. ]

Thanks Klaus

 

[Shelby]

2. Click on "Convert" button.... the DB file will be created for you , in the same folder as your CSV file.

With my DB Browser (version 3.12.1) I can't find a Convert button. Last Friday went we had seemingly solved this dilemma of mine, I luckily found a db file and used it. Now after more editing of my csv file, I can import : File>import>table from csv file but when I click OK, I get error:

Error importing data.
Creating the table failed: attempt to write a readonly database(CREATE TABLE "table List IRC2 saved from CSV"............ Here's a printscreen image:

 

[klaus]

In mangojacks' post #2 the line
2. Click on "Convert" button.... the DB file will be created for you , in the same folder as your CSV file.
was referring to the CsvToSQLite program https://www.b4x.com/android/forum/threads/csv-to-sqlite-program.87061/

 

[klaus]

The Green Cross icon,.....B4a Object Browser.

Sorry, but I do not understand what you mean?
The program is a B4J program in the thread in the link in my previous post.