Populating SQLite Db in iOS from CSV file?

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
quailcreek
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Populating SQLite Db in iOS from CSV file?

Post by quailcreek » Sun Jan 03, 2016 6:15 am

Hi,
I want to be able to pull data from a CSV or xlsx file in the users DropBox account and save it to an iSO apps SQLite Db.

I have figured out how to insert, line-by-line into the Db, at least I think I have.
Ken Ray at Sons of Thunder has some code I’ve used in the past for desktop apps but I’m sure it will need to be changed in order to work for iOS and SQLite.

Related to this I have 2 questions... for now anyway.
1) I’m going to figure the best way to access the users DropBox account would be using Monte’s mergDropbox external? If you agree or If anyone has another or a better way please let me know.

2) How do I pull the info from the CSV file or is it better to use xlsx file? And what changes need to be made to this code to make it work?

This is what I've used before for desktop apps.

Code: Select all

global gRowCount

on mouseUp
   answer file "Select the Excel file to open" with type "Excel Files|xls,xlsx"
   
   if it is not empty then
      launch document it
      put empty into it
      
      ask "Please enter the number of rows in the spreadsheet."
      if the result is "Cancel" then
         answer "Import Canceled"
         put empty into gRowCount
         exit mouseUp
      else
         if it is empty then
            answer "No information entered."
         else
            put it into gRowCount
            importCSV
         end if
      end if
   else
      answer "Import Canceled"
   end if
end mouseUp

on importCSV
   if gRowCount = "Cancel" then exit importCSV
   else
      
      put GetFromXL("A1:L1") into tHeaders
      put GetFromXL("A2:L" & gRowCount) into tData

      set the itemdel to tab
      set the rowDel to cr
      
      get executeSQL("BEGIN")
      if it is not an integer then
         -- error handling code
         answer "An error occured BEGIN"
         exit importCSV
      end if
      
      repeat with nLines = 1 to gRowCount
         put item 1 of line nLines of tData into SQLArray[1]
         put item 2 of line nLines of tData into SQLArray[2]
         put item 3 of line nLines of tData into SQLArray[3]
         put item 4 of line nLines of tData into SQLArray[4]
         put item 5 of line nLines of tData into SQLArray[5]
         put item 6 of line nLines of tData into SQLArray[6]
         put item 7 of line nLines of tData into SQLArray[7]
         put item 8 of line nLines of tData into SQLArray[8]
         put item 9 of line nLines of tData into SQLArray[9]
         put item 10 of line nLines of tData into SQLArray[10]
         put item 11 of line nLines of tData into SQLArray[11]
         put item 12 of line nLines of tData into SQLArray[12]
         
         put "INSERT into " & tTable & "(FIrst, Middle, Last, Suffix, Spouse, Address, City, State, Zip, Country, HomePhone, CellPhone) "&\
         "VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12)" into tSQLStatement
         revExecuteSQL the uDatabaseID of this stack, tSQLStatement, "SQLArray"
         
         if the result is not an integer then
            get executeSQL("ROLLBACK")
            -- error handling here
            answer "An error occured ROLLBACK"
            exit importCSV
         end if
         
      end repeat
      
      get executeSQL("COMMIT")
      if the result is an integer then
         populateTheGrid
      end if
   end if
end importCSV

function GetFromXL pRangeRef
   switch (the platform)
      case "MacOS"
         put "tell app" && q("Microsoft Excel") & cr & "get the value of range" && q(pRangeRef) & cr & "end tell" into tScript
         do tScript as AppleScript
         put the result into tData
         
         replace "{{" WITH "{" in tData
         replace "}}" WITH "}" in tData
         replace "}, " WITH ("}" & cr) in tData
         replace ("\" & quote) WITH numtochar(1) in tData
         replace (quote & ", ") WITH tab in tData
         if char 1 of tData = "{" then delete char 1 of tData
         if char -1 of tData = "}" then delete char -1 of tData
         replace ("}" & cr & "{") WITH CR in tData
         replace quote WITH "" in tData
         replace numtochar(1) WITH quote in tData
         return tData
         break
         
      case "Win32"
         put "Dim ObjXL,tNumRows,tNumCols,tRetVal,tRow,tCol" & cr & \
               "Set ObjXL = GetObject(," & q("Excel.Application") & ")" & cr & \
               "tNumRows = ObjXL.Range(" & q(pRangeRef) & ").Rows.Count" & cr & \
               "tNumCols = ObjXL.Range(" & q(pRangeRef) & ").Columns.Count" into tScript
         
         put tScript & cr & "For tRow = 1 To tNumRows" & cr & \
               "For tCol = 1 to tNumCols" & cr & \
               "if tCol <> tNumCols then" & cr & \
               "tRetVal = tRetVal & ObjXL.Range(" & q(pRangeRef) & ").Cells(tRow,tCol).Value & vbTab" & cr &  \
               "else" & cr & \
               "tRetVal = tRetVal & ObjXL.Range(" & q(pRangeRef) & ").Cells(tRow,tCol).Value & vbCrLf" & cr & \
               "end if" & cr & "Next" & cr & "Next" into tScript
         
         put tScript & cr & "tRetVal = Left(tRetVal,Len(tRetVal) - 2)" & cr & "WScript.Echo tRetVal" into tScript
         
         put specialFolderPath("temporary") & "/VBSTemp.vbs" into tFile
         put tScript into url("file:" & tFile)
         set the hideconsolewindows to true
         
         get shell("cscript.exe //nologo" && q(tFile))
         
         send "delete file" && q(tFile) to me in 1 second
         if char -1 of it is CR then delete char -1 of it  -- strip any trailing CR
         return it
         break
   end switch
end GetFromXL

function q pWhat
  return quote & pWhat & quote
end q
Tom
MacBook Pro OS Mojave 10.14

quailcreek
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Re: Populating SQLite Db in iOS from CSV file?

Post by quailcreek » Tue Jan 12, 2016 10:47 pm

Hi,
Does anybody have any thoughts on this? I'm not sure where t start.
Tom
MacBook Pro OS Mojave 10.14

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: Populating SQLite Db in iOS from CSV file?

Post by AxWald » Wed Jan 13, 2016 10:20 am

Hmmm,

dunno if this is what you're looking for, but let's try:
  1. You have a .csv. (I'd avoid xls, much too complicated ...)
  2. You read this file into a variable ("MyTable"), and using "replace" make sure you have a nice comma/return delimited "table", like this:

    Code: Select all

    MyIntegerID,'AString','AnotherString',ACurrencyPointDelimited,AnotherInteger
    ...
    Make sure to have single-quoted all strings!
  3. 3. Construct a SQL String:

    Code: Select all

    put "INSERT into MyTABLE (ID, FirstName, LastName, MoneySpent, Rank) VALUES " into StrSQL
    put return & "(" & MyTable & ");" after StrSQL
  4. And revdb_execute it ;-)
That's how I'd do it. Beware, above is untested pseudo code! But you should get the idea.

Have fun!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

Mikey
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 755
Joined: Fri Jun 27, 2008 9:00 pm

Re: Populating SQLite Db in iOS from CSV file?

Post by Mikey » Wed Jan 13, 2016 2:43 pm

We do this many times per hour from many devices across several apps in several companies. If you give up trying to do it yourself or getting free advice, let us know, and we will be happy to provide you with a quote.

quailcreek
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Re: Populating SQLite Db in iOS from CSV file?

Post by quailcreek » Wed Jan 13, 2016 7:30 pm

Hi AxWald,
Thanks. I appreciate the help. What I'm trying to figure out is how to pull the data from a cvs file. And how to access the file on the cloud.
Tom
MacBook Pro OS Mojave 10.14

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: Populating SQLite Db in iOS from CSV file?

Post by phaworth » Wed Jan 13, 2016 8:23 pm

The SQL side of this is easy, with the caveat that you should use the variableslist/arrayname parameter of revExecuteSQL so you don't have to worry about escaping quotes.

Can't help much with how to download the csv file if you mean you want to do it programmatically.

To parse the csv file, the only way to go is to use Alex Tweedly's excellent csv parser. There are a lot of horrible gothas that occur when parsing csv files and Alex's handler deals with just about all of them. Just search for Alex on the forum and I'm sure you'll find where to get the handler. If you don;t find it, send me a message offlist and I'll send the version I have.

Pete

quailcreek
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Re: Populating SQLite Db in iOS from CSV file?

Post by quailcreek » Wed Jan 13, 2016 9:53 pm

Hi Pete,
is this what your talking about?
function CSV4Tab pData,pColdelim

I also grabbed this.
function CSVtoArray pData
Tom
MacBook Pro OS Mojave 10.14

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: Populating SQLite Db in iOS from CSV file?

Post by phaworth » Thu Jan 14, 2016 12:33 am

Yes, those are the ones.
Pete

Post Reply

Return to “Databases”