Convert Format of SQL Data

[Wayne Wengert]

I have an Excel 2003 worksheet that has a data range based on a query
against a SQL Server. A couple of columns returned are the number 1 or 0. I
want to change those cell contents to "True" or "False". How can I
accomplish this? It appears that any formulas in those cells get overlaid by
the external data. I don't see a way to do this in the query?

Any suggestions are most welcome.

Wayne

 

[Guest]

You are correct that the query, when refreshed, will overwrite any formulas
in the query range, so you can't do it that way. There are two suggestions I
can think of:

1) Create some calculated columns immediately to the right of (and adjacent
to) your query result table - one column for each of the values you need to
convert. Put your formulas in those columns to convert the 1/0 values to
True/False and copy the formula down for each row in your query results.
Right-click on the query range and choose "Properties" and then tell it to
"Fill down formulas in columns adjacent to data" - this will ensure that your
calculated columns get filled down if you reload the data.

2) If you absolutely need the converted values to become part of the query
result table, you will need to do this via MSQuery/SQL. In MSQuery, use the
Records menu to "Insert Column." For the "Field" put in a formula to
calculate the result you want - for example, (COLUMNNAME=1) will give a
Boolean (true/false) result; true if your field called 'COLUMNNAME' is equal
to one, false otherwise. The query will then return the calculated result to
Excel.

I think one of these methods ought to accomplish what you need.
K Dales

 

[Wayne Wengert]

Thanks for the suggestions. I'll experiment with your approach - sounds like
it will do what I want.

Wayne

 

[Wayne Wengert]

I am trying option 2 but I cannot get MSQuery to accept the field name that
is a formula? I probably misunderstood your instructions? I tried
"myColumn=1" but I get a syntax error when I try to add that column. Where
can I find information on the correct syntax. I tried googling but am not
finding what I need?

Wayne

 

[Fredrik Wahlgren]

I have an Excel 2003 worksheet that has a data range based on a query
against a SQL Server. A couple of columns returned are the number 1 or 0. I
want to change those cell contents to "True" or "False". How can I
accomplish this? It appears that any formulas in those cells get overlaid by
the external data. I don't see a way to do this in the query?

Any suggestions are most welcome.

Wayne

Let's say your data goes to columns A to F and you get number 1 or 0 in
columns A and C
Copy the data to columnns D-F by enterering, this
D1: = IF(A1=1,"True","False")
E1: =C1
F1: = IF(C1=1,"True","False")
Drag these formulas downwards and hide columns A-C

/Fredrik

 

[Wayne Wengert]

Fredrik;

I do understand that process. The problem was when I was trying to create
the new column in the SQL statement as described in your second suggestion.

Wayne

 

[Wayne Wengert]

Finally figured it out. That is a very powerful capability. Thanks for
pointing it out to me.

Wayne

 

[Guest]

Hello Wayne - here are step by step instructions, assuming you are in MSQuery
with your original query showing:

- Select the column with the field you wish to convert by clicking on the
column header.
- Go to the Records menu and choose "Edit Column..."
- You should get a dialog with 3 things to enter: Field, Column Heading,
Total. The "Field" should show the data field you wish to convert.
- Enter your conversion formula here - you can enter any formula that is
recognized by the SQL interpreter in your database. The formula I gave tests
an equality and gives a true/false result: (COLUMNNAME=1). You need to
surround it in parentheses in order for it to be recognized as a formula,
though. And, you might need to put your column name in square braces, i.e:
([COLUMNNAME]=1)
- For the Column Heading, give the new column any name you wish (except it
cannot be an existing field name).
- Leave the Total blank.
- Press OK and rerun the query - it should now appear with your new column
replacing the original, and the results should be the true/false you are
looking for.

If any problems, it is probably in the syntax of the SQL. If the 1 and 0
are actually string values instead of numeric, for example, you would need to
change the formula to read ([COLUMNNAME]="1"). You can even press the SQL
toolbar button and manually edit the SQL query if you need to. What you need
is something like this:

SELECT COLUMN1, COLUMN2, COLUMN3, (COLUMN4=1) AS 'NEWNAME', ... FROM
TABLENAME WHERE...

Hope this is enough to get it working. If problems with the above it would
have to be something in the SQL syntax, I would think.

 

[onedaywhen]

The problem was when I was trying to create
the new column in the SQL statement as described

I think there is a fundamental problem with this request: SQL has no
boolean data type. I don't think that

(COLUMN4=1) AS 'NEWNAME'

will have the effect the poster expects i.e. this will not coerce the
result to a boolean.

Practically speaking, you can't cast as boolean on the server side
because T-SQL lacks an appropriate data type and you can't cast in the
SQL on the client side because MS Query's implementation of SQL AFAIK
lacks any cast or conversion functions. So I think you need to do some
post-import conversion within Excel e.g. using a 'helper' column or
VBA.

Jamie.

--

 

[Guest]

Thanks for pointing this out: I don't work directly with T-SQL so I was not
aware of this limitation; I did mention that the actual formula would be
dependent on the implementation of SQL used. However any formula accepted by
T-SQL should work, and I am pretty sure there must be some formula that will
yield the proper result. There must be an IF statement that would do the
trick, but I would need to study up on the details of T-SQL function syntax
to get it right.

K Dales

 

[onedaywhen]

Thanks for pointing this out: I don't work directly with T-SQL so I was not
aware of this limitation; I did mention that the actual formula would be
dependent on the implementation of SQL used. However any formula accepted by
T-SQL should work, and I am pretty sure there must be some formula that will
yield the proper result.

Trying to be sneaky here... Rather than using a SQL Server connection,
I instead connected to the workbook, i.e. used a MS Jet connection
which *does* support a BOOLEAN data type, then used Jet's ODBC
pass-through functionality to connect to the SQL Server table then cast
as a Boolean in Jet e.g.

SELECT IIF(MyBitCol<>0,TRUE,FALSE)
FROM [ODBC;DSN=SQLServer_Airplanes].MyTable;

No luck, though. MS Query still returned the values to the worksheet as
numeric values.

Jamie.

--