B4J Question SQLite import CSV with single quote inside field

[Phil Thompson]

I am trying to import a csv into sqlite with
lst1 = su.LoadCSV(FDir, FName, ",")
This csv has almost 250,000 lines, with 7 columns

There are many fields in different columns that contain the single quote character inside the text of the column
ie: d'Azure, O'Mahoney,
The sqlite insert command chokes on this text field.

SqlStr="INSERT INTO DMR_Contacts (ID,RadioID,Callsign,Name,Name2,City,State,Country) VALUES ("&i&","&sC(0)&",'"&sC(1)&"','"&sC(2)&"','"&sC(3)&"','"&sC(4)&"','"&sC(5)&"','"&sC(6)&"')"
SQL.ExecNonQuery(SqlStr)

My app downloads this csv and imports it into SQLite
The following works but is dreadfully slow, and changes the meaning of the text
sC = lst1.Get(i)
If sC(3).Contains("'") Then
sC(3)=sC(3).Replace("'","")
End If
If sC(3).Contains("'") Then
sC(3)=sC(3).Replace("'","")
End If
If sC(4).Contains("'") Then
sC(4)=sC(4).Replace("'","")
End If
If sC(5).Contains("'") Then
sC(5)=sC(5).Replace("'","")
End If
I have spent many hours fighting this issue>
I and not a real programmer, just a newbie (3 weeks on B4J)
Any suggestions would be greatly appreciated

 

[Mariano Ismael Castro]

I am trying to import a csv into sqlite with
lst1 = su.LoadCSV(FDir, FName, ",")
This csv has almost 250,000 lines, with 7 columns

There are many fields in different columns that contain the single quote character inside the text of the column
ie: d'Azure, O'Mahoney,
The sqlite insert command chokes on this text field.

My app downloads this csv and imports it into SQLite
The following works but is dreadfully slow, and changes the meaning of the text
sC = lst1.Get(i)
If sC(3).Contains("'") Then
sC(3)=sC(3).Replace("'","")
End If
If sC(3).Contains("'") Then
sC(3)=sC(3).Replace("'","")
End If
If sC(4).Contains("'") Then
sC(4)=sC(4).Replace("'","")
End If
If sC(5).Contains("'") Then
sC(5)=sC(5).Replace("'","")
End If
I have spent many hours fighting this issue>
I and not a real programmer, just a newbie (3 weeks on B4J)
Any suggestions would be greatly appreciated

Check this
https://www.b4x.com/android/forum/threads/b4x-csvparser-csv-parser-and-generator.110901/

 

[OliverA]

What does you insert command look like? Are you using parameterized queries? If not, that may be your solution to your problem

 

[zed]

Use a smart string literal. There will no longer be problems with quotes and double quotes

 

[Mahares]

There are many fields in different columns that contain the single quote character inside the text of the column
ie: d'Azure, O'Mahoney,
The sqlite insert command chokes on this text field.

2. It is obvious from the code you posted that you are not using a parameterized INSERT query which you need to do, especially when you have single quotes in your data. Do not use any REPLACE statement. Do not tamper with your data. Leave it as it is. You are going to need the real data the way it was imported when you execute various queries later on.
2. Since you are dealing with a very large text file, you definitely need to use a BATCH insert with the WAIT FOR
Do not sweat it out by yourself. If you need help and are not sure how to proceed using either or both of the methods, post your project or if you cannot post a project, at least post a sample CSV file containg say, few hundred records or so and your database structure. We will help you.

 

[Daestrum]

I would build the string like (if you are not using parameterized query)

Dim SqlStr As String = $"INSERT INTO DMR_Contacts (ID,RadioID,Callsign,Name,Name2,City,State,Country) VALUES (${i},${sC(0)},"${sC(1)}","${sC(2)}","${sC(3)}","${sC(4)}","${sC(5)}","${sC(6)}")"$

Then alter it for sqlite (replacing the single quote (if there are any in the values) with 2 single quotes, then replace double quotes with single quote)

SqlStr = SqlStr.Replace("'","''").Replace("""","'")

in fact this works too (but looks really bad)

Dim SqlStr As String = $"INSERT INTO DMR_Contacts (ID,RadioID,Callsign,Name,Name2,City,State,Country) VALUES (${i},${sC(0)},"${sC(1)}","${sC(2)}","${sC(3)}","${sC(4)}","${sC(5)}","${sC(6)}")"$.Replace("'","''").Replace("""","'")

 

[Phil Thompson]

2. It is obvious from the code you posted that you are not using a parameterized INSERT query which you need to do, especially when you have single quotes in your data. Do not use any REPLACE statement. Do not tamper with your data. Leave it as it is. You are going to need the real data the way it was imported when you execute various queries later on.
2. Since you are dealing with a very large text file, you definitely need to use a BATCH insert with the WAIT FOR
Do not sweat it out by yourself. If you need help and are not sure how to proceed using either or both of the methods, post your project or if you cannot post a project, at least post a sample CSV file containg say, few hundred records or so and your database structure. We will help you.

Thankyou!!! I set it up with parameters in batch mode and it works, an order of magnitude faster than my previous best effort.
Thanks Again