Android Question [SOLVED] SQLite - importing a csv-utf8 text file

[LucaMs]

I have a csv-utf8 text file, which has two "columns", like:

Name,Age <--- "header" with db field names
Erel,368 <--- data
Me,99

If I try to read the db field "Name" and use it in a query I get the error: "no such column: Name".

The field name displayed by log appears to be correct, but it probably contains non-displayable characters.

I read the name of the field from the file in this way:

' Reads csv utf8 file
Dim lstLines As List = File.ReadList(File.DirAssets, "CSV_UTF8_FileToImport.csv")

' First "row" of csv file - a header with field names.
Dim FieldName As String = Regex.Split(",", lstLines.Get(0))(0)

'displays Name, correctly, but it probably contains non-displayable characters.
Log("Field name: " & FieldName)

Both DB and csv file are utf8. I tried other encodings without success.

The query written: "SELECT Name FROM..." works correctly.


I've attached the example.


Thank you

 

[sorex]

the problem is that you save the text as utf8 + signature.

remove the signature bytes and it will wrk fine.

or just use a line feed on line 1 and adjust the pointers in the splits.

 

[DonManfred]

Cur = mDB.ExecQuery("select Name from tbl where Name = '"&NameToSearch&"'")

works too.

I can confirm the error you got with your code

Cur = mDB.ExecQuery2(strQrySearch, Array As String(NameToSearch))

I get the same error saying the field Name do not exists. In my opinion weird.. It should work :-/

Using the query this way works..weird

    Cur = mDB.ExecQuery("select Name from tbl where Name = '"&NameToSearch&"'")
    Log(Cur.RowCount)
  
    If Cur.IsInitialized And Cur.RowCount > 0 Then
        'Log(Cur.GetString(FieldName))
        For i = 0 To Cur.RowCount -1
            Cur.Position = i
            For o = 0 To Cur.ColumnCount-1
                Log(Cur.GetColumnName(o)&" = "&Cur.GetString(Cur.GetColumnName(o)))
            Next
        Next
    End If
    Cur.Close

 

[eurojam]

Lucas,
try with quoted Fieldnames, then it should work:

sbQrySearch.Append("SELECT '" & FieldName & "' FROM tbl WHERE '" & FieldName & "' = ?")

best regards
stefan

 

[cimperia]

I got the same error, but I (re-)saved, ie overwrote, the csv file on my win7 laptop, using these settings (PC, UTF8) and everything works fine after that. So obviously there's something wrong with the file format as is.

 

[sorex]

the solution to your problems has already been given in my reply

the field name "name" has extra invisble stuff sticked to it (the utf8 signnature)

 

[LucaMs]

Thank you all.

the field name "name" has extra invisble stuff sticked to it (the utf8 signnature)

Yes, I'm sure; if you copy that "Name" from the log view and paste it to Wordpad, you can see a space inside of it.

I created the csv file using Notepad, which has only one UTF8 format.

Is the signature just a "header"? If so, how many bytes?

(I should try to install another text editor ).


Thank you all again.

 

[LucaMs]

Installed Notepad++.

Used "Convert to UTF-8 without BOM", now it's ok (except that in the code I have forgotten the position of the cursor )

 

[sorex]

Is the signature just a "header"? If so, how many bytes?

yes. it's 3 chars in your file. (the 0D 0A is the extra linefeed that's also a solution to bypass the problem)

 

[cimperia]

One more thing about the statements.

My own preference is to use smart strings to build statements as they are easier to read. If you have not considered using smart strings, this little example might give you a little push...

So I would replace

sbQrySearch.Append("SELECT " & FieldName & " FROM tbl WHERE " & FieldName & " = ?")

with

  sbQrySearch.Append($"SELECT ${FieldName} FROM tbl WHERE ${FieldName} = ?"$)

Furthermore, I am not sure there's any gain in using parameterized Select statements with sqlite on Android (though I am happy to be proved wrong), so I would rewrite the query like this

Log($"SELECT ${FieldName}  FROM tbl WHERE ${FieldName} = '${NameToSearch}'"$)

Though I understand why you did it this way: very same statement within a loop, only the parameter changes.

The added advantage of smart strings is that you can manipulate the variable directly within it. So, if I wanted to lowercase NameToSearch's value, I would do it this way:

Log($"SELECT ${FieldName}  FROM tbl WHERE ${FieldName} = '${NameToSearch.ToLowerCase}'"$)

The output wold be:

SELECT Name  FROM tbl WHERE Name = 'someone'

 

[sorex]

I prefer the old style for debugging reasons, but Erel promotes that array one since it includes the escaping and maybe other things.

Isn't the smart string for B4A 4.x+ only?

 

[cimperia]

I agree it's just a matter of personal preferences, one style is no better than the other, but personally I would never go back to the old style
Yes, it's only available for BA >= 4.x

I have a lot of complex and long SQL statements in my applications. I can format the SQL in a very readable way, without many spurious formatting characters.
Ex:

  Private Const SELECT_ALL As String = _
   $"SELECT
      id
   ,  kt_mem_deck
   ,  kt_name
   ,  kt_stack_id
   ,  kt_offset
   ,  kt_shuffle_move_order
   ,  kt_mem_tour  
   ,  kt_show_move_no  
   ,  name
   ,  stack_id
   ,  timer
   ,  time_guess
   ,  time_reveal
   ,  range_start
   ,  range_end
   ,  range_htr
   ,  suit_hearts  AS hearts
   ,  suit_diamonds AS diamonds
   ,  suit_spades  AS spades
   ,  suit_clubs  AS clubs
   ,  test_choice
   ,  order_cards
   ,  show_image
   ,  show_stack_no
   ,  show_stack_no_embedded
   ,  show_index
   ,  show_short_name
   ,  show_full_name
   ,  show_mnemonic
   ,  card_back
   ,  test_background_color
   ,  tts
   ,  pos_minus
   ,  pos_plus
   ,  pos_random  
   FROM
    v_stacktestsettings"$

 

[sorex]

yeah, it's php like

 

[cimperia]

yeah, it's php like

Is that good or bad, in your book?

 

[sorex]

Is that good or bad, in your book?

good!

you can use the old style

$mysql="insert into table (f1,f2,f3,f4) values ('".$f1."','".$f2."','".$f3."','".$f4."')";

or the way you do as

$mysql="insert into table (f1,f2,f3,f4) values ('$f1','$f2','$f3','$f4')";

or

$mysql="insert into table (
f1,
f2,
f3,
f4
)
values (
'$f1',
'$f2',
'$f3',
'$f4'
)";

for larger queries


in the end it types faster and less concat debug misery

 

[cimperia]

We agree!

Formatting wise, and I have seen a lot of different types, there are nearly as many styles as developers. I learnt mine while working for a big ORACLE/SQL shop ages ago and I've stuck with it ever since, which seems like for ever

 

[LucaMs]

My own preference is to use smart strings to build statements

You're right, but I have to "study" it and I'm lazy (and I wrote the example on the fly, to ask the question).

Using a parameterized query, as you know, I do not have to deal with apices and quotes.
Probably, this also applies to smart strings (like I said, I have to re-read that post).

Thank you

 

[cimperia]

No worry. If you're comfortable with the way you write your SQL statements, there's nothing to change. I was not , until the smart strings came along. I am now a happy camper