CSV - Ignoring Comma in Text

[travisdh]

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;

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!

[ITguyHS]

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.

[Uncle Michael]

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.

[travisdh]

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