CSV Data import into db

[JCO]

Hi,

I'm trying to import a set of rows from a csv file into a db using ExecNonQuery2. It seems to work fine when the list object contains 1 or 2 rows of data, but fails when there are more. I've looked in the help files and searched the forums but I don't find any info on such a limit (I thought the limit would either be 1 or none, but 2?).

The relevant code:

' Data in csv file: (read ok by lst1)
' "HQ", "00", "Ground floor"
' "HQ", "01", "First floor"
' "HQ", "02", "Second floor"

   Dim lst1 As List
   lst1 = su.LoadCSV(sFilePath, sFlFile, "|")
   Log (lst1.Size) 'Returns 3, as expected
   Dim sColumn() As String 'Added for debugging
   sColumn = lst1.Get(0) 
   Log(sColumn.Length) '3, OK
   Log(sColumn(0)) '=HQ, OK
   Log(sColumn(1)) '=00 , OK
   Log(sColumn(2)) '=Ground floor, OK

   Try
   sql1.ExecNonQuery2("INSERT INTO fl (bl_id, fl_id, description) VALUES (?, ?, ?)", lst1) 
   Catch
      'None if only two rows in CSV, otherwise:
      Log(LastException.Message)' -> android.database.sqlite.SQLiteException: bind or column index out of range: handle 0x234c70
   End Try

Thanks for any info,

Julio

 

[Erel]

It doesn't really work in any case.
LoadCSV returns a list of arrays. You should iterate over this list and add each array separately.

Try
sql1.BeginTransaction
Dim lst1 As List
lst1 = ...
For i = 0 To lst1.Size - 1
   Dim sColumn() As String
   sColumn = lst1.Get(i)
   sql1.ExecNonQuery2("INSERT INTO fl (bl_id, fl_id, description) VALUES (?, ?, ?)", sColumn) 
Next
sql1.TransactionSuccessful
Catch
...
End Catch
sql1.EndTransaction

 

[JCO]

Thanks for the reply.
Yes, that's the way I had done it, but when in my testing I saw that it worked for two rows, I thought that maybe that wonderful List object (I really love it) would be able to do all the importing in one clean shot.

 

[paskuale]

Hi JCO what path you use for import csv file ?

lst1 = su.LoadCSV(sFilePath, sFlFile, "|")

sFilePath = ?

tnx

 

[JCO]

Hi JCO what path you use for import csv file ?
tnx

It's a parameter to the Sub. It can be configured in the settings of the App. Normally it's an app folder in the external storage

But going row by row, the import works fine (other than the fact that it's a bit slow).

Julio