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).

1682347253665.png


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.
 

Attachments

  • CsvToSQLiteV1_2.zip
    5.4 KB · Views: 476
  • CsvToSQLite_V1_6.zip
    6.2 KB · Views: 208
Last edited:

rboeck

Well-Known Member
Licensed User
Longtime User
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
 

Diceman

Active Member
Licensed User
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

Expert
Licensed User
Longtime User
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

Expert
Licensed User
Longtime User
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...
 

klaus

Expert
Licensed User
Longtime User
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.

1681900820832.png
 

Peter Meares

Member
Licensed User
Longtime User
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

Expert
Licensed User
Longtime User
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.
 

Alexander Stolte

Expert
Licensed User
Longtime User
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

Expert
Licensed User
Longtime User
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

Expert
Licensed User
Longtime User
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.
 
Top