Data Importing

I have an old program in windows that will export data to a CSV format.

In my new program that I have written on the MAC I want to import the data from
the old database (CSV file) to the new Sqlite database. This will be a one time shot.
The fields do not match between the 2 programs.

Can anyone provide a sample or information on how to do this ?

Jerry, if you just need to convert the old CSV to sqlite, you can simply convert the file with a DB tool for Sqlite.
If you would like to code something, instead, just take a look at the split() function.

I believe you will have more luck if you put some effort into solving the problem yourself and then asking specific questions with areas of difficulty along the way.

easiest way would be to import the data into a temp table. then use an insert statement to map in the temp table into the new table. You can delete the temp table when operation finishes.

@ Jeremy Cowgar If thats the type of help you have to offer why bother. I did try things and you assume I did
not put any effort into it. Who the hell are you to say otherwise anyway. Sorry I’m not up to your standards. I thought
this forum was to get help and to help others when and where you can.

And to you other guys, Thanks for the comments.

Hi Jerry, did you find a solution for what you’re trying to do?

For importing data from other databases I usually do as follows:

  1. I use Navicat Premium to connect to the old database and pull data into a new SQLite database. Navicat does a nice job to transcode data to UTF-8 encoding, which is what I want to have in a Xojo app.

  2. If the data structure changes from the old to the new database, then I write a little Xojo app which selects and adjusts data from the intermediate SQLite database (created with Navicat) to the final database.

Let us know if you need some more specific support.

@ Oliver
The old program does not use a database. It saves data to a structured encrypted text file.
Luckily the program will export to a CSV file. There are only 9 data fields in the old program and in my
new program I have many more fields for the same table. I have SQlite Expert Pro and Excel.
I brought all the data into Excel and created the fields in excel to match the fields and then saved
it to a new CVS from Excel and then tried to import it into the SQliteDatabase using SQlite Expert Pro.
The problem is it only will bring in 15 records of 140 and misses 2 of the 15 that do come in.
I’ve been racking my brain for the last to days trying to figure out why and haven’t figured it out yet.
Thats why I asked the question here to see what others have done for importing data from 1 format to another.

I usually save to TAB-Delimited format to make it easier to read and parse the fields.

ReadLine each record
Split on Tab to get an array
Manipulate the data as needed
Save to the new database

As Tim wrote: save the CSV file as a tab delimited file.
One way to read such file would be as in this example, reading a textfile with 3 columns, tabulator delimited:

[code]Private Sub imTextRead()
Dim f As FolderItem
Dim textInput As TextInputStream
Dim AccountNo, AccountName, IsRev As String
Dim rowFromFile As String
Dim txName As String = “accounts_EN.txt”

#If TargetCocoa
f = App.ExecutableFile.Parent.Parent.Child(“Resources”).Child(txName)
#Else
f = GetFolderItem(“Resources”).Child(txName)
#EndIf

If f <> Nil And f.Exists Then
Dim tab As String = ChrB(9)
textInput = TextInputStream.Open(f)
textInput.Encoding = Encodings.UTF8 //strings are UTF8

While Not textInput.EOF
  rowFromFile = textInput.ReadLine
  
  If (CountFields(rowFromFile, tab) <> 3) Then
    MsgBox "ERROR ..."
    Return
  Else
    AccountNo = NthField(rowFromFile, tab, 1)
    AccountName = NthField(rowFromFile, tab, 2)
    IsRev = NthField(rowFromFile, tab, 3)
  End If
  
  If Not imAccountAddImportRow(AccountNo, AccountName,IsIncome) Then
    MsgBox "ABORT: Unknown error"
    Exit
  End If
Wend
textInput.Close

End If
End Sub
[/code]

You then still would need a method to write each record to your database file (In my example this is done by imAccountAddImportRow)

Because it is good advice. I saw the question was not answered after a period of time and was offering advice on how to get better response from the forums. Generally specific questions get answered quickly vs. broad questions. I was attempting to be helpful, I’m sorry that it came across incorrectly. I did read my response and thought it sounded harsh but was unable to edit the post to lighten it up. About 1/2 of the time I am never able to edit my posts, I should re-read them before clicking the “Post a Reply”. My bad.

I have a free set of classes to parse CSV files.

Try them here: https://dl.dropboxusercontent.com/u/10747116/RealStudioClasses/sbCSV_In_Out.zip

Thanks for the help guys. I did end up using what I was using to start with (Excel and Sqlite Expert Pro).
My problem ended up being the import function in Sqlite Expert Pro. It would only read in 1 line at a time.
Still not sure what I did in Excel but it finally worked.

Simon, I did try playing with your routines but just got errors. Something about not existing.

Oliver. Your example could have worked for me but I did not try it as excel worked.

Again thanks for the help guys.