Convert data csv to sl3(SQLite)

Discussion in 'Questions (Windows Mobile)' started by JJM, Dec 24, 2008.

  1. JJM

    JJM Active Member Licensed User

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

    Best regards
    JJM
     
  2. Erel

    Erel Administrator Staff Member Licensed User

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

    mozaharul Active Member Licensed User

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


    regards,
     

    Attached Files:

  4. JJM

    JJM Active Member Licensed User

    Thank you for your help.

    JJ M
     
  5. Museltook

    Museltook Member

    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?
     
    Last edited: Mar 19, 2010
  6. Erel

    Erel Administrator Staff Member Licensed User

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

    Museltook Member

    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)?
     
  8. Erel

    Erel Administrator Staff Member Licensed User

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

    Museltook Member

    i try it with:

    Code:
    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...
     
    Last edited: Mar 20, 2010
  10. Museltook

    Museltook Member

    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:

    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...
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice