none
CSV Files? RRS feed

  • Question

  • I'm still new to SB and in playing with it, I've come to the conclusion that there is no easy way to read data from a csv file where the separated items vary in length.
    Text.GetSubText() won't work. Am I right?

    Thanks,


    Tuesday, August 11, 2009 9:06 PM

Answers

  • Yes, but you can write a subroutine to parse a line and create an array of all the comma separated variables:

    Here's a starter idea.

    line = "this , is,a,csv separated, list,12435.,"

    parseLine()
    For i = 1 To Array.GetItemCount(fields)
      TextWindow.WriteLine(fields[i])
    EndFor

    Sub parseLine
      fields = ""
      i = 0
      lineCopy = line
      pos = Text.GetIndexOf(lineCopy,",")
      While (pos > 0)
        i = i+1
        fields[i] = Text.GetSubText(lineCopy,1,pos-1)
        lineCopy = Text.GetSubTextToEnd(lineCopy,pos+1)
        pos = Text.GetIndexOf(lineCopy,",")
      EndWhile
      If (Text.GetLength(lineCopy) > 0) Then
        i = i+1
        fields[i] = lineCopy
      EndIf
    EndSub
    • Proposed as answer by dhester Tuesday, August 11, 2009 10:03 PM
    • Marked as answer by litdevModerator Friday, August 14, 2009 8:30 PM
    Tuesday, August 11, 2009 9:31 PM
    Moderator

All replies

  • Yes, but you can write a subroutine to parse a line and create an array of all the comma separated variables:

    Here's a starter idea.

    line = "this , is,a,csv separated, list,12435.,"

    parseLine()
    For i = 1 To Array.GetItemCount(fields)
      TextWindow.WriteLine(fields[i])
    EndFor

    Sub parseLine
      fields = ""
      i = 0
      lineCopy = line
      pos = Text.GetIndexOf(lineCopy,",")
      While (pos > 0)
        i = i+1
        fields[i] = Text.GetSubText(lineCopy,1,pos-1)
        lineCopy = Text.GetSubTextToEnd(lineCopy,pos+1)
        pos = Text.GetIndexOf(lineCopy,",")
      EndWhile
      If (Text.GetLength(lineCopy) > 0) Then
        i = i+1
        fields[i] = lineCopy
      EndIf
    EndSub
    • Proposed as answer by dhester Tuesday, August 11, 2009 10:03 PM
    • Marked as answer by litdevModerator Friday, August 14, 2009 8:30 PM
    Tuesday, August 11, 2009 9:31 PM
    Moderator
  • I have added the part to read a csv file just change the c:\book1.csv to point to your file.

    line=File.ReadContents("c:\Book1.csv")
    

    If line = "" then
      TextWindow.WriteLine("No such file")
    endif

    parseLine() For i = 1 To Array.GetItemCount(fields) TextWindow.WriteLine(fields[i]) EndFor Sub parseLine fields = "" i = 0 lineCopy = line pos = Text.GetIndexOf(lineCopy,",") While (pos > 0) i = i+1 fields[i] = Text.GetSubText(lineCopy,1,pos-1) lineCopy = Text.GetSubTextToEnd(lineCopy,pos+1) pos = Text.GetIndexOf(lineCopy,",") EndWhile If (Text.GetLength(lineCopy) > 0) Then i = i+1 fields[i] = lineCopy EndIf EndSub
    Tuesday, August 11, 2009 10:05 PM
  • Thanks for the reply.  As I said, "It ain't easy."

    I'll have to study your code, as a novice it'll take me a while to figure out.

    Thanks again.

    Bill

    Wednesday, August 12, 2009 4:25 PM
  • Some comments on the code:

    Try adding TextWindow.Writeline commands to see the value of data e.g. pos and lineCopy.

    'This is just a sample line that could be read from a file, either one line at a time or the whole file
    'Probably easier to do it a line at a time since there is no detection of 'end of line' in the code below.

    line = "this , is,a,csv separated, list,12435.,"

    'This calls the subroutine to parse the line and put the csv fields into an array called 'fields'

    parseLine()
    'This just prints each csv field on a separate line in the TextWindow
    For i = 1 To Array.GetItemCount(fields)
      TextWindow.WriteLine(fields[i])
    EndFor

    'Now the main subroutine
    Sub parseLine
      'First initialise the array 'fields' to be empty
      fields = ""
      'Initialise the number of fields found to 0
      i = 0
      'Take a copy of the line to work with because it will be changed by following code
      lineCopy = line
      'Find the index of the first character inside lineCopy that is a comma - for the test string this is 6 : 'this , ...', the first ',' is at position 6
      pos = Text.GetIndexOf(lineCopy,",")
      'Loop over more than one possible ',' inside the string lineCopy - it is done if there is at least 1 comma (pos > 0)
      While (pos > 0)
        'We have found a comma at position 'pos' so increment our counter of fields 'i'
        i = i+1
        'Set the current found field to the array 'fields' starting at postion 1 and length 'pos-1' - everything before the comma
        fields[i] = Text.GetSubText(lineCopy,1,pos-1)
        'Modify the working string 'lineCopy' to remove the current field and comma - i.e. keep every after the comma (pos+1 to the end of the string'
        lineCopy = Text.GetSubTextToEnd(lineCopy,pos+1)
        'check if we have any commas in the new lineCopy string
        pos = Text.GetIndexOf(lineCopy,",")
        'Continue the while loop recording the fields and moving through the lineCopy string deleting fields as we set them in array 'fields'
      EndWhile
      'No more commas in lineCopy
      'Perhaps the last character was a comma and then lineCopy will be empty and we have found all of the 'fields'
      'If lineCopy still contains data then it is the final field - note this would also detect a line that had no commas
      If (Text.GetLength(lineCopy) > 0) Then
        'This is the last field if there is still data in lineCopy after removing all the fields to the left of the last comma
        'Just increment the field counter and set it in the array 'fields'
        i = i+1
        fields[i] = lineCopy
      EndIf
    EndSub

    Wednesday, August 12, 2009 6:23 PM
    Moderator
  • I would like to once again like say a BIG THANKS for your help. It allowed me to come up with my own version that works very well.  




    Friday, August 14, 2009 8:10 PM
  • Here are two other ways to do this:



    http://msmvps.com/blogs/deborahk/archive/2009/08/25/reading-comma-delimited-files-textfieldparser.aspx

    http://msmvps.com/blogs/deborahk/archive/2009/08/25/reading-comma-delimited-files.aspx

    Hope this  helps.

    www.insteptech.com ; msmvps.com/blogs/deborahk
    We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!
    Sunday, August 30, 2009 8:01 PM