asked on

Problem with ' Character and TextDelimiters with OLE as a Datasource

I have an issue with a OLE Text file that I am reading into a table then building sql statements from...
the problem is that any field that contains a ' character is getting messed up...

Here is my connection string:
mODBCcn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + config.AppSettings.Settings["ImportLoc"].Value.ToString() + ";Extended Properties=\"text;HDR=Yes;FMT=Delimited\"");

now, I build a schema file that looks like ths:

[import.txt]
ColNameHeader=True
Format=TabDelimited
DecimalSymbol=,
TextDelimiter='

Then

ODBCda = new OleDbDataAdapter("Select * from " + msrcFile, mODBCcn);
try
{
ODBCda.Fill(mODBCdt);
mODBCdt.TableName = "import";
mODBCdt.DefaultView.AllowNew = false;
}

any row that has a field with ' in it..gets wacked..in other words if the columns are:
mfgr model descr list cost
and ' is in the description, the rest of the row has null values.

Thanks for the help.

Anthony Perkins

You need to escape the apostrophe. The way you do that is replace any single quote with two single quotes.

mitsic

ASKER

Yes, however I am reading the tab delimited file as a datasource,
ODBCda = new OleDbDataAdapter("Select * from " + msrcFile, mODBCcn);
and then filling a datatable which is a datasource for a grid, so I have no real chance to change the ' with two....

Anthony Perkins

Have you tried enclosing all fields in square brackets?

mitsic

ASKER

Do you mean in the .txt file before I load it?
I am really trying to avoid having the user do anything extra. I don't understand why it won't read into the grid correctly..if it did then I can hanlde the escape processing as I build the sql updates/inserts. Is there something I can put in the schema so that the OLEDB provider will process the ' character?

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

mitsic

ASKER

All right, So I took out the line
TextDelimiter='
So, I suppose if the file is delimeted with ', then I will turn on that line, otherwise I will leave it out...does that sound right?

Guy Hengel [angelIII / a3]

it all depends on the file data.