Announcement

Collapse
No announcement yet.

CSV - Ignoring Comma in Text

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    CSV - Ignoring Comma in Text

    Hi Everyone,

    I am trying to parse a CSV file which changes on a regular basis. I can read the file fine but every now and again one of the fields will have a comma in it which throws off my splitstring.

    My initial code is below, just wondering how I can do this so that if a comma appears within quotation marks e.g. "Test1, Inc" I can ignore that and not change my splitstring, or at least the references to array 5, 6 or 7?

    My code is shown below;

    Code:
    Dim str As String
    Dim FileName As string = "C:\HomeSeer HS3\scripts\speed.csv"
    Dim SplitStr() As String
    Dim Ref as integer
    Dim PNG as Double
    Dim DL as Double
    Dim UL as Double
    				
    str = System.IO.File.ReadLines(FileName).Last()
    		
    SplitStr = Split(str, ",")
    
    'hs.writelog("Speedtest - Ping", SplitStr(5)) 'Ping
    Ref = hs.GetDeviceRef("SPDTEST-PG")
    PNG = SplitStr(5)
    hs.SetDeviceString(ref, SplitStr(5), True) 
    hs.SetDeviceValueByRef(ref,Splitstr(5),true)
    		
    'hs.writelog("Speedtest - Download", SplitStr(6)) 'Download
    Ref = hs.GetDeviceRef("SPDTEST-DL")
    hs.SetDeviceString(ref, Math.Round(SplitStr(6)/1000000,2), True)
    hs.SetDeviceValueByRef(ref,Math.Round(Splitstr(6)/1000000,2),true)
    DL = Math.Round(Splitstr(6)/1000000,2)
    		
    'hs.writelog("Speedtest - Upload", SplitStr(7)) 'UL
    Ref = hs.GetDeviceRef("SPDTEST-UL")
    hs.SetDeviceString(ref, Math.Round(SplitStr(7)/1000000,2), True)
    hs.SetDeviceValueByRef(ref,Math.Round(Splitstr(7)/1000000,2),true)
    UL = Math.Round(Splitstr(7)/1000000,2)
    Many Thanks!
    HS3 PRO, Win10, WeatherXML, HSTouch, Pushover, UltraGCIR, Heaps of Jon00 Plugins, Just sold and about to move so very slim system.

    Facebook | Twitter | Flickr | Google+ | Website | YouTube

    #2
    You can use the VB replace function to change the commas to a space. For example:
    Dim value2 As String = value1.Replace(",", " ")
    would replace all the commas in value1 with a space and assign the result to value2.
    Fred

    HomeSeer Pro 3.0.0.548, HS3Touch, Zwave 3.0.1.252, Envisalink DSC 3.0.0.40, WeatherXML, Z-stick, HS phone, Way2Call

    Comment


      #3
      Originally posted by travisdh View Post
      I am trying to parse a CSV file which changes on a regular basis. I can read the file fine but every now and again one of the fields will have a comma in it which throws off my splitstring.. . .just wondering how I can do this so that if a comma appears within quotation marks e.g. "Test1, Inc" I can ignore that and not change my splitstring, or at least the references to array 5, 6 or 7?
      How is the csv file generated? Can you change the separator used in that process? If so, I'd choose a character that is not likely to be a part of the data - | or $, perhaps.
      Mike____________________________________________________________ __________________
      HS3 Pro Edition 3.0.0.548, NUC i3

      HW: Stargate | NX8e | CAV6.6 | Squeezebox | PCS | WGL 800RF | RFXCOM | Vantage Pro | Green-Eye | X10: XTB-232, -IIR | Edgeport/8 | Way2Call | Ecobee3 | EtherRain | Ubiquiti

      Comment


        #4
        Thanks for the suggestions, i got lucky that i could change the delimiter
        HS3 PRO, Win10, WeatherXML, HSTouch, Pushover, UltraGCIR, Heaps of Jon00 Plugins, Just sold and about to move so very slim system.

        Facebook | Twitter | Flickr | Google+ | Website | YouTube

        Comment

        Working...
        X