I have pipe delimited (and comma/tab) files that I read with JET using a
schema file. Occasionally a field has multiple quotes (") inside a single
field - which chokes the line (the rest of the columns get read as
nulls). An example of an offending line is:
J" Invalid " ....
This is a text field that is between the two 'pipes' so I need it read
exactly as is.
I have reverted to schema files, trying everything I can to overcome this
problem. If I open the file in Excel or do a DTS into SQL it parses it
fine (as they seem to be properly formatted 'delimited' files). While I
am having this problem I am forced to open in excel and save as an excel
file (which JET reads fine), or hack regex's that change these quotes.
This is very time consuming as these files are extremely large.
I assume it is something to do with telling Jet (through the schema file)
to ignore quotes inside fields - or at least treat them as part of the
regular text. But I have checked MS (best I have found is:
http://msdn.microsoft.com/library/de...l=/library/en-
us/odbc/htm/odbcjetschema_ini_file.asp )
and scoured google etc...
Code to read the file:
Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & sPath & ";Extended Properties=Text;"
Dim objConn As New System.Data.OleDb.OleDbConnection(sConnectionStrin g)
objConn.Open()
Dim objCmdSelect As New OleDbCommand("SELECT * FROM 1.txt", objConn)
Dim objAdapter1 As New OleDbDataAdapter
objAdapter1.SelectCommand = objCmdSelect
Dim ds As New DataSet
objAdapter1.Fill(ds)
ojbConn.Close()
Schema file:
[1.txt]
ColNameHeader=False
CharacterSet=ANSI
Format=Delimited(|)
Col1="User Name" Text
.....
