|
SQL "INSERT INTO" puts Single Quote in Front of Text Value
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 |
SQL "INSERT INTO" puts Single Quote in Front of Text Value
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" wrote in message ... 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 |
SQL "INSERT INTO" puts Single Quote in Front of Text Value
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" wrote in message ... 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 . |
SQL "INSERT INTO" puts Single Quote in Front of Text Value
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 wrote in message ... 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" wrote in message ... 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 . |
SQL "INSERT INTO" puts Single Quote in Front of Text Value
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 wrote in message ... 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" wrote in message ... 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 . . |
SQL "INSERT INTO" puts Single Quote in Front of Text Value
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] wrote: 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 wrote in message ... 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" wrote in message ... 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 . . . |
| All times are GMT +1. The time now is 05:56 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com