I ended up using the MS Excel objects to convert files from XLS to CSV . Converted CSV had comma "," as field separator. But, what if the a filed content has comma like "lastName,FirstName". Now if you use default .net split method to get the fileds of a row in csv file, it will treat "lastName,FirstName" as two fields. I ended up writing my own split method which will take care of the comma in fields:
Private Function CustomSplit(ByVal sText As String,ByVal sSeparator As String) As String()
Dim RetrunArray As New ArrayList
Dim CurrentIndex As Int32
Dim CommaIndex As Int32
Dim CurrentField As String
Dim NextChar As String
CommaIndex = sText.IndexOf(sSeparator)
While(CommaIndex>0 and CommaIndex<sText.Length)
CurrentField = sText.Substring(CurrentIndex,CommaIndex-CurrentIndex)
RetrunArray.Add(CurrentField)
NextChar = sText.Substring(CommaIndex+1,1)
If(NextChar.Equals ("""")) Then
CurrentIndex = CommaIndex +1
CommaIndex = sText.IndexOf("""",CommaIndex+2)
CommaIndex = CommaIndex +1
If(CommaIndex<sText.Length)
NextChar = sText.Substring(CommaIndex,1)
If(Not NextChar.Equals (",")) Then
CommaIndex = sText.IndexOf(",",CommaIndex+2)
End If
End If
Else
CurrentIndex = CommaIndex +1
CommaIndex = sText.IndexOf(sSeparator,CurrentIndex)
End If
End While
'Pick up last field
CurrentField = sText.Substring(CurrentIndex)
RetrunArray.Add(CurrentField)
Return RetrunArray.ToArray(Type.GetType("System.String"))
End Function