Insert problem

cirollo · Feb 10, 2012

I post here because I'm stuck in a big problem:

this is what the log says:

(SQLiteException) android.database.sqlite.SQLiteException: near ")": syntax error: , while compiling: INSERT INTO [Clienti] () VALUES ()

this is my code:

B4X:

   Dim su As StringUtils
   Dim Table,Table2 As List
   Dim Items() As String
      If File.Exists(sdRoot, "clienti.csv") = True Then
         Table = su.LoadCSV(sdRoot, "clienti.csv", ";")
         Table2.Initialize
         For i = 0 To Table.Size - 1
            Items = Table.Get(i)
            Dim m As Map   
            m.Initialize
            m.Put("IdCli", Items(0)) 
            m.Put("Ragsoc", Items(1))
            m.Put("Indirizzo", Items(2))
            m.Put("Cap", Items(3))
            m.Put("Localita", Items(4))
            m.Put("Prov", Items(5))
            m.Put("Telefono", Items(6))
            m.Put("Mobile", Items(7))
            m.Put("Iniziale", Items(1).SubString2(0,1))
            m.Put("Giorno", Items(8))
            m.Put("Status", Items(9))
            m.Put("Listino", Items(10))
            m.Put("CatScm", Items(11))
            m.Put("AliIva", Items(12))
            Table2.Add(m)
         Next
         m.Clear
         DBUtils.InsertMaps(SQL1, "Clienti", Table2)
      End If
   End If

and I attached the file that I'm trying to import into the db
please, I don't know what else to try!!!!

Kiffi · Feb 10, 2012

replace the single quotes (') in your CSV with two single quotes ('')

B4X:

[...]
m.Put("IdCli", Items(0).Replace("'", "''")) 
m.Put("Ragsoc", Items(1).Replace("'", "''"))
m.Put("Indirizzo", Items(2).Replace("'", "''"))
m.Put("Cap", Items(3).Replace("'", "''"))
m.Put("Localita", Items(4).Replace("'", "''"))
m.Put("Prov", Items(5).Replace("'", "''"))
m.Put("Telefono", Items(6).Replace("'", "''"))
m.Put("Mobile", Items(7).Replace("'", "''"))
m.Put("Iniziale", Items(1).SubString2(0,1).Replace("'", "''"))
m.Put("Giorno", Items(8).Replace("'", "''"))
m.Put("Status", Items(9).Replace("'", "''"))
m.Put("Listino", Items(10).Replace("'", "''"))
m.Put("CatScm", Items(11).Replace("'", "''"))
m.Put("AliIva", Items(12).Replace("'", "''"))
[...]

Greetings ... Kiffi

cirollo · Feb 11, 2012

it's not that!

infact, in the csv file I don't have ' and " only fields separated by ;

the problem is this line:

DBUtils.InsertMaps(SQL1, "Clienti", Table2)

if I comment it all works

anybody could test my file and give me an hint?

Kiffi · Feb 11, 2012

cirollo said:
infact, in the csv file I don't have ' and " only fields separated by ;

ASSIST;Canone d'assistenza AHR modulo Base;ASSIST;n.;;Servizi;;20
ASSISTPOSTO;Canone d'assistenza AHR Posto Aggiuntivo;ASSISTPOSTO;n.;;Servizi;;20

Greetings ... Kiffi

cirollo · Feb 11, 2012

maybe b4a is not good for me!

tried this other method:

B4X:

   If File.Exists(sdRoot, "clienti.csv") = True Then
   Msgbox("clienti","")
      TxtLog.Text = "Inserimento Dati Tabella Clienti..." &Chr(10)&TxtLog.Text
      SQL1.BeginTransaction
      Dim Reader As TextReader
          Reader.Initialize(File.OpenInput(sdRoot, "clienti.csv"))
         Try
            Dim line As String
             Reader.ReadLine
           line = Reader.ReadLine
         Msgbox(line,"inizio")
           Do While line <> Null 
              Dim j As Int
            j = j + 1
        '       line = Reader.ReadLine
      
               Dim ColumnContent() As String
               ColumnContent = Regex.Split(";", line)
            TxtLog.Text = "Inserimento Cliente: " &ColumnContent(0) &" - "&ColumnContent(1)&Chr(10)&TxtLog.Text
               For i = 0 To 12
                  ColumnContent(i) = ColumnContent(i).Replace(QUOTE, "")
            '   ColumnContent(i) = ColumnContent(i).Replace(".", " ")
                    ColumnContent(i) = ColumnContent(i).Replace(",", " ")
            '   If ColumnContent(i) = "." Then
            '      ColumnContent(i) = " "
            '   End If 
         '      Msgbox(ColumnContent(i),"")
               Next
            SQL1.ExecNonQuery2("INSERT INTO Clienti VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", Array As Object (ColumnContent(0), ColumnContent(1), ColumnContent(2), ColumnContent(3), ColumnContent(4), ColumnContent(5), ColumnContent(6), ColumnContent(7), ColumnContent(7).SubString2(0,1), ColumnContent(8), ColumnContent(9), ColumnContent(10), ColumnContent(11), ColumnContent(12)))
               Msgbox(line,"fine")
            DoEvents
           Loop
       Catch
           Log(LastException.Message)
       End Try
       Reader.Close
      SQL1.TransactionSuccessful
       SQL1.EndTransaction
   End If

how can I do if some fields (not primary key) are empty??? It returns an error!
If someone can suggest other methods, please do it.

Kiffi · Feb 11, 2012

The following snippet imports your CSV without problem:

B4X:

If File.Exists(File.DirInternal, "articoli.csv") = False Then
   File.Copy(File.DirAssets, "articoli.csv", File.DirInternal, "articoli.csv")
End If

File.Delete(File.DirInternal, "database.db")

SQL1.Initialize(File.DirInternal, "database.db", True)

SQL1.ExecNonQuery("Create Table myTestTable (c0,c1,c2,c3,c4,c5,c6,c7)")

Dim su As StringUtils
Dim Table,Table2 As List
Dim Items() As String

If File.Exists(File.DirInternal, "articoli.csv") = True Then

   Table = su.LoadCSV(File.DirInternal, "articoli.csv", ";")

   Table2.Initialize

   For i = 0 To Table.Size - 1

      Items = Table.Get(i)

      Dim m As Map    

      m.Initialize

      For Counter = 0 To Items.Length - 1
         m.Put("c" & Counter, Items(Counter)) 
      Next

      Table2.Add(m)

   Next

   DBUtils.InsertMaps(SQL1, "myTestTable", Table2)

End If

Msgbox(SQL1.ExecQuerySingleResult("Select Count(*) From myTestTable"), "")

BTW: Your posted CSV contains only 8 fields each line (not 13).

Greetings ... Kiffi

cirollo · Feb 14, 2012

thank you kiffi

I've used a method similar to manage the problem

using file.readline and file.writeline

even if with big files is quite slow!

Insert problem

cirollo

Active Member

Attachments

Kiffi

Well-Known Member

cirollo

Active Member

Kiffi

Well-Known Member

cirollo

Active Member

Kiffi

Well-Known Member

cirollo

Active Member

Insert problem

cirollo

Active Member

Attachments

Kiffi

Well-Known Member

cirollo

Active Member

Kiffi

Well-Known Member

cirollo

Active Member

Kiffi

Well-Known Member

cirollo

Active Member

Privacy & Transparency

Privacy & Transparency