Announcement

Collapse
No announcement yet.

CSV - Ignoring Comma in Text

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

  • travisdh
    replied
    Thanks for the suggestions, i got lucky that i could change the delimiter

    Leave a comment:


  • Uncle Michael
    replied
    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.

    Leave a comment:


  • ITguyHS
    replied
    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.

    Leave a comment:


  • travisdh
    started a topic CSV - Ignoring Comma in Text

    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!
Working...
X