Recently i was asked to read from a .csv file and populate metadata into sharepoint list items.There i stumbled upon a annoying thing that is values separated by commas inside double quotes which should be considered as asingle element
which means i had to write code to ignore values separeted commas inside double quotes and this is how did that.i hope it will help others and if somebody knows better way of doing it,then share the code with me as well
Here it is:
read line by line from csv file
line = fs.ReadLine()
Dim Array(30) As String
Dim splitter() As Char = {","}
Dim chars As Char() = line.ToCharArray()
Dim j As Integer = 0
Dim flag As Boolean = False
Dim word As Char() = Nothing
For i = 0 To chars.Length - 1
If Not flag Then
If chars(i) = ControlChars.Quote Then
flag = True
Continue For
End If
If chars(i) <> "," And i <> chars.Length - 1 Then
If Not flag Then
word += chars(i)
End If
ElseIf i = chars.Length - 1 Then
Try
word += chars(i)
Array(j) = word
Catch ex As Exception
End Try
Else
Try
Array(j) = word
word = Nothing
Catch ex As Exception
End Try
j += 1
End If
Else
If (i + 1) < chars.Length - 1 And chars(i + 1) <> ControlChars.Quote Then
word += chars(i)
Continue For
Else
If chars(i + 1) = ControlChars.Quote Then
word += chars(i)
End If
flag = False
i += 2
End If
Try
Array(j) = word
word = Nothing
Catch ex As Exception
'Array(j) = ""
End Try
j += 1
End If
Next
After this start populating like Dim names As String = Array(2) etc
thank you
which means i had to write code to ignore values separeted commas inside double quotes and this is how did that.i hope it will help others and if somebody knows better way of doing it,then share the code with me as well
Here it is:
read line by line from csv file
line = fs.ReadLine()
Dim Array(30) As String
Dim splitter() As Char = {","}
Dim chars As Char() = line.ToCharArray()
Dim j As Integer = 0
Dim flag As Boolean = False
Dim word As Char() = Nothing
For i = 0 To chars.Length - 1
If Not flag Then
If chars(i) = ControlChars.Quote Then
flag = True
Continue For
End If
If chars(i) <> "," And i <> chars.Length - 1 Then
If Not flag Then
word += chars(i)
End If
ElseIf i = chars.Length - 1 Then
Try
word += chars(i)
Array(j) = word
Catch ex As Exception
End Try
Else
Try
Array(j) = word
word = Nothing
Catch ex As Exception
End Try
j += 1
End If
Else
If (i + 1) < chars.Length - 1 And chars(i + 1) <> ControlChars.Quote Then
word += chars(i)
Continue For
Else
If chars(i + 1) = ControlChars.Quote Then
word += chars(i)
End If
flag = False
i += 2
End If
Try
Array(j) = word
word = Nothing
Catch ex As Exception
'Array(j) = ""
End Try
j += 1
End If
Next
After this start populating like Dim names As String = Array(2) etc
thank you