-
Dec 30th, 2013, 03:22 PM
#1
Thread Starter
Addicted Member
[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.
-
Dec 30th, 2013, 04:35 PM
#2
Re: Exporting Access Database and importing to SQlite - Encoding issue with symbols Ω
What's the column data type? Is it varchar or nvarchar?
-
Dec 30th, 2013, 05:23 PM
#3
Thread Starter
Addicted Member
Re: Exporting Access Database and importing to SQlite - Encoding issue with symbols Ω
The table columns are varchar except one column which contains images (blob)
-
Dec 30th, 2013, 05:30 PM
#4
Re: Exporting Access Database and importing to SQlite - Encoding issue with symbols Ω
Originally Posted by formlesstree4
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!
-
Dec 30th, 2013, 05:52 PM
#5
Thread Starter
Addicted Member
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")
-
Dec 30th, 2013, 06:02 PM
#6
Thread Starter
Addicted Member
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!?
-
Dec 30th, 2013, 06:26 PM
#7
Thread Starter
Addicted Member
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!
-
Dec 30th, 2013, 08:07 PM
#8
Re: Exporting Access Database and importing to SQlite - Encoding issue with symbols Ω
Originally Posted by dunfiddlin
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|