Android Question [RESOLVED] Different results from: string.GetBytes, File.ReadBytes and RandomAccessFile.ReadBytes

R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
Experimenting with reading large files and writing data to SQLite.
With this I noticed there are differences with the above 3 methods to get a byte array.

For example testing with this string (either in a file or as a string):

Phone¬Email¬Website¬Fax
13043¬FA512¬Pharmacy¬Community Pharmacy¬Visible¬

Reading this as string I get bytes -62 and -84 for the ¬ character and and only byte 10 for the linebreak
Reading this string from a text file (.csv) I get only byte -84 for the ¬ character and bytes 13 and 10 for the linebreak.

These are the code fragments to get the byte array:

B4X: 
'reading string:
    arrBytes = strString.GetBytes("UTF8")

'reading file, using File:
arrBytes = File.ReadBytes(tFF.strFolder, tFF.strFile)

'reading the file using RAF:
    RAF.Initialize(tFF.strFolder, tFF.strFile, True)
    lBytes = RAF.Size
    Dim arrBytes(lBytes) As Byte
    RAF.ReadBytes(arrBytes, 0, lBytes, 0)

I can work around this, but would like to understand why this happens and how I can predict what will happen.

RBS
 
Sort by date Sort by votes

MicroDrie

Well-Known Member
Licensed User
Longtime User
Here is a B4J and B4A program that takes the following steps:
  1. It loads the csv file directly from the specified URL (from the hospital overview) into "temp.csv"
  2. It replaces the separator with a tab value and writes the record to "temp2.csv"
  3. It reads the file "temp2.csv" in the database test.db in the table "Hospitals"
The program is not perfect, but it creates or overwrites a table in an specified SQLite database. In B4J the program works without any problems, in B4A the reading starts for a reason that is unclear to me. Due to the current Android restrictions on directories I could not read the result in a file exploxer. In addition, the size of the files places quite a large demand on memory. It may be worth considering a more client server-like solution in which the client only downloads a limited subset of the information to reduce the resource requirement.

You can further expand this concept to your own wishes by making the filename, database table name and strQuery structure references multiple.

Because all fields in the database table TEXT have values, you may find it easier to define the strQuery with a loop. The current method allows for this in contrast to a different format.
 

Attachments

  • NHShospitals.zip
    15.3 KB · Views: 130
Upvote 0
E

emexes

Expert
Licensed User
Last edited:
Upvote 0
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
Thanks, and will have a look at your code.

There are really 2 things to deal with:
1. Very large files. This I think I have sorted.
2. Working out somehow what the charset of the file is.
This I haven't figured out yet.

I convert from bytes to string eg like this:

B4X: 
arrData(c) = BytesToString(arrBytes, lSeparatorPos + 1, (i - 1) - lSeparatorPos, "UTF-8")

As I understand it there are files where "UTF-8" is not the right charset to use.

RBS
 
Upvote 0
E

emexes

Expert
Licensed User
RB Smissaert said:
2. Working out somehow what the charset of the file is.
This I haven't figured out yet.
Click to expand...

If this is a once-off mission done when you first use data from a new source, then use something like https://abctool.gitlab.io/encoding-detector/

If it is an ability that you want to build into your program then that is a tad harder but this will get you there 99.9% of the time:

Go with UTF-8 unless the text bytes contain any invalid UTF-8 multibyte encodings, in which case go with windows-1252

Invalid UTF-8 multibyte encodings are:
a/ bytes beginning with high bits 10 are that are not preceded by a byte beginning with high bit 1
b/ bytes beginning with high bits 11 that are not followed by a byte beginning with high bits 10

edited: to simplify
 
Last edited:
Upvote 0
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
I will need to build this in the app, so users can view or import (to DB) text files without manipulating (eg in Excel) these files themselves.
Your steps 1 to 3 will probably be sufficient to handle the files that I am dealing with so that is very helpful.
Do you have code that will take a byte array and produce the charset although it looks I can work this out myself.
I take it I have to look at all the bytes of the file, so not for example the first 1000 or so.

RBS
 
Upvote 0
E

emexes

Expert
Licensed User
RB Smissaert said:
Do you have code that will take a byte array and produce the charset
Click to expand...

I do but it's in PowerBasic.

I was thinking about it during lunch and realised that nowadays you can probably ignore the old IBM PC set anyway, and simplified the preceding post accordingly.

RB Smissaert said:
I take it I have to look at all the bytes of the file, so not for example the first 1000 or so.
Click to expand...

All would be better, but capped at the first 1000 or 10000 will do the job most-if-not-all times.

With your NHS data where the field separator is invalid UTF-8 and appears often and on each line, then cqpped will do the job all times.
 
Upvote 0
E

emexes

Expert
Licensed User
RB Smissaert said:
Do you have code that will take a byte array and produce the charset
Click to expand...

I do but it's in PowerBasic.

Now in B4X (simplified by ignoring old IBM PC IBM437) :

B4X: 
Sub DetectCharacterEncoding(TextBytes() As Byte) As String

    Dim InvalidUTF8Flag As Boolean = False
 
    Dim PreviousByte As Int    'actually just the top 2 bits
    Dim ThisByte As Int = 0    'actually just the top 2 bits

    For I = 0 To TextBytes.Length - 1
        PreviousByte = ThisByte
        ThisByte = Bit.And(TextBytes(I), 0xC0)    'top 2 bits

        If PreviousByte = 0xC0 Then    'if multibyte start
            If ThisByte <> 0x80 Then     'not followed by multibyte continuation
                InvalidUTF8Flag = True
                Exit
            End If
        else if ThisByte = 0x80 Then    'if multibyte continuation
            If PreviousByte < 0x80 Then    'not preceded by multibyte start or continuation
                InvalidUTF8Flag = True
                Exit
            End If
        End If
    Next
 
    If InvalidUTF8Flag Then
        Return "windows-1252"
    Else
        Return "UTF-8"    'or plain ASCII
    End If
 
End Sub
 
Last edited:
Upvote 0
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
Thanks for that and your code picks out rightly that SCL.csv file (the one with triple "C") as windows-1252.

Would it be possible to do this:

B4X: 
Do While True
    Dim L As String = TR.ReadLine
    If L = Null Then
        Exit
    End If

    L = L.Replace(Chr(65533), Chr(9))    'invalid UTF-8 translates to Chr(65533); change those to tabs

In my byte arrays rather than on strings.

I am rewriting my CSV2List Sub, so it always has a start and end byte index (if -1 will be first and last) and also make it work with a byte array directly
rather than with folder and file strings. All to make it simpler and smaller subs.

RBS
 
Upvote 0

MicroDrie

Well-Known Member
Licensed User
Longtime User
What I don't understand is why you want to use a byte array to read a CSV text file that, according to the CVS file by default, can only contain text characters. Of course the advantage of a standard is that everyone can create their own and therefore different standard with exceptions to the rule. You refer to the NHS files, but they are purely text. A text-only file can of course start with a number of bytes written before the definitions/signature of the application of that file or a different character code. A base64 image file may be included, but those image bytes are still written as base64 text characters and can be read as a text string by your app and written to the text-based SQLite database. Why do you want to use a bytre array?
 
Upvote 0
E

emexes

Expert
Licensed User

Yes, but "manually" eg:

B4X: 
Dim BA() As Byte = bc.StringToBytes("first¬second¬third", "windows-1252")

Log("Before" & TAB & bc.StringFromBytes(BA, "windows-1252"))

Dim ChangeFrom As Byte = Asc("¬")
Dim ChangeTo As Byte = Asc(TAB)

For I = 0 To BA.Length - 1
    If BA(I) = ChangeFrom Then BA(I) = ChangeTo    'standardise field separator to TAB
Next

Log("After" & TAB & bc.StringFromBytes(BA, "windows-1252"))
 
Upvote 0
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
I started to use byte arrays as it was then easy to read a file (with RandomAccessFile) in small parts, to deal with large files.
The other reason was that I was dealing with misformed files, for example a trailing comma (as field delimiter) when there shouldn't be one.
The last thing to mention is that using a byte array just gives you full control, admittedly at the cost of extra complexity.

I had a go with TextReader to read the file line by line, but for some reason that still gave me out of memory errors.

In any case I have a feeling that I am not far off from making it work OK, so will persevere for now.

RBS
 
Upvote 0
E

emexes

Expert
Licensed User
RB Smissaert said:
I had a go with TextReader to read the file line by line, but for some reason that still gave me out of memory errors.
Click to expand...

Was this only with B4A? I had no problems using TextReader.ReadLine in B4J on the NHS files.

Perhaps the problem is caused by .ReadLine expecting *valid* UTF-8 and choking on the -84 byte (which is *not* valid UTF-8).
 
Last edited:
Upvote 0
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
emexes said:
Was this only with B4A? I had no problems using TextReader.ReadLine in B4J on the NHS files.

Perhaps the problem is caused by .ReadLine expecting *valid* UTF-8 and choking on the -84 byte (which is *not* valid UTF-8).
Click to expand...
Yes this was with B4A, not using anything else.

RBS
 
Upvote 0
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
emexes said:
Was this only with B4A? I had no problems using TextReader.ReadLine in B4J on the NHS files.

Perhaps the problem is caused by .ReadLine expecting *valid* UTF-8 and choking on the -84 byte (which is *not* valid UTF-8).
Click to expand...
I can see now that TextReader has Initialize2, which allows specifying a characterset. Not used that before and
will have a look at that.

RBS
 
Upvote 0
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
emexes said:
Lol I am blind as a bat sometimes.



Definitely worth a shot.

TextReader.ReadLine should be much easier than manually doing it via byte arrays.

Although... it's still a mild worry if TextReader.ReadLine can choke on invalid input.
Click to expand...
Specifying the charset does seem to work OK. Tested with that TCL.csv file:

B4X: 
Sub TestTextReader
    
    Dim oTR As TextReader
    Dim lBytes As Long
    Dim strLine As String
    
    Dim rs As ResumableSub = PickFile("Parse a .csv or .txt file", _
                                      "Pick a .csv or .txt file or a folder", _
                                       File.DirRootExternal & "/PhonePats", _
                                       Array As String("csv", "txt"), _
                                       True)
    Wait For (rs) Complete (tFF As tFolderAndFile)
    
    RAF.Initialize(tFF.strFolder, tFF.strFile, True)
    lBytes = 10000 'enough at least in this file to give me characterset
    Dim arrBytes(lBytes) As Byte
    RAF.ReadBytes(arrBytes, 0, lBytes, 0)
    
    Dim rs1 As ResumableSub  = DetectCharacterEncoding(arrBytes)
    Wait For (rs1) Complete (strCharset As String)
    
    Log(strCharset) '>>   windows-1252
    
    oTR.Initialize2(File.OpenInput(tFF.strFolder, tFF.strFile), strCharset)
    
    Do While True
        strLine = oTR.ReadLine
        If strLine = Null Then Exit
        If strLine.StartsWith("8329479") Then 'this is the line with Complete Contemporary Care Triple "C" etc.
            Log(strLine)
        End If
    Loop
    
    oTR.Close
    
End Sub

Will give me this:

8329479¬1-4207346477¬Care homes and care at home¬UNKNOWN¬Visible¬False¬Complete Contemporary Care Triple "C" Ltd¬Suite 1, Regent business centre¬16 to 20 Regent Street¬¬Leeds¬¬LS2 7QA¬53.801853179931641¬-1.5328665971755981¬1-4111445769¬Complete Contemporary Care. Triple "C". Ltd¬07733800660¬¬¬¬

And that looks all fine.

RBS
 
Upvote 0
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
Fully tested now with TextReader and all seems OK and good performance as well:

B4X: 
Sub CountFieldsInTextLine(strCSVLine As String, strSeparator As String, strEncloser As String) As Int
    
    Dim c As Int
    Dim iEndIndex As Int
    Dim bInsideQuotes As Boolean = False
        
    For iEndIndex = 0 To strCSVLine.Length - 1
        If strCSVLine.CharAt(iEndIndex) = strEncloser Then
            bInsideQuotes = bInsideQuotes = False
        Else
            If strCSVLine.CharAt(iEndIndex) = strSeparator Then
                If bInsideQuotes = False Then
                    c = c + 1
                End If
            End If
        End If
    Next
    
    Return c + 1
    
End Sub

Sub ParseCSVLine(strCSVLine As String, iCols As Int, strSeparator As String, strEncloser As String) As String()
    
    Dim c As Int
    Dim arrValues(iCols) As String
    Dim iStartIndex As Int
    Dim iEndIndex As Int
    Dim bInsideQuotes As Boolean = False
        
    For iEndIndex = 0 To strCSVLine.Length - 1
        If strCSVLine.CharAt(iEndIndex) = strEncloser Then
            bInsideQuotes = bInsideQuotes = False
        Else
            If strCSVLine.CharAt(iEndIndex) = strSeparator Then
                If bInsideQuotes = False Then
                    arrValues(c) = strCSVLine.SubString2(iStartIndex, iEndIndex)
                    iStartIndex = iEndIndex + 1
                    c = c + 1
                End If
            End If
        End If
    Next
    
    arrValues(c) = strCSVLine.SubString2(iStartIndex, iEndIndex )
    
    Return arrValues
    
End Sub

Sub TestTextReader
    
    Dim c As Int 'ignore
    Dim oTR As TextReader
    Dim lBytes As Long
    Dim strLine As String
    Dim iCols As Int
    Dim lstCSV As List
    
    Dim rs As ResumableSub = PickFile("Parse a .csv or .txt file", _
                                      "Pick a .csv or .txt file or a folder", _
                                       File.DirRootExternal & "/PhonePats", _
                                       Array As String("csv", "txt"), _
                                       True)
    Wait For (rs) Complete (tFF As tFolderAndFile)
    
    StartSW(0)
    
    RAF.Initialize(tFF.strFolder, tFF.strFile, True)
    lBytes = 1000 'this is enough at least in case of the SCL.csv file
    Dim arrBytes(lBytes) As Byte
    RAF.ReadBytes(arrBytes, 0, lBytes, 0)
    
    Dim rs1 As ResumableSub  = DetectCharacterEncoding(arrBytes)
    Wait For (rs1) Complete (strCharset As String)
    
    Log(strCharset) '>>   windows-1252
    
    oTR.Initialize2(File.OpenInput(tFF.strFolder, tFF.strFile), strCharset)
    lstCSV.Initialize
    
    'first line will be column names
    strLine = oTR.ReadLine
    iCols = CountFieldsInTextLine(strLine, "¬", Chr(34))
    Dim arrColumnNames() As String = ParseCSVLine(strLine, iCols, "¬", Chr(34)) 'ignore, will be used to create SQLite table

    Do While True
        strLine = oTR.ReadLine
        If strLine = Null Then Exit
        Dim arr() As String = ParseCSVLine(strLine, iCols, "¬", Chr(34))
'        If arr(0) = "8329479" Then
'            For c = 0 To arr.Length - 1
'                Log(arr(c))
'            Next
'        End If
        lstCSV.Add(arr) 'will need to move list to DB if gets too big and Dim a new list
    Loop
    Log("lstCSV.Size: " & lstCSV.Size & ", " & FormatMilliSecs(StopSW(0), False)) 'lstCSV.Size: 28269, 110 milli-seconds
    
    oTR.Close
        
End Sub

That troublesome line with comes out fine as well:

8329479
1-4207346477
Care homes and care at home
UNKNOWN
Visible
False
Complete Contemporary Care Triple "C" Ltd
Suite 1, Regent business centre
16 to 20 Regent Street
Leeds
LS2 7QA
53.801853179931641
-1.5328665971755981
1-4111445769
Complete Contemporary Care. Triple "C". Ltd
07733800660

Not sure it is all solved as I specified ¬ as the value separator and Chr(34) as the value encloser and not sure yet how to determine these 2.
Still, at least using TextReader makes it all simpler.

RBS
 
Upvote 0
E

emexes

Expert
Licensed User
RB Smissaert said:
Not sure it is all solved as I specified ¬ as the value separator and Chr(34) as the value encloser and not sure yet how to determine these 2.
Click to expand...

As in: how to automatically determine, without human help?

That'd be an interesting puzzle to solve.

Although: even if you did solve it, the computer then has to know what to do with each column... like, how is it going handle phone number vs fax number?
 
Last edited:
Upvote 0
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
Probably will need user input, that is for them to specify the column separator string at least, although if there are field names in row 1 analysing then
analysing them will often make it clear what that should be, especially if there are no characters past 127.

Handling the colums I have solved with a simple function that takes a list of arrays and produces the data types, that is text, integer, long or double.

RBS
 
Upvote 0
You must log in or register to reply here.

Similar Threads

R
  • Article
Android Code Snippet Get data type from 1D string array
2
Replies
23
Views
3K
RB Smissaert
R
F
  • Question
Android Question convert byte to string
Replies
11
Views
5K
FormCommander
F
  • Question
Android Question Reading encrypted string and decoding it into a string array
Replies
8
Views
2K
Stephen A Wolfe
  • Article
Android Code Snippet [B4X] Join arrays of bytes
Replies
0
Views
6K
Erel
  • Question
Android Question string to Byte Array
Replies
7
Views
6K
KMatle
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…