Share My Creation Csv to SQLite program

Attached you find a small project to convert csv files to a SQLite database.
The program:
- detects automatically between the two separator characters ',' ';'.
- determines, on request, the data type for each column (TEXT, REAL, INREGER), these can be changed.
- the column names are the values of the first line in the csv file.
- three test csv files are included.
- by default, the Database generic file name is the same as the csv file name.
- by default, the Table name is also the same as the csv file name, but empty characters are automatically removed and "-" (minus) characters are replaced by "_" (underscore).

EDIT: 2023.04.24 Version 1.6
Added reading csv files without a header, adds Col0, Col1, Col2 etc as column names.
Column names can de edited.

EDIT: 2023.04.20 Version 1.5
Improved the detection of the separator character.

EDIT: 2022.11.01 Version 1.4
Removed invalid characters in Table names.
Added: Number of rows / Time to load csv / Time to convert.
The CsvToSQLiteExe file is still the old file, a new file with Java 11 is too big.
I left also the previous version zip file.

EDIT: 2021.09.18
Added the use of SQLite transaction, suggested by Diceman in post #6.

EDIT: 2021.01.29
Added an exe file:
CsvToSQLiteExe.zip
You need to unzip it to get the exe file.
Unfortunately the exe file is not accepted by my McAfee anti virus program it is put in quarantaine.
I must manually exclude from the analysis.

 

[klaus]

assumes that the text file to convert always has a first line as header row. Is that correct or no?

Yes, correct.
I will have a look at it.

 

[Mahares]

Yes, correct.

I have made a few changes to your B4J code to allow for an import of a text file without a header and allows you to edit the temporary column names Col1, Col2, etc. to rename them to more descriptive text. I can post the project if you like and you can fine tune it, modify it and enhance it per your expertise.

 

[klaus]

I have also added the possibility to read a csv file without a header.
It sets Col0, Col1, etc default column names, which are editable.
I will post it this afternoon.
Thank you for the proposal.

 

[klaus]

The CsvToSQLite program has been updated to versio 1.6.
Added reading csv files without a header, adds Col0, Col1, Col2 etc as column names.
Column names can de edited.

 

[Mahares]

The CsvToSQLite program has been updated to versio 1.6.

I used a similar approach to yours, but instead of using a ScrollPane I used an xClv to edit the column names. Your overall approach is much superior to mine.
There is only one minor thing I added. Sometimes, you are importing data from a CSV file that starts with a number. However, since SQLite table names cannot start with a number unless you enclose it in brackets, I added this code to btnLoadCSV_Click sub to prevent a crash..

DBTableName = DBTableName.Replace("-", "_") 'klaus  
    If IsNumber(DBTableName.SubString2(0,1)) Then  'mahares
        DBTableName = $"[${DBTableName}]"$   'mahares
    End If   'mahares  
    DBFileName = DBFileName & ".db"  'klaus

 

[Mahares]

The CsvToSQLite program has been updated to versio 1.6.

I have modified your project version 1.6 to allow creation of multiple tables in the same database . Also making sure it does not crash if tablename starts with a number. Do you want me to post the revised project here, or do you want me to create a whole new thread. What suits you is fine with me, because most of the code is yours. In my opinion, your example should have been in another forum to get more exposure, as a small number of members bother going through the creation forum.

 

[klaus]

Sorry for answering only today, these days i am not much on B4X.
I had already added your code from post #25 but not yet published.

but instead of using a ScrollPane I used an xClv to edit the column names

I use a simple ScrollPane for both the column titles and the ComboBoxes for the column types.
I replaced the Labels by TextFields.
For such simple layouts, i prefer to use the basic objects.
By the way, xCustomListView is based on a ScrollPane in B4J.
Post your project here and i will look at it and see if i merge both into one, with credits to you.

 

[Mahares]

I use a simple ScrollPane for both the column titles and the ComboBoxes for the column types.

Actually the one I modified is your project version 1.6 (the latest you posted) which still uses the ScrollPane. I modified it by only adding a feature allow multiple tables in a single database. I still use the scrollPanel and whatever other simple things you have there. The one I intend to post does not use xClv. It uses SCrollPane.

 

[klaus]

My comment about the ScrollPane was just to clarify why i use it instead of xCustomListView.
My last comment was meant for the feature to create multiple tables in a same database.

 

[Mahares]

create multiple tables in a same database.

Here is klause B4J project version 1.6 that I modified to allow multiple tables in a single database. I did not change much more, because I hardly use B4J. Most of my programming is in B4A. I tested it extensively and seems to work for me. But, additional testing by other members is highly recommended. Of course you can alter it any way you want. Most of the programming was done by klaus. I just extended it a little more.

 

[klaus]

I will look deeper into your code and update the original one.

 

[Mahares]

will look deeper into your code and update the original one.

Your original project is great the way it is. It is up to you whether you want to merge them or leave them the way they are. I only took the challenge of extending it with multiple table creation because I saw a member 's post here requesting it.The purpose was not to outdo yours.