Results 1 to 13 of 13

Thread: [RESOLVED] Issue with parsing

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Feb 2015
    Location
    USA, New Jersey
    Posts
    23

    Resolved [RESOLVED] Issue with parsing

    Any help will be appreciated.
    After remove the first line I could not figure out, should I use select case, maybe InStr with vbBinaryCompare ...

    This is the string I need to parse.

    From 9:30AM until 4:00PM - Only show time 11:30:54 AM,

    symbol,latestTime,open,high,low,close,
    PSEC,11:30:54 AM,6.63,6.64,6.61,6.61
    ' This one works fine

    After 4:00 PM - show March 15, 2019,
    symbol,latestTime,open,high,low,close,
    PSEC,"March 15,2019",6.63,6.64,6.61,6.61,

    Code:
     Function ParseStockQuote&(Main$, tStockDetails As StockDetails)
    tMain$ = Main$
    On Error Resume Next 'GoTo ParseError
    With tStockDetails
      DoEvents
           '******** Remove first line ************
      Call StripFirstLine$(tMain$) ' to remove first line
      Debug.Print tMain$
      
           Dim CSV$(1 To 6)
      For n& = 1 To 6
        CSV$(n&) = SubtractString$(tMain$, ",")
      
       Next
       .Ticker = CSV$(1) 'PSEC
       .LatestTime = CSV$(2) 'Time
        
     If LatestTime = "N/A" Then 'no date no good
        ParseStockQuote& = 2 'not a valid date resubmit ? 
        Exit Function
      
     End If
     
     .Open = Val(CSV$(3))          
     .High = Val(CSV$(4))         
     .Low = Val(CSV$(5))           
     .Close = Val(CSV$(6))
     
     'If InStr(tTime$, "chr$(58)", vbBinaryCompare) = 0 Then  'I m trying use
     
      
     Else
       
        Exit Function
                                                               
      End Function
    Last edited by Tony.; Mar 18th, 2019 at 10:42 AM.

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    Re: Issue with parsing

    What's the supposed Result you're expecting?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Feb 2015
    Location
    USA, New Jersey
    Posts
    23

    Re: Issue with parsing

    Hi, Thanks for respond.
    The parse should be CSV1 = symbol, CSV2 = time, CSV3 =Open, CSV4 =High
    After 4:00PM , CSV1 is=Symbol, CSV2 is March15, CSV3 Is 2019, needs to be =open , CSV4 needs to be =high and is = low

    Tony

  4. #4
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Issue with parsing

    Quote Originally Posted by Tony. View Post
    Hi, Thanks for respond.
    The parse should be CSV1 = symbol, CSV2 = time, CSV3 =Open, CSV4 =High
    After 4:00PM , CSV1 is=Symbol, CSV2 is March15, CSV3 Is 2019, needs to be =open , CSV4 needs to be =high and is = low
    You will need a decent CSV-Parser, which interprets "quoted text cells" within the CSV-Stream correctly.

    One easy way to accomplish that is, to use a Helper-Lib which has something like that built-in.
    The example below can be pasted into an empty Form (and will work with a project-reference to vbRichClient5):
    Code:
    Option Explicit
    
    Implements ICSVCallback
    Private CSV As cCSV, ResultRows As cArrayList
    
    Private Sub Form_Load()
    Const S As String = "symbol,latestTime,open,high,low,close," & vbLf & _
                        "PSEC,11:30:54 AM,6.63,6.64,6.61,6.61," & vbLf & _
                        "PSEC,""March 15,2019"",6.63,6.64,6.61,6.61,"
    
      Set ResultRows = New_c.ArrayList(vbVariant) 'create a result-gathering instance
      Set CSV = New_c.CSV 'create the CSV-parsing-instance
          CSV.ParseBytes New_c.Crypt.VBStringToUTF8(S), Me 'and parse the UTF8-ByteArray of the CSV-Input
      
      'the above was triggering the ICSVCallback_NewValue and filling the ResultRows-Array, ...
      'now let's print what we gathered in the ICSVCallback-Routine
      Dim Row As Long, Col As Long, ColArr()
      For Row = 0 To ResultRows.Count - 1
          ColArr = ResultRows(Row)
          For Col = 0 To UBound(ColArr): Debug.Print Row, Col, ColArr(Col): Next
      Next
    End Sub
    
    Private Function ICSVCallback_NewValue(ByVal RowNr As Long, ByVal ColNr As Long, B() As Byte, ByVal BValStartPos As Long, ByVal BValLen As Long) As Long
      Static ColArr()
      If RowNr = 0 Then ReDim Preserve ColArr(ColNr): Exit Function
     
      ColArr(ColNr) = CSV.GetStringValue(B, BValStartPos, BValLen)
      If ColNr = UBound(ColArr) Then ResultRows.Add ColArr
    End Function
    The above code will print out (from the 3 Rows of CSV-Input which were similar to what you've posted)
    Code:
    RowIdx    ColIdx     Text-Cell-Value
     0             0            PSEC
     0             1            11:30:54 AM
     0             2            6.63
     0             3            6.64
     0             4            6.61
     0             5            6.61
     0             6            
     1             0            PSEC
     1             1            March 15,2019
     1             2            6.63
     1             3            6.64
     1             4            6.61
     1             5            6.61
     1             6
    HTH

    Olaf

  5. #5
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Issue with parsing

    You do need to deal with the use of a quotation mark text-qualifier. You also have ragged use of an empty extra column. Is your source data really this shabby?

    But your real issue would appear to be special handling you want to perform on the date-time column. That's a bit easier if you parse and convert column data to strongly-typed values instead of trying to juggle String values.

    The data appears to be in either U.S. English format or possibly it is meant to be in Earth Standard ("Invariant") interchange format. Those are roughly identical, making it easy enough if the user has matching locale settings. But we can do better and parse to strong types using the Invariant Locale explicitly.

    Sample data:

    Code:
    symbol,latestTime,open,high,low,close,
    PSEC,11:30:54 AM,6.63,6.64,6.61,6.61
    PSEC,"March 15,2019",6.63,6.64,6.61,6.61,
    PSEC,"March 15,2019 8 PM",1.10,2.21,3.32,4.43
    PSEC,"March 15,2019 9:45 AM",10.0,10.0,10.0,10.0,
    PSEC,"March 15,2019 4:01 PM",6,6,6,6
    PSEC,"March 15,2019 9:29:59 AM",13,13,13,13,
    My results:

    Name:  sshot.png
Views: 311
Size:  3.5 KB

    Code excerpt:

    Code:
            'Data rows:
            Do Until Start = 0
                Row = Row + 1
                V = ParseLine(Start, Data)
                If Row > .Rows - 1 Then .Rows = Row + 1
                For Col = 0 To UBound(V)
                    If Col <= 5 Then
                        'Convert from String values to DataTypes:
                        V(Col) = VConvert.Convert(V(Col), DataTypes(Col))
                        If Col = 0 Then
                            .TextMatrix(Row, Col + 1) = V(Col)
                        ElseIf Col = 1 Then
                            TimeOnly = CDate(CDbl(V(Col)) - Int(CDbl(V(Col))))
                            If #9:30:00 AM# <= TimeOnly And TimeOnly <= #4:00:00 PM# Then
                                V(Col) = TimeOnly
                            End If
                            .TextMatrix(Row, Col + 1) = FormatDateTime(V(Col))
                        Else
                            .TextMatrix(Row, Col + 1) = FormatCurrency(V(Col))
                        End If
                    End If
                Next
            Loop
    V is a Variant, VConvert is a Class included in the Project archive. The ParseLine() function is in Form1.


    But this is just a guess. You haven't explained the actual problem very well so I may have wildly misinterpreted what you are trying to accomplish. And real data might be even more raggedy than the small examples you gave, so my program might fail on the real thing.
    Attached Files Attached Files

  6. #6
    PowerPoster
    Join Date
    Jun 2001
    Location
    Trafalgar, IN
    Posts
    4,141

    Re: Issue with parsing

    It's been a while since I've played in VB6 but my gut reaction is why not ADO? I may be a little off on the syntax but it should be pretty close. Of course you still need to declare and instantiate the connection and recordset objects.
    Code:
    Dim strFilePath as String
    Dim fileName as String
    
    strFilePath = "c:\Documents"
    fileName = "sample.csv"
    
    'Set the database connection
    objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                       "Data Source=" & strFilePath & ";" & _
                       "Extended Properties=""text;HDR=YES;FMT=Delimited"
    
    'Query from the file
    objRecordset.Open "SELECT * FROM " & fileName, _
              objConnection, adOpenStatic, adLockOptimistic, adCmdText
    
    'Loop through the records
    Do Until objRecordset.EOF
        Debug.Print objRecordset.Fields.Item("symbol")
        Debug.Print objRecordset.Fields.Item("latestTime")
        Debug.Print objRecordset.Fields.Item("open")
        Debug.Print objRecordset.Fields.Item("high")
        Debug.Print objRecordset.Fields.Item("low")
        Debug.Print objRecordset.Fields.Item("close")
        objRecordset.MoveNext
    Loop

  7. #7
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Issue with parsing

    Quote Originally Posted by MarkT View Post
    It's been a while since I've played in VB6 but my gut reaction is why not ADO? I may be a little off on the syntax but it should be pretty close. Of course you still need to declare and instantiate the connection and recordset objects.
    I'm not sure the data is in a disk file, and whether you use Jet's Text IISAM or the ODBC Desktop Text Driver (whether via ADO or DAO/RDS) you'd need to write the data to disk first. I got the idea this data was coming from an HTTP response or something.

    The Text IISAM and Text Driver also risk running afoul of localization issues. For data interchange decimal point is "." and the grouping separator is "," and month names are in English, dates are mm/dd/yyyy, and so on. You can override/force some of those things via an explicit schema.ini file or custom registry settings but month names and the like are not as easy to deal with. Things like Boolean values as text "True" and "False" are issues as well.


    But then you have the issue which seems to be the real question here: fiddling with the date-time values as requested. I'm not saying that one makes a lot of sense to me, but it seemed to be the only odd thing asked for but not addressed.

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Feb 2015
    Location
    USA, New Jersey
    Posts
    23

    Re: Issue with parsing

    Hi,thank you to all for the help.
    I attach some pictures so you can have an idea of the program. Meanwhile I will try see if I can implement some of the suggesting code.
    The link I'm pulling the quotes is https://api.iextrading.com/1.0/stock...t,?&format=csv
    I will update, thanks.
    Tony
    Last edited by Tony.; Mar 16th, 2019 at 04:35 PM.

  9. #9
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Issue with parsing

    Well at least it looks cleaner, no trailing "," on the rows of text. More columns though.

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Feb 2015
    Location
    USA, New Jersey
    Posts
    23

    Re: Issue with parsing

    Yes , but if you notice the date of the details is from 2015, since yahoo stop the free quotes. For the details I"m try get only the Name, 52week high and week52Low.
    I'm using 3 URLs to pull the data, one for charts, one for Details and one for daily update during the day.
    The problem is the quote from 9:30AM until 4:00PM ( time the market is open) have only the time , after 4:00 have month and year, let's see if I can solve the issue.
    Thanks Tony

  11. #11
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Issue with parsing

    Found a CSV parsing bug and fixed it, "inverted" rows and columns for cleaner display of a single data row since we have more fields now:

    Name:  sshot.png
Views: 253
Size:  3.6 KB


    But can you clarify what the real issue is? Is it fiddling with the date-time value as shown in my examples?

    Oops, too slow. I'll let it go unless you have another question.
    Attached Files Attached Files

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Feb 2015
    Location
    USA, New Jersey
    Posts
    23

    Re: Issue with parsing

    Yes, the issue is with the date and time, like in your picture above
    symbol,latestTime,open,high,low,latestPrice,latestVolume,change,changePercent
    MO,"March 15, 2019",56.84,57.07,56.01,56.75,24537901,0.35,0.00621, From symbol to open 3 commas MO, March 15, 2019, after 4:00PM this way the 3 place is 2019
    MO,"10:23:30 AM",56.84,57.07,56.01,56.75,24537901,0.35,0.00621, From symbol to open 2 commas MO, 10:23:30 AM, before 4:00PM this way the 3 place is open
    The original program is in vb4 I converted to vb5, got an error when try open in vb6. I have to open your files in vb6 and try adapt I will try .
    Thanks
    Last edited by Tony.; Mar 16th, 2019 at 06:55 PM.

  13. #13

    Thread Starter
    Junior Member
    Join Date
    Feb 2015
    Location
    USA, New Jersey
    Posts
    23

    Resolved Re: Issue with parsing

    Hi, the problem was resolved, I use a" If InStr(CSV(2), ":") <> 0 Then " This did a trick, . Is not perfect but works. What takes a minute for an expert, takes a day or more for a newbie.
    I'm very grateful everyone who tried to help me, especialy to dilettante, thanks for all the help.
    Code:
    Function ParseStockQuote&(Main$, tStockDetails As StockDetails)
    
        Dim tLastDate As Date
        tMain$ = Main$
        On Error Resume Next 'GoTo ParseError
        With tStockDetails
      DoEvents
      '******** Remove first line ************
          Call StripHTTPheader$(tMain$) ' to remove first line
          tMain$ = RemoveChars$(tMain$, Chr$(34)) ' remove all ""
      
             'convert the CSV string into an array of strings
          Dim CSV$(1 To 10)
            For n& = 1 To 10
              CSV$(n&) = SubtractString$(tMain$, ",")
      
      Next
        .Ticker = CSV$(1) '"^OEX"
                  If InStr(CSV(2), ":") <> 0 Then
            tLastDate = Date & " " & CSV(2)
             .LastTraded = tLastDate  'Date & Time
            If CSV(2) = "N/A" Then
        ParseStockQuote& = 3 'exit no good no date for symbol prolly
        Exit Function
        End If
        .Open = Val(CSV$(3))          '10085
        .High = Val(CSV$(4))          '10085
        .Low = Val(CSV$(5))           '9651
        .LastPrice = Val(CSV$(6))     '9975
        .Volume = Val(CSV$(7))        'N/A
        .PointChange = Val(CSV$(8))   '-114.69
        '.PercentChange = Val(CSV$(9)) '-1.14%
        .PercentChange = tMain$ * 100 '-1.14%
     
         Else
        Dim DateT As Date
        DateT = CSV(2) & CSV(3)  'March 17 2019
        DateT = Format(DateT, "mm/dd/yyyy")
        .LastTraded = DateT '03/17/2019
        'Debug.Print .LastTraded
        If .LastTraded = "N/A" Then
        .Open = Val(CSV$(4))          '10085
        .High = Val(CSV$(5))          '10085
        .Low = Val(CSV$(6))           '9651
        .LastPrice = Val(CSV$(7))     '9975
        .Volume = Val(CSV$(8))        'N/A
        .PointChange = Val(CSV$(9))   '-114.69
        '.PercentChange = Val(CSV$(9)) '-1.14%
        .PercentChange = tMain$ * 100 '-1.1
    
    
        Else
       .LastTraded = 0
        ParseStockQuote& = 2 'not a valid date resubmit ?
                    End If
           Exit Function
           End If
       End With
    End Function
    Last edited by Tony.; Mar 18th, 2019 at 10:58 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width