Convert data csv to sl3(SQLite)

[JJM]

Hello,
Could you help me?
I don't remember how to convert a CSV file to a sl3 file.

Best regards
JJM

 

[Erel]

You can load the data to a table with Table.LoadCSV and then create a new SQL table with Connection.CreateSQLTable.
You will need to first open the connection (with the new file name).

 

[mozaharul]

I'm using the attached application for the same purpose.


regards,

 

[JJM]

Thank you for your help.

JJ M

 

[Museltook]

Is there a way to Update a whole table in the Database, with the actual loaded table? in your example, you create a new database, but what is, when i have an old database and want to update his data with a newly imported csv file? can i use only a "for->To construct" and Update? whats with new entry, in this updated file?

Update: I have found the way to Drop the table. but now i have another problem. i have imported a csv an converted this to sqlite successfully. but whats with the datatypes? the firts field MUST be unique /primary key/ autoincrement. how can i resolve this?

 

[Erel]

You can first create a temporary table with Connection.CreateSQLTable and then use an Insert with select to copy the data to the real table which you previously defined with the required constraints.

 

[Museltook]

When i try this, it work in parts. I get a new (temp)table, with the data from the table control. But all fieldtypes are in TEXT. After this i create a new (target) table with the needed field types. That works also. But when i copy the data from the (temp)table to the (target)table, i will get an error : „type missmatch“. I think this belongs to the different field types in both tables. but how can i convert the field types, espacialy from TEXT to INTEGER (PRIMARY KEY)?

 

[Erel]

Do you load your data from a CSV file? If yes, then first create the columns with the correct type (cNumbers) and only then load the data.
That way when you convert it to SQL it will create columns of type REAL in the db. You will still need to convert it to INTEGER which can be done with the CAST keyword (in your select query).

 

[Museltook]

i try it with:

Sub Test_Click
    Connection.Close
    Connection.Open("Data Source = " & AppPath & "\test.sqlite")
    
    Connection.BeginTransaction
    Command.CommandText="DROP table movies"
    Command.ExecuteNonQuery
    Connection.CreateSQLTable("ImportMovies","tmp_movies")
    Command.CommandText="INSERT INTO tmp_movies SELECT * FROM tmp_movies"
    Command.ExecuteNonQuery
    Command.CommandText = "CREATE TABLE movies (movie_id INTEGER PRIMARY KEY, movie_title TEXT, genre_id NUMERIC, format_id NUMERIC, movie_date NUMERIC, movie_modified NUMERIC, movie_copy TEXT, movie_place TEXT, movie_comment TEXT, movie_fsk TEXT, movie_length NUMERIC, movie_celebs TEXT, movie_regie TEXT, movie_description TEXT)"
    Command.ExecuteNonQuery
    Command.CommandText = "INSERT INTO movies SELECT * FROM tmp_movies"
    Command.ExecuteNonQuery
    Command.CommandText = "DROP TABLE tmp_movies"
    Command.ExecuteNonQuery
    Connection.EndTransaction
    
    Msgbox("Database successfully created !")
End Sub

but this doesnt work :-( i dont know why, i will test your comments tomorrow... maybe you can correct my code, when you have the time...


PS: when i use the SQL statement in the SQL Browser (mozilla AddoN). it works like a charm, but not with Basic4PPC. In the mozillAddon, i put the SQL statement with ";" at the end... line for line

PPS: I get the error "constraint failed". hope this helps...

 

[Museltook]

Oh my god, im so stupid. i have the solution!!! I have added the records twice, so i get the arror with the constraints. i use now this code:

 Connection.Close
    Connection.Open("Data Source = " & AppPath & "\test.sqlite")
    Command.CommandText="DROP table If Exists test_movies"
    Command.ExecuteNonQuery
    Connection.CreateSQLTable("ImportTable","tmp_movies")
    Command.CommandText = "CREATE TABLE movies (movie_id INTEGER PRIMARY KEY, movie_title TEXT, genre_id NUMERIC, format_id NUMERIC, movie_date NUMERIC, movie_modified NUMERIC, movie_copy TEXT, movie_place TEXT, movie_comment TEXT, movie_fsk TEXT, movie_length NUMERIC, movie_celebs TEXT, movie_regie TEXT, movie_description TEXT)"
    Command.ExecuteNonQuery
    Command.CommandText = "INSERT INTO movies SELECT * FROM tmp_movies"
    Command.ExecuteNonQuery
    Command.CommandText = "DROP TABLE tmp_movies"
    Command.ExecuteNonQuery
    Command.CommandText = "VACUUM"
    Command.ExecuteNonQuery
    Connection.Close

this works. after the insert and convert, it cleans up the database...