Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Format of SQL Data
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Format of SQL Data
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Format of SQL Data
Thanks for the suggestions. I'll experiment with your approach - sounds like
it will do what I want. Wayne "K Dales" wrote in message ... 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Format of SQL Data
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 "K Dales" wrote in message ... 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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Format of SQL Data
"Wayne Wengert" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Format of SQL Data
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 "Fredrik Wahlgren" wrote in message ... "Wayne Wengert" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Format of SQL Data
Finally figured it out. That is a very powerful capability. Thanks for
pointing it out to me. Wayne "Wayne Wengert" wrote in message ... 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 "Fredrik Wahlgren" wrote in message ... "Wayne Wengert" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Format of SQL Data
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. "Wayne Wengert" wrote: 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 "K Dales" wrote in message ... 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" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Format of SQL Data
Wayne Wengert wrote: SELECT COLUMN1, COLUMN2, COLUMN3, (COLUMN4=1) AS 'NEWNAME', ... FROM TABLENAME WHERE... 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. -- |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Format of SQL Data
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" wrote: Wayne Wengert wrote: SELECT COLUMN1, COLUMN2, COLUMN3, (COLUMN4=1) AS 'NEWNAME', ... FROM TABLENAME WHERE... 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. -- |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Format of SQL Data
K Dales wrote:
I think there is a fundamental problem with this request: SQL has no boolean data type. 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. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert data into proper time format | Excel Worksheet Functions | |||
How do I convert numeric data to string format (without VBA)? | Excel Worksheet Functions | |||
Import csv data and convert to TIME format | Excel Worksheet Functions | |||
How do I convert word data (not in tables) into excel format? | Excel Worksheet Functions | |||
CONVERT DATA TO CURRENCY FORMAT | Excel Discussion (Misc queries) |