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.

 

[rboeck]

Last week i found a wonderfull program, which is able to connect and migrate all most used databases against another; its include Access, Excel, dBase, Foxpro, Firebird, SQL Server, Sqlite, PostgreSql, ODBC, OLEDB and much more.
Its a matter minutes to transfer an access table to an sqlite db and back. Also text files are supported. Even the free version is very usefull.
Link: http://fishcodelib.com/DBMigration.htm

 

[klaus]

Thank you for the link.
I am old fashioned, I like my own tools, I did not google to find something existing.

 

[LucaMs]

Thank you for the link.
I am old fashioned, I like my own tools, I did not google to find something existing.

And privacy? Reserved data?

 

[Deon Wilken]

This is briliant! Great work Klaus.

 

[Diceman]

Klaus,

Very nice utility.

As a suggestion (no rush) you might want to wrap the table inserts in a transaction (with Try/Catch block) because it will run 20x faster. I did that with your program and it is now fast as lightning. If it throws an exception you should report the record# and the record contents on the screen so the user knows which CSV rcd caused the problem. In my case I roll back the transaction. I suppose you could also have an option (checkbox) to continue with the inserts even if there are exceptions.

 

[klaus]

Hi Diceman.
I saw your post only today, do not know why I missed it.

I added your suggestion to use SQLite transaction. Yes the program is now very fast.

The project has been updated to version 1.2 in the first post.

 

[Mashiane]

Last week i found a wonderfull program, which is able to connect and migrate all most used databases against another; its include Access, Excel, dBase, Foxpro, Firebird, SQL Server, Sqlite, PostgreSql, ODBC, OLEDB and much more.
Its a matter minutes to transfer an access table to an sqlite db and back. Also text files are supported. Even the free version is very usefull.
Link: http://fishcodelib.com/DBMigration.htm

Their tools have been my favorite like forever...

 

[Mashiane]

This is inspiring, I can see myself creating a Web based version in BANanoVuetifyAD3. Awesome!

 

[Mashiane]

Browsing the web and found this: https://github.com/itsalb3rt/cloudcsv

 

[klaus]

The CsvToSQLite program has been updated to version 1.4.
Removes invalid characters in Table names.
Added: Number of rows / Time to load csv / Time to convert.

 

[Alexander Stolte]

Is there any way to save the umlauts?

 

[klaus]

The problem is that your original data is encoded with ASCII and not UTF-8.
If you load the person.csv file you see under Peter KRAUSE Kurfürstendamm, the 'ü' is displayed correctly.
And also french special characters like 'é'.
To load a csv file i use the LoadCSV2 routine from the StringUtils library which expects UTF-8 encoding.
When you save a csv file in Excel you should use the UTF-8 file option.

 

[Peter Meares]

Klaus
Not sure it is a bug really, more of an observation.
I was loading a CSV file which had a semi-colon ( in a text field a long way down the data list and this caused the program crash.
I traced this to not assigning headers as the semicolon was a long way down the rows of data.
By forcing the Loadcsv2 to use commas (,) it worked well.

Very useful program and educational as I wish to convert a program to use a proper database ratehr than lots of lists.

Thanks

 

[klaus]

I consider it as a bug.
The problem is that i test if the text contains a semi-colon and if yes, i admit it as the separator character and if no the separator character is a comma.
If i had tested with a comma instead of the semi-colon we would have the inverse behavior.
I will look at it.

 

[klaus]

The program has been updated to version 1.5.
Improved the detection of the separator character mentioned in post #14.

 

[toby]

EDIT: 2023.11.20 Version 1.5

you're far ahead of us and we have a lot to catch up.

 

[Alexander Stolte]

What do you think about the idea of converting multiple csv files into one db file? Because I have 3 csv files that I need to get into one. Or instead of 3 files, just in one csv file, 3 sheets. The tool unfortunately only supports one csv file.

 

[klaus]

This is not on my to do list.
Then, it would depend on what are the differences between the data.
Are there only new rows, are the only new columns, or any other combination.
Anyway the source code is in the first post and feel free to adapt it to your needs.

 

[Mahares]

From what I understood, this creation by klaus assumes that the text file to convert always has a first line as header row. Is that correct or no?
If the first line is not a header but it is data, then I think I can modify the code to automatically create column names Col1, Col2, ..Colx for the table. If it is not necessary, please advise.