Inserting "," in textData file

[tcgoh]

Hi,

I have a text Data file with fix offset space (see below). I'm trying to insert "," into each line at a fix interval.

00111173 ROBERT T L+            222 MR  LLL2 31-07-12 2230 XX0322   04-08-12 0720
00111173 ROBERT T L+            222 MR  CCC6 11-08-12 2255 YY0334   15-08-12 0640
00222175 DEMI K B               333 MRS SSS1 03-07-12 1130          03-07-12 2359
00222175 DEMI K B               333 MRS SSS2 04-07-12 1130          04-07-12 2359

and save them into a new listgd.txt file for conversion into SQL database.

Below is my code using richString to insert "," but I'm not getting the result with "," in the new txt file and only the 1st line is save.

Sub Activity_Create(FirstTime As Boolean)
   Activity.LoadLayout("main")
   
   Dim List1 As List
   'List1 = File.ReadList(File.DirDefaultExternal, "txtdata.txt")
   List1.Initialize
   
   If File.Exists(File.DirRootExternal,"txtdata.txt") Then
           Dim Reader1 As TextReader
         If Reader1.IsInitialized = False Then
            Reader1.Initialize(File.OpenInput(File.DirRootExternal,"txtdata.txt"))
         End If
      
      Dim rs As RichString      
      Dim rsb As RichStringBuilder
      
      
      
       Dim line As String
       line=Reader1.ReadLine 
      
       rs.Initialize(line)
       rsb.Initialize
   
      rsb.Append(rs)
      rsb.Insert(9,",")
      rsb.Insert(33,",") 
      rsb.Insert(37,",") 
      rsb.Insert(42,",") 
      rsb.Insert(48,",") 
      rsb.Insert(58,",")
      rsb.Insert(64,",")
      rsb.Insert(75,",")
      rsb.Insert(85,",")
   
   
        Do While line <> Null
        
        line = rs
        
        List1.Add(line)
        line=Reader1.ReadLine
        Loop
        Reader1.Close 
   
   End If

        File.WriteList(File.DirRootExternal, "Listgd.txt", List1)
End Sub

Please help. Thanks

 

[Mahares]

Here is the best way I know to convert your text file to a comma delimited text file:
1. Open Excel on your PC, then open your original text file on the PC. It appears that it is a fixed width text file. Excel wizard will walk you through the import process. Follow wizard prompts.
2. Adjust the width of any columns and delete any unnecessary rogue ( blank) columns. Also, format your dates the way you want, like:dd-mm-yy.
3. Then, do a 'File' , 'Save as' csv file (comma delimited).
4. Save it to any name you want. Then, exit Excel without doing any more saving. You can rename it later.
5. Once it is a comma delimited, you can then import it to a SQLite database table, using RegEx.Split(",",line).
I have attached the modified text file here with the comma delimiter.
Maybe, someone has a better solution. I would applaud seeing it.

 

[mc73]

You should read all lines inside the loop. You're just reading the first line, outside of the populating loop. Then, I didn't understand why to use a richString and richStringBuilder, but I guess this has something to do with format?
Anyway, here's a version that worked for me. I've added also a listView just to see if results are ok.

'Activity module
Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.

End Sub

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    'These variables can only be accessed from this module.

Dim tempListView As ListView
End Sub

Sub Activity_Create(FirstTime As Boolean)
If File.Exists(File.DirRootExternal ,"txtdata.txt")=False Then
    File.Copy (File.DirAssets ,"txtdata.txt",File.DirDefaultExternal ,"txtdata.txt")
End If
tempListView.Initialize ("")
tempListView.Color =Colors.DarkGray 
Activity.AddView (tempListView,0,0,Activity.Width,Activity.Height )
populateList
End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub


Sub populateList
Dim list1 As List
list1.Initialize
If File.Exists(File.DirdefaultExternal,"txtdata.txt")=True Then
    Dim Reader1 As TextReader
    If Reader1.IsInitialized = False Then
        Reader1.Initialize(File.OpenInput(File.DirdefaultExternal,"txtdata.txt"))
    End If
     
     Dim rs As String        
     Dim line As String
     line=Reader1.ReadLine
     Do While line.Length >0
         
         Dim rsb As StringBuilder
        rsb.Initialize  
        rsb.Append(line)
        rsb.Insert(9,",")
        rsb.Insert(33,",") 
        rsb.Insert(37,",") 
        rsb.Insert(42,",") 
        rsb.Insert(48,",") 
        rsb.Insert(58,",")
        rsb.Insert(64,",")
        rsb.Insert(75,",")
        rsb.Insert(85,",")
        list1.Add (rsb.ToString )
        tempListView.AddSingleLine (rsb.ToString )
        Dim line As String
        line=Reader1.ReadLine

      Loop
  
      Reader1.Close 
      File.WriteList(File.DirRootExternal, "Listgd.txt", list1)
  
  Else
    Msgbox("file does not exist","error")
  End If

         
End Sub

 

[tcgoh]

Hi Mc,

Thanks for the code. Its works nicely.
Yes , I noticed the rsb read line should be inside the loop, but not sure how to "do while...". About the Richstring, I'm new to this code format and had just copy from one of the example.

Thanks again. Regards

 

[Mahares]

I went further with your revised code by adding code to remove all the trailing and leading white spaces (unless of course you want them there) from the lines and hence from the final text file to clean it up. Below is a revised sub of the code that does it. Actually, by getting rid of the unneeded spaces, you reduce the size of the file by 27%:

Sub populateList
Dim list1 As List
list1.Initialize
If File.Exists(File.DirRootExternal,"txtdata.txt")=True Then
    Dim Reader1 As TextReader
    If Reader1.IsInitialized = False Then
        Reader1.Initialize(File.OpenInput(File.DirRootExternal,"txtdata.txt"))
    End If
       
    Dim line As String
      Dim Line2 As String
    Dim rsb As StringBuilder
      Dim  CC() As String  'CC is  parsed elements content

     line=Reader1.ReadLine
     Do While line.Length >0
        rsb.Initialize  
        rsb.Append(line) 
        rsb.Insert(9,",")  
        rsb.Insert(33,",") 
        rsb.Insert(37,",")
        rsb.Insert(42,",") 
        rsb.Insert(48,",") 
        rsb.Insert(58,",")
        rsb.Insert(64,",")
        rsb.Insert(75,",")
        rsb.Insert(85,",")   
            Line2=rsb.ToString
            CC = Regex.Split(",", Line2)  'field separator is comma
            Line2=CC(0).Trim & "," & CC(1).Trim & "," & CC(2).Trim & "," & CC(3).Trim & "," & CC(4).Trim & "," & _
            CC(5).Trim & "," & CC(6).Trim & "," & CC(7).Trim & "," & CC(8).Trim   & "," & CC(9).Trim             
            list1.Add(Line2) 
        tempListView.AddSingleLine (Line2)  
        line=Reader1.ReadLine
      Loop

      Reader1.Close 
      File.WriteList(File.DirRootExternal, "Listgd.txt", list1)
  
  Else
    Msgbox("file does not exist","error")
  End If

End Sub