REPLACE in SQL function against text files

[mark]

Long story short...

I'm using ADO against text files on the network, and selecting certain stuff
through SQL, using the exmample I found from Ron de Bruin a while back.

( http://www.rondebruin.nl/ado.htm )

In the process of that, I've run into the inch sign, " , being in some of my
item numbers and descriptions, and it's causing trouble picking it up on the
Excel side.

So, I've stripped it out of the text files' input, and replaced it with
_THE_INCH_SIGN_ .

Now, in my SQL from VBA against the text files, I need to put it back. But,
when I try replace(), it tells me there's no replace function.

I've stripped it down to:

stSQLstring = "SELECT 'a' " & _
" , replace('abc','a','d') " & _
"FROM " & cnFileSummary & " excess "

and that's what it tells me:

"Undefined function 'replace' in expresson."

What is it called in SQL I can use from Excel??

Thanks.

 

[mark]

ok. unfortunately, I'm not quite sure how to do that.

In some cases, I'm returning the query's result to an ADO recordset, and
setting that to the recordset for a pivot cache, producing the data from the
text file in a pivot table, without directly displaying the data in Excel

In other cases, I'm pasting the ADO recordset to a location in Excel. In
that case, I could potentially do the find/replace after the data has been
pasted.

but, I'm not yet sure how to update the pivot cache, from what you said.

 

[RB Smissaert]

OK, I thought you moved data to another text file.
In that case you could replace in the recordset or do rs.GetRows and replace
in the array and then dump that array in Excel.
Possibly there is a way to do this in SQL and maybe somebody else will tell
you.

RBS

 

[mark]

thanks. I could dump it to Excel, but I'd like it be able to go to the
PivotCache even if it happened to exceed Excel 2003's 65536 row limit...
that's why I don't want to put it on a sheet and change things.

I'm trying to work on learning how to "replace in the recordset", but I'm
having trouble with it.

I found an example here:

http://camstechtricks.blogspot.com/2006/10/update-query-from-excel-to-mysql.html which is similar, but if it's the same, I'm not getting it yet.

The original problem is that the " is causing some problem in the
ADORecSet.execute(stSQLString) command, so that it doesn't read anything in
the pipe delimited record after the "

that's why today I started into trying to replace the " . I got that done,
but now I'm having trouble putting it back!

Thanks for trying.

 

[RB Smissaert]

Maybe post all the relevant code.

RBS

thanks. I could dump it to Excel, but I'd like it be able to go to the
PivotCache even if it happened to exceed Excel 2003's 65536 row limit...
that's why I don't want to put it on a sheet and change things.

I'm trying to work on learning how to "replace in the recordset", but I'm
having trouble with it.

I found an example here:

http://camstechtricks.blogspot.com/2006/10/update-query-from-excel-to-mysql.html
which is similar, but if it's the same, I'm not getting it yet.

The original problem is that the " is causing some problem in the
ADORecSet.execute(stSQLString) command, so that it doesn't read anything
in
the pipe delimited record after the "

that's why today I started into trying to replace the " . I got that
done,
but now I'm having trouble putting it back!

Thanks for trying.

 

[mark]

As I thought about it, I realized that I had treated the value differently in
PL/SQL. For the summary level entry, I ran it through a variable in PL/SQL.
For the detail, I ust used a cursor and put the result directly into the
output statement.

So, even though both look the same visually, since the use a variable method
worked for what I need ( " not stopping the record read later in Excel), I
switched the PL/SQL program to handle the detail record in the same manner...
pass the values through a variable.

It works. I don't know why, but it does. Consistantly.