Delimited File Problem

[Guest]

I am trying to import a file using a custom VB.net procedure, but the problem
is it works on a file with pure comma separation and not inverted commas and
commas, i.e. it works for AAA,BBB,CCC,DDD but not for "AAA","BBB","CCC","DDD".

Here is an extract from the code which needs to be modified for the """:

Sub LoadTextFile(ByVal strFilePath As String)
Dim oDS As New DataSet()
Dim strFields As String
Dim oTable As New DataTable()
Dim oRows As DataRow
Dim intCounter As Integer = 0
Dim intI As Integer = 0
Dim strDelimiter As String = "," <----THIS IS FOR THE COMMA SEP
Dim oSR As New IO.StreamReader(strFilePath)

Dim FileID As String

Dim FileName As String
Dim DbName As String

Dim oConn As SqlClient.SqlConnection


'Try
oImportForm.Refresh()

'Create Datatable with 12 columns
oDS.Tables.Add("InputTable")
Do While intI <= 51
oDS.Tables("InputTable").Columns.Add(intI)
intI = intI + 1
Loop

'Read the text file into the data set
oTable = oDS.Tables(0)
While (oSR.Peek() > -1)
oRows = oTable.NewRow()

For Each strFields In oSR.ReadLine().Split(strDelimiter)
<----HOW DO I MOD THIS FOR "AAA","BBB" AS IT WORKS FOR AAA,BBB?
oRows(intCounter) = strFields
intCounter = intCounter + 1
Next
intCounter = 0
oTable.Rows.Add(oRows)

End While

oSR.Close()


Thanks,

SKC

 

[Peter Rilling]

What results do you get when splitting the quoted values?

 

[Bill Burwell]

Is there a rule that the quoted values will never contain a comma??

If not, how are embedded commas handled??

 

[Guest]

Peter

It does not work. In my code, I have the following line:

If oTable.Rows(0).Item(0) <> "HDR" Then 'terminate condition for
incompatible files
oLogFile.WrongDFType(strFilePath, "HDR")
Exit Sub
End If

As the first item is "HDR" and not HDR (without the quotes), the program
just falls over.

 

[Guest]

Bill,

What do you mean? I have updated the question - see my answer to Peter.

 

[Guest]

Just modified my program. It ran, but the fields in my table contain values
with inverted commas - i.e. "AA" etc...

Is there a stored procedure that can get rid of these inverted commas?
I am new to this and need help on making this.

skc

 

[Bill Burwell]

Consider a delimited file containing
"AAA","BBB","How now, brown cow","CCC"

The list is comma delimited and the 3rd list item contains a comma.

EXCEL is one source of comma delimited files and the above example is valid
for EXCEL.