SQL "INSERT INTO" puts Single Quote in Front of Text Value

[David Weilmuenster]

I am using MS Excel 2002 to manage financial data.

To add new data to a named range in a spreadsheet, I have
written a query that uses the SQL "INSERT INTO" capability.

Works well, except for two problems. For this Post, the
problem I'm trying to solve is that the update query
inserts a single quote at the front of each text field.
E.g., the field "Symbol" could have the value of "IBM" (my
double quotes for purposes of explanation). But, the
update query produces "'IBM".

Any suggestions on how I can get the query to drop the
spurious single quote?

Thanks,
David

 

[Rob van Gelder]

Use ADO Parameters.
Or if that's not an option, then try single-quote twice (eg. "''IBM")

 

[Guest]

Rob, thanks. ADO isn't an option in this case.

But, I'm puzzled by your second suggestion. I'm trying to
eliminate the leading single quote from the result, not
preserve it.

Admittedly, I'm new to SQL/Excel, so I may have
misunderstood the hint.

David

 

[Rob van Gelder]

sorry. I thought you were trying to preserve it. My mistake.

I'm not certain how a single quote is being introduced.
Could I see your update SQL?

 

[Guest]

Thanks, Rob. The update SQL is below. The single quote
is being introduced as the lead character in [Symbol].

I just recognized, however, that the quote may not
actually be part of the field value, although it is
visible when one looks at the Excel cell. Comparisons,
searches, etc., on [Symbol] in Excel seem to ignore the
single quote, and if that's true, its presence poses no
problem for my spreadsheet.

Thanks again,
David
---------------------------

DBQ=D:\My Documents\Personal\Investment\General\Metastock
Scans\Boucher New Highs - New Lows.xls;DefaultDir=D:\My
Documents\Personal\Investment\General\Metastock
Scans;Driver={Microsoft Excel Driver
(*.xls)};DriverId=790;FIL=excel
8.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly
=0;SafeTransactions=0;Threads=3;UserCommitSync=Yes;
INSERT INTO Master_Database ([COMPANY NAME], [Eqy Industry
Sector], [Eqy Industry Group], [Eqy Industry Subgroup],
[HiLo], [Date], [Symbol]) SELECT
`Today$Boucher_New_Highs`.`COMPANY NAME`,
`Today$Boucher_New_Highs`.`Eqy Industry Sector`,
`Today$Boucher_New_Highs`.`Eqy Industry Group`,
`Today$Boucher_New_Highs`.`Eqy Industry Subgroup`, '1' ,
Date() , trim(`Today$Boucher_New_Highs`.Symbol) FROM
`Today$Boucher_New_Highs` `Today$Boucher_New_Highs`

 

[Jake Marx]

Hi David,

Is it possible that the cell containing the symbol has a single quote at the
beginning of it? Maybe to keep smart tags from bothering you? I wasn't
aware that they would get transferred over via data access, but maybe so.
If that's the case, you can try using the RIGHT and LEN functions to correct
it:

trim(`Today$Boucher_New_Highs`.Symbol)

becomes:

RIGHT(Today$Boucher_New_Highs`.Symbol,
LEN(Today$Boucher_New_Highs`.Symbol)-1)

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Thanks, Rob. The update SQL is below. The single quote
is being introduced as the lead character in [Symbol].

I just recognized, however, that the quote may not
actually be part of the field value, although it is
visible when one looks at the Excel cell. Comparisons,
searches, etc., on [Symbol] in Excel seem to ignore the
single quote, and if that's true, its presence poses no
problem for my spreadsheet.

Thanks again,
David
---------------------------

DBQ=D:\My Documents\Personal\Investment\General\Metastock
Scans\Boucher New Highs - New Lows.xls;DefaultDir=D:\My
Documents\Personal\Investment\General\Metastock
Scans;Driver={Microsoft Excel Driver
(*.xls)};DriverId=790;FIL=excel
8.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly
=0;SafeTransactions=0;Threads=3;UserCommitSync=Yes;
INSERT INTO Master_Database ([COMPANY NAME], [Eqy Industry
Sector], [Eqy Industry Group], [Eqy Industry Subgroup],
[HiLo], [Date], [Symbol]) SELECT
`Today$Boucher_New_Highs`.`COMPANY NAME`,
`Today$Boucher_New_Highs`.`Eqy Industry Sector`,
`Today$Boucher_New_Highs`.`Eqy Industry Group`,
`Today$Boucher_New_Highs`.`Eqy Industry Subgroup`, '1' ,
Date() , trim(`Today$Boucher_New_Highs`.Symbol) FROM
`Today$Boucher_New_Highs` `Today$Boucher_New_Highs`

-----Original Message-----
sorry. I thought you were trying to preserve it. My mistake.

I'm not certain how a single quote is being introduced.
Could I see your update SQL?

--
Rob van Gelder - http://www.vangelder.co.nz/excel





.

 

[Guest]

Jake, thanks again.

I checked the source file (just a plain old text file
exported from another program), and there is no single
quote in that field.

However, and luckily, it turns out that the quote presents
no problem even if it remains in my Excel data. It may
just be some sort of data qualifier/delimiter that has no
effect on the cell value. I can search, sort, compare,
etc. and the single quote has no effect.

On to the next one...

David

-----Original Message-----
Hi David,

Is it possible that the cell containing the symbol has a single quote at the
beginning of it? Maybe to keep smart tags from bothering you? I wasn't
aware that they would get transferred over via data access, but maybe so.
If that's the case, you can try using the RIGHT and LEN functions to correct
it:

trim(`Today$Boucher_New_Highs`.Symbol)

becomes:

RIGHT(Today$Boucher_New_Highs`.Symbol,
LEN(Today$Boucher_New_Highs`.Symbol)-1)

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Thanks, Rob. The update SQL is below. The single quote
is being introduced as the lead character in [Symbol].

I just recognized, however, that the quote may not
actually be part of the field value, although it is
visible when one looks at the Excel cell. Comparisons,
searches, etc., on [Symbol] in Excel seem to ignore the
single quote, and if that's true, its presence poses no
problem for my spreadsheet.

Thanks again,
David
---------------------------

DBQ=D:\My Documents\Personal\Investment\General\Metastock
Scans\Boucher New Highs - New Lows.xls;DefaultDir=D:\My
Documents\Personal\Investment\General\Metastock
Scans;Driver={Microsoft Excel Driver
(*.xls)};DriverId=790;FIL=excel
8.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly
=0;SafeTransactions=0;Threads=3;UserCommitSync=Yes;
INSERT INTO Master_Database ([COMPANY NAME], [Eqy Industry
Sector], [Eqy Industry Group], [Eqy Industry Subgroup],
[HiLo], [Date], [Symbol]) SELECT
`Today$Boucher_New_Highs`.`COMPANY NAME`,
`Today$Boucher_New_Highs`.`Eqy Industry Sector`,
`Today$Boucher_New_Highs`.`Eqy Industry Group`,
`Today$Boucher_New_Highs`.`Eqy Industry Subgroup`, '1' ,
Date() , trim(`Today$Boucher_New_Highs`.Symbol) FROM
`Today$Boucher_New_Highs` `Today$Boucher_New_Highs`

-----Original Message-----
sorry. I thought you were trying to preserve it. My mistake.

I'm not certain how a single quote is being introduced.
Could I see your update SQL?

--
Rob van Gelder - http://www.vangelder.co.nz/excel


Rob, thanks. ADO isn't an option in this case.

But, I'm puzzled by your second suggestion. I'm trying to
eliminate the leading single quote from the result, not
preserve it.

Admittedly, I'm new to SQL/Excel, so I may have
misunderstood the hint.

David

-----Original Message-----
Use ADO Parameters.
Or if that's not an option, then try single-quote twice (eg.
"''IBM")


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"David Weilmuenster"
I am using MS Excel 2002 to manage financial data.

To add new data to a named range in a spreadsheet, I have
written a query that uses the SQL "INSERT INTO" capability.

Works well, except for two problems. For this Post, the
problem I'm trying to solve is that the update query
inserts a single quote at the front of each text field.
E.g., the field "Symbol" could have the value of "IBM" (my
double quotes for purposes of explanation). But, the
update query produces "'IBM".

Any suggestions on how I can get the query to drop the
spurious single quote?

Thanks,
David


.



.

.