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

LucaMs · Sep 8, 2015

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:

B4X:

' 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 · Sep 8, 2015

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 · Sep 8, 2015

B4X:

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

works too.

I can confirm the error you got with your code

B4X:

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

B4X:

    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 · Sep 8, 2015

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

B4X:

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

best regards
stefan

cimperia · Sep 8, 2015

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 · Sep 9, 2015

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 · Sep 9, 2015

Thank you all.

sorex said:
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 · Sep 9, 2015

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 · Sep 9, 2015

LucaMs said:
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 · Sep 9, 2015

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

B4X:

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

with

B4X:

  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

B4X:

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:

B4X:

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

The output wold be:

B4X:

SELECT Name  FROM tbl WHERE Name = 'someone'

sorex · Sep 9, 2015

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 · Sep 9, 2015

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:

B4X:

  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 · Sep 9, 2015

yeah, it's php like

cimperia · Sep 9, 2015

sorex said:
yeah, it's php like

Is that good or bad, in your book?

sorex · Sep 9, 2015

cimperia said:
Is that good or bad, in your book?

good!

you can use the old style

B4X:

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

or the way you do as

B4X:

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

or

B4X:

$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 · Sep 9, 2015

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 · Sep 9, 2015

cimperia said:
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 · Sep 9, 2015

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

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

LucaMs

Expert

Attachments

sorex

Expert

DonManfred

Expert

eurojam

Well-Known Member

cimperia

Active Member

sorex

Expert

LucaMs

Expert

LucaMs

Expert

sorex

Expert

cimperia

Active Member

sorex

Expert

cimperia

Active Member

sorex

Expert

cimperia

Active Member

sorex

Expert

cimperia

Active Member

LucaMs

Expert

cimperia

Active Member

Similar Threads

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

Expert

Attachments

Expert

Expert

Well-Known Member

Active Member

Expert

Expert

Expert

Expert

Active Member

Expert

Active Member

Expert

Active Member

Expert

Active Member

Expert

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency