MS Query capabilities/regex
 

Hi all. New to Excel, but formerly a programmer. I've got Excel 2003
(?) and have begun using it to pull data from an external database. I
can't seem to find a good reference to the features/limitations of MS
Query. It appears a lot of material points to Access (which our IT guy
won't let me use). Also I'm stuck with using whatever s/w is on our
weird server - I can't add or upgrade any apps.

Currently I'm looking at a lot of ugly data stored as long strings,
pulling 'possible matches' with MS Query, and then using a VBscript in
a formula to regex the text to find the specific things I want.
Naturally I'd prefer to be able to use regex right in the SQL query,
but I don't know how to begin. I don't know what (if any) regex
capabilites can be used in Query.

For example, I've got text that looks like this:

<garbage<useful value 1<unique token<useful value 2<garbage

I use MS Query to find the <unique token, and then the regexp in a
VBScript formula to find the useful values 1 & 2. Not the cleanest
solution, to be sure.

TIA
Mike

MS Query capabilities/regex
 

Hi Mike,

I see you have not received a reply yet, so I'll have a go...

1) MS Query uses Access SQL which is similar to T-SQL - you can use
expressions with wildcards % and ?, but not regex AFAIK.

So to search in a string with MS Query you would use something like;
SELECT * FROM myTable WHERE myTable.myField LIKE '%unique token%'

2) Given the data you describe, you may get a better result if you bring the
whole thing into Excel and use Excel's Text-to-columns wizard to parse the
data into something useful.

i.e. if you have data like this in a column
<garbage<useful value 1<unique token<useful value 2<garbage

Select the whole column, then use the menu Data - Text to Columns -
Delimited
Click Next
Check Other as a delimiter and use < as a delimiter
Click Finish

The data will be separated into columns like this;
garbage useful value 1 unique token etc

Now select all the new columns and use Edit - Replace to find and replace
with a blank

Ed Ferrero
www.edferrero.com


"Mike" wrote;
Hi all. New to Excel, but formerly a programmer. I've got Excel 2003 (?)
and have begun using it to pull data from an external database. I can't
seem to find a good reference to the features/limitations of MS Query. It
appears a lot of material points to Access (which our IT guy won't let me
use). Also I'm stuck with using whatever s/w is on our weird server - I
can't add or upgrade any apps.

Currently I'm looking at a lot of ugly data stored as long strings,
pulling 'possible matches' with MS Query, and then using a VBscript in a
formula to regex the text to find the specific things I want. Naturally
I'd prefer to be able to use regex right in the SQL query, but I don't
know how to begin. I don't know what (if any) regex capabilites can be
used in Query.

For example, I've got text that looks like this:

<garbage<useful value 1<unique token<useful value 2<garbage

I use MS Query to find the <unique token, and then the regexp in a
VBScript formula to find the useful values 1 & 2. Not the cleanest
solution, to be sure.

TIA
Mike

MS Query capabilities/regex
 

Thx Ed. This confirms my futzing around. I had a crazy idea that it
used the same SQL as the MS server, but as usual the dizzying array of
stuff got me confused. What I've done is pull the data into Excel, with
a search like you describe, and then regex over it. Thus I enter the
world of VBScript. Not so bad, but at home I'm a Mac user, and VBScript
seems to be dead-ended... oh well

Hi Mike,

I see you have not received a reply yet, so I'll have a go...

1) MS Query uses Access SQL which is similar to T-SQL - you can use
expressions with wildcards % and ?, but not regex AFAIK.

So to search in a string with MS Query you would use something like;
SELECT * FROM myTable WHERE myTable.myField LIKE '%unique token%'

2) Given the data you describe, you may get a better result if you
bring the whole thing into Excel and use Excel's Text-to-columns wizard
to parse the data into something useful.

i.e. if you have data like this in a column
<garbage<useful value 1<unique token<useful value 2<garbage

Select the whole column, then use the menu Data - Text to Columns - Delimited
Click Next
Check Other as a delimiter and use < as a delimiter
Click Finish

The data will be separated into columns like this;
garbage useful value 1 unique token etc

Now select all the new columns and use Edit - Replace to find and
replace with a blank

Ed Ferrero
www.edferrero.com


"Mike" wrote;
Hi all. New to Excel, but formerly a programmer. I've got Excel 2003
(?) and have begun using it to pull data from an external database. I
can't seem to find a good reference to the features/limitations of MS
Query. It appears a lot of material points to Access (which our IT guy
won't let me use). Also I'm stuck with using whatever s/w is on our
weird server - I can't add or upgrade any apps.

Currently I'm looking at a lot of ugly data stored as long strings,
pulling 'possible matches' with MS Query, and then using a VBscript in
a formula to regex the text to find the specific things I want.
Naturally I'd prefer to be able to use regex right in the SQL query,
but I don't know how to begin. I don't know what (if any) regex
capabilites can be used in Query.

For example, I've got text that looks like this:

<garbage<useful value 1<unique token<useful value 2<garbage

I use MS Query to find the <unique token, and then the regexp in a
VBScript formula to find the useful values 1 & 2. Not the cleanest
solution, to be sure.

TIA
Mike