Results 1 to 8 of 8

Thread: [RESOLVED] Exporting Access Database and importing to SQlite - Encoding issue with symbols Ω

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Feb 2009
    Location
    Uk
    Posts
    157

    Resolved [RESOLVED] Exporting Access Database and importing to SQlite - Encoding issue with symbols Ω

    Im trying to export my data from an access database and import it to an SQlite database, however some of the characters have encoding issues such as Ohms Ω which displays as a ? in the database

    I've tried to copy / paste the data but that still has the same issue, tried various encoding exports such as Western European (Windows), Windows-1252, Unicode, & UTF-8 which semi works.

    I've exported the data as UTF8 encoding which imports into the SQlite database correctly - however it causes a 'specified cast is not valid' error to be thrown when read by my application

    Any idea how i can get my data out of access and into SQlite whilst also being compatable with my .net 2.0 program?

    My alternative is to manually go through the whole database and manually update all of the broken characters which doesnt appeal to me as i have 11 databases to convert

    Alternatively could i force UTF8 encoding at the database stream?

    Code:
          Private Sub getDbRecordSQLite(ByVal section As String)
    
            Dim connectStr As String = "Data Source=" & DecryptedDB & ";New=False;Compress=True;"
            Dim con As SQLiteConnection
            Dim cmd As SQLiteCommand
            Dim datareader As SQLiteDataReader
            Dim CommandText As String
            Dim i As Integer
            Dim randList
            Dim offset As Integer
    
            Dim question As Question
            Try
                con = New SQLiteConnection(connectStr)
                con.Open()
                randList = getRandList(QnosEachSection)
    
                For i = 0 To randList.count - 1
                    Dim x = randList.Item(i)
                    offset = x - 1
                    CommandText = "SELECT * FROM exam WHERE Section = '" & section & "'" & " LIMIT 1 OFFSET " & offset.ToString
                    cmd = con.CreateCommand()
                    cmd.CommandText = CommandText
    
                    datareader = cmd.ExecuteReader()
                    question = New Question
    
                    If datareader.Read() Then
                        question.section = datareader.GetString(1)
                        question.question = datareader.GetString(2)
                        question.option1 = datareader.GetString(3)
                        question.option2 = datareader.GetString(4)
                        question.option3 = datareader.GetString(5)
                        question.option4 = datareader.GetString(6)
                        question.answer = datareader.GetString(7)
                        question.regulation = datareader.GetString(9)
    
                        If Not DBNull.Value.Equals(datareader("Image")) Then
                            question.hasAttachment = True
                            question.attachment = BlobToImage(datareader("Image"))
                        Else
                            question.hasAttachment = False
                        End If
    
                        qList.Add(question)
                    Else
                        Debug.Print("a")
                    End If
                    datareader.Close()
                Next
                con.Close()
                con = Nothing
                GC.Collect()
            Catch ex As Exception
                Log.LogInfo(ex)
                MsgBox(ex.Message)
            End Try
    
        End Sub
    Last edited by experience; Dec 30th, 2013 at 03:53 PM.

  2. #2

    Re: Exporting Access Database and importing to SQlite - Encoding issue with symbols Ω

    What's the column data type? Is it varchar or nvarchar?

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Feb 2009
    Location
    Uk
    Posts
    157

    Re: Exporting Access Database and importing to SQlite - Encoding issue with symbols Ω

    The table columns are varchar except one column which contains images (blob)

  4. #4
    PowerPoster dunfiddlin's Avatar
    Join Date
    Jun 2012
    Posts
    8,245

    Re: Exporting Access Database and importing to SQlite - Encoding issue with symbols Ω

    Quote Originally Posted by formlesstree4 View Post
    What's the column data type? Is it varchar or nvarchar?
    Neither. It's SQLite. You get TEXT (The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE)) and like it!

    Why you would export data from Access to SQLite is a mystery to me. Not only is Access considerably more sophisticated database it's also VB friendly so you don't even have to have Access to create or maintain databases.
    As the 6-dimensional mathematics professor said to the brain surgeon, "It ain't Rocket Science!"

    Reviews: "dunfiddlin likes his DataTables" - jmcilhinney

    Please be aware that whilst I will read private messages (one day!) I am unlikely to reply to anything that does not contain offers of cash, fame or marriage!

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Feb 2009
    Location
    Uk
    Posts
    157

    Re: Exporting Access Database and importing to SQlite - Encoding issue with symbols Ω

    The main reason I've changed to sqlite is to secure the database (it is also lighter/faster than access which is a bonus) - unfortunately the only security Access offers is password protection which can easily be revealed using the Access password recovery apps.

    My sqlite database is now encrypted which makes my data a little bit more secure - but thats another topic!

    From what i can see, UTF8 is probably the easiest way to do this and somehow i would have to add UTF8 encoding to my datastream using something similar to this:?

    System.Text.Encoding.GetEncoding("UTF8")

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Feb 2009
    Location
    Uk
    Posts
    157

    Re: Exporting Access Database and importing to SQlite - Encoding issue with symbols Ω

    Actually i'm just testing it and it opens the database in UTF8, all the symbols are showing - however it is still throwing the 'specified cast is not valid' error - maybe this is referring to something else in the database and has nothing to do with the encoding!?

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Feb 2009
    Location
    Uk
    Posts
    157

    Re: Exporting Access Database and importing to SQlite - Encoding issue with symbols Ω

    Fixed (my error!)

    i double checked the design view of the table in the Access and one of the columns was set to type 'number' instead of 'text' - i changed it to 'text', exported the database as UTF-8 and imported it into my Sqlite database - its now working without any errors!

  8. #8

    Re: Exporting Access Database and importing to SQlite - Encoding issue with symbols Ω

    Quote Originally Posted by dunfiddlin View Post
    Neither. It's SQLite. You get TEXT (The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE)) and like it!

    Why you would export data from Access to SQLite is a mystery to me. Not only is Access considerably more sophisticated database it's also VB friendly so you don't even have to have Access to create or maintain databases.
    I'm not well versed with SQLite. Huh. The more you know.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width