Hello everyone, i've come across this issue while downloading a csv file, the amount of records inside the file can vary from 90 to a few hundred records.
I'm able to save the file, read it using the StringUtils library, i'm able to extract the headers just fine, the problem is when i save the records to a Sqlite database. some of the records contain a comma within each column, for example the number of columns of the database is 34 which are the amount of columns in the header, when i parse each line and try to insert each record into the database, i sometimes will end up with some rows that have more than 34 columns and this is because of the comma (,) that some of the columns contain.
My question is, does anyone have any suggestion of how I can escape the comma (,) on some of the columns?
For example the column How did you Hear about us? will have something like this
Facebook, Instagram, Flyer.
As you can see in this case since there are two commas in that line, I will end up with 3 different items from that line even though it should be treated as only one line.
My relevant code is below.
Thanks for the help in advanced everyone.
Cheers,
Walter
I'm able to save the file, read it using the StringUtils library, i'm able to extract the headers just fine, the problem is when i save the records to a Sqlite database. some of the records contain a comma within each column, for example the number of columns of the database is 34 which are the amount of columns in the header, when i parse each line and try to insert each record into the database, i sometimes will end up with some rows that have more than 34 columns and this is because of the comma (,) that some of the columns contain.
My question is, does anyone have any suggestion of how I can escape the comma (,) on some of the columns?
For example the column How did you Hear about us? will have something like this
Facebook, Instagram, Flyer.
As you can see in this case since there are two commas in that line, I will end up with 3 different items from that line even though it should be treated as only one line.
My relevant code is below.
B4X:
Dim su As StringUtils
Dim headers As List
Dim table As List
table = su.LoadCSV2(rp.GetSafeDirDefaultExternal(""), "list.csv", ";", headers)
''Insert each header column into a Map with the Column Type
Dim headers2 As Map
headers2.Initialize
For j = 0 To headers.Size - 1
Log("header: " & j & " " & headers.Get(j))
Dim header() As String = Regex.Split(",", headers.Get(j))
For r = 0 To header.Length - 1
headers2.Put(header(r), "TEXT")
Next
Next
'''Create table with columns given in headers2 map
DBUtils.CreateTable(Common.SQL1, "members", headers2, "")
''since the splitting character in the csv file is ";"
''' the only item in the items() variable will 1 line with all the columns separated by commas.
Dim membermap As Map
membermap.Initialize
For i = 0 To table.Size - 1
Log("table item # " & i)
Dim items() As String = table.Get(i)
Dim fields() As String
fields = Regex.Split(",", items(0))
''If headers2.Size = fields.Length Then
LogColor("headers2 size: " & headers2.Size & " " & "fields lenght: " & fields.Length, Colors.blue)
For q = 0 To headers2.Size - 1
membermap.Put(headers2.GetKeyAt(q), fields(q))
''membermap.Put(headers2.GetKeyAt(q), items(q))
Next
Dim memberlist As List
memberlist.Initialize
memberlist.Add(membermap)
DBUtils.InsertMaps(Common.SQL1, "members", memberlist)
NextThanks for the help in advanced everyone.
Cheers,
Walter
