OleDb Reading Text File handle quotes in field

[billboy]

Is there a way to handle quotes with a field in text tab delimited file
example:

"This program works "really" nicely and is helpful"

my reading and understand thus far is that while the file is tab delimited fields can also begin and end with quotes. Therefore is a field contains a quote it gets interpreted as end of field

Dim sql As String = String.Format("Select * From {0} WHERE " & Status & " <> @Status AND [" & SubjId & "] <> @SubjAdd ", safeFileName)
        '    Dim comm As New OleDb.OleDbCommand(sql, con)
        '    comm.Parameters.AddWithValue("@Status", Form11.Closed_Code.Text)
        '    comm.Parameters.AddWithValue("@SubjAdd", 111111)
        '    Dim dalist As New OleDbDataAdapter(comm)
        '    dalist.Fill(dtlist)

[jmcilhinney]

If you want to use ADO.NET then you will need to create a 'schema.ini' file, which I believe must be in the same folder as the data file. You can search online for the file format.

The alternative would be to use a TextFieldParser and read the file yourself, line by line.

[danasegarane]

If you prefer to read using TextField Parser then consider using this one

[billboy]

Heres is the schema.ini file I have been using:

Dim safeFileName As String = Me.OpenFileDialog1.FileName
        Dim folder As String = IO.Path.GetDirectoryName(Me.OpenFileDialog1.FileName)
        Dim iniFileName As String = IO.Path.Combine(folder, "Schema.ini")
        Using writer As New StreamWriter(iniFileName)
            writer.WriteLine("[" & safeFileName & "]")
            writer.WriteLine("Format = TabDelimited")
            writer.WriteLine("Col1=StreetNumber Text Width 10")
            writer.WriteLine("Col2=StreetName Text Width 60")
            writer.WriteLine("Col3=Unit Text Width 20 ")
            writer.WriteLine("Col4=City Text Width 30")
            writer.WriteLine("Col5=State Text Width 10")
            writer.WriteLine("Col6=Zip Text Width 10")
            writer.WriteLine("Col7=MLS Text Width 20")
            writer.WriteLine("Col8=OriginalListPrice Text Width 10")
            writer.WriteLine("Col9=ListPrice Text Width 10")
            writer.WriteLine("Col10=ListDate Text Width 30")
            writer.WriteLine("Col11=DaysOnMarket Text Width 10")
            writer.WriteLine("Col12=Status Text Width 20")
            writer.WriteLine("Col13=Style Text Width 10")
            writer.WriteLine("Col14=Bedroom Text Width 10")
            writer.WriteLine("Col15=Bath Text Width 10")
            writer.WriteLine("Col16=SqFt Text Width 10")
            writer.WriteLine("Col17=Parking Text Width 10")
            writer.WriteLine("Col18=Fireplace Text Width 10")
            writer.WriteLine("Col19=Pool Text Width 10")
            writer.WriteLine("Col20=View Text Width 10")
            writer.WriteLine("Col21=LotSize Text Width 10")
            writer.WriteLine("Col22=YearBuilt Text Width 10")
            writer.WriteLine("Col23=Levels Text Width 10")
            writer.WriteLine("Col24=Attached_Detached Text Width 20")
            writer.WriteLine("Col25=SaleType Text Width 10")
            writer.WriteLine("Col26=Heat Text Width 10")
            writer.WriteLine("Col27=AirCondition Text Width 10")
            writer.WriteLine("Col28=HOADues Text Width 10")
            writer.WriteLine("Col29=PrivateRemarks Text Width 300")
            writer.WriteLine("Col30=PublicRemarks Text Width 300")
            writer.WriteLine("Col31=SoldDate Text Width 30")
            writer.WriteLine("Col32=SalePrice Text Width 15")
            writer.Close()
        End Using

And here is the connection string

builder("Provider") = "Microsoft.Jet.OLEDB.4.0"
        builder("Data Source") = IO.Path.GetDirectoryName(Me.OpenFileDialog1.FileName)
        builder("extended properties") = "text;HDR=Yes;FMT=Delimited"

I have been reading all day and cant seem to find where I am going wrong

[jmcilhinney]

Ah, I don't think I read the first post carefully enough. If your fields can be wrapped in quotes then any quotes in the data itself must be escaped, e.g.

"This program works ""really"" nicely and is helpful"

The thing is, there's no reason for your fields to be quoted, so this really shouldn't be an issue. The point of quoting a field value is so that you can contain the column delimiter within a field value. For instance, let's say that you have a CSV file that contains addresses. The field delimiter is a comma so how do you include a comma in an address field? You quote the filed value and then any comma within the quotes is considered part of the data.

Are you going to have any Tab characters in your data? If not then quoting anything is pointless.

[billboy]

I am working from a 3rd party file, thats how the data is nothing I can do about it.
The field is a comments field and they allow the quotes in there

[danasegarane]

Then you can go for the text field parsing as mentioned in post #3

[billboy]

Ok I am working on implementing the text field parser

Is there no way to do this with ADO.net ?

Is there perhaps a way to remove the quotes within the field?

Thanks for help and feedback

[jmcilhinney]

ADO.NET works on the assumption that your data is valid. If your are using double quotes as text delimiters and they also appear in your field values unescaped then then that data is not valid, in so far as it's not in a standard for supported by the Jet OLE DB provider. Either use data that is in a supported format or don't use ADO.NET. If you want to preprocess your data to get it into a supported format then go ahead but, if you're going to have to read the data in anyway, why do it twice?

[billboy]

ADO.NET works on the assumption that your data is valid. If your are using double quotes as text delimiters and they also appear in your field values unescaped then then that data is not valid, in so far as it's not in a standard for supported by the Jet OLE DB provider. Either use data that is in a supported format or don't use ADO.NET. If you want to preprocess your data to get it into a supported format then go ahead but, if you're going to have to read the data in anyway, why do it twice?

Thanks thats a great point and I agree, I was using this obstacle as an opportunity to learn. So I wanted to exhaust every possiblity not so much for implementation but for learning. I Didnt know if there was something I was missing something in implementing ADO.NET. under these circumstances.

The interesting thing with this particular file is that it doesnt use quotes as delimiters example:

Field Comments:
This is a really "nice" program, it looks useful

But since quotes CAN be used , ADO assumes it is a new field

the more I am learning about progrmming and datafiles the more I am realizing that this is a lousy file format. As I mentioned before in other posts the datafile provider also calls two columns using the same name.

But this is what I am faced with in trying to create a program compatible with as many datafile providers as possible.( they are all tab delimited )

I am going to implement the TextFieldParser, actually have it working just need to learn to filter my data for populating to the datatables then it would apprear the rest of my code could remain.

Then on to learning how to add a record

Thanks as always for all the great insight. It is much appreciated and valued