We help IT Professionals succeed at work.

Problem with ' Character and TextDelimiters with OLE as a Datasource

312 Views
Last Modified: 2013-12-17
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.
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2012

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

Author

Commented:
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....
CERTIFIED EXPERT
Top Expert 2012

Commented:
Have you tried enclosing all fields in square brackets?

Author

Commented:
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?
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
it all depends on the file data.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.