Apostrophe in extracted data interfering with code.

[LacyLu42]

I am using the ExcelSQL add-in to extract data from a series of cells in an Excel worksheet and then push them into a SQL table.

The problem I've run into is that if the text in the cell contains an apostrophe ('), SQL reads that as a break in the instruction code.

For example, my code reads as follows:

INSERT into TABLE (Field_Name) values ('{A1}')

(where {A1} is the cell name within the worksheet). This works beautifully until I get to a text with an apostrophe; then SQL truncates the text, and receives an error, because it now has an unclosed quotation mark.

Following is the actual error I receive:

Error in ExcelSQL cell note in $A$2, data source CAM:

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near '‘'.

[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the character string 's divorce.’, ‘’, ‘’, ‘’) '.

ODBC--call failed.

Any suggestions would be appreciated.

Thanks,
~Lacy

 

[SonOfEmidec1100]

This is a common problem in sql server, this is because sql server uses a single quote for string delimiters . There are several threads on this - search for apostrophe or 'single quote'.

 

[LacyLu42]

Thank you, I looked at the other threads, but I'm having trouble adapting them to my situation.

First, I am using an automate program to automatically extract the data. I have over 1200 excel files with 20,000 cells + each to interpret, so there's no way I can do them all by hand, converting the apostrophes as they appear.

Second, only a very small number of the fields will have this problem.

Is there a way to program the conversion of an apostrophe into two it into the SQL statement by using an IF statment, so that if - and only if - the data contained in a variable includes an apostrophe, it will be converted?

Thanks,
~Lacy

 

[amorous]

try this:

yourcellname=Replace(yourcellname,"'","''")

Then do the query

INSERT into TABLE (Field_Name) values ('yourcellname')

-VJ

 

[donutman]

Two thoughts:
1) A VB app could easily be written to automate the job.
2) Isn't there a way to tell SQL Server to use a different character for quoting? If so make it some obsure character on a workstation edition of SQL Server and then export results to the production server.
-Karl