?
Solved

Excel - SQL Statement to Cover Multiple Data Types in Download

Posted on 2012-09-12
7
Medium Priority
?
595 Views
Last Modified: 2012-09-13
Currently, I have a VBA Sub routine, to extract data from a SQL Table, insert it into an Excel Worksheet, and perform the needed formatting of the cells.  All of this works fine, no problems.

Do to the size of this table, it has been suggested that for one of the processes, where I look for a Blank, Empty, or Null value (depending on how one wants to classify, a Blank Excel cell) , that the word "NULL" be placed within the empty cell so it is not blank.

The issue is that SQL is somewhat "picky" about a value of no data, or Null, depending upon its data type.  Yes, I know that maybe a cell could have a "space" or some other non-printable character, and maybe for security reasons.  However, the customer wants to see the word NULL if the cell is empty.

The problem is with the data types. If the values are text then the following can be used:
 
Query = ", CASE WHEN TEXT = '' THEN 'NULL' ELSE TEXT END AS TEXT "

Open in new window

If it is a numeric then the code is:
Query = ", CASE WHEN NUMERIC = '' THEN 'NULL' ELSE CONVERT(VARCHAR(50),NUMERIC ) END AS NUMERIC  "

Open in new window


Maybe there are more elegant ways to do this, but all that is presently known, is that a seperate line of code has to be written for each data type.  Given that, I might as well stick to a VBA loop.  

Although, since this is the place to come for those difficult situations, I thought that it might be worthwhile to see if anyone knows of a way to enter a text value of NULL, if the cell is blank, regardless of data type.
0
Comment
Question by:Cook09
  • 4
  • 3
7 Comments
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
ID: 38391383
Not quite sure what you are looking for as a solution - SQL or Excel based?

A single approach for all types in SQL is to convert everything to varchar(max) and  use COALESCE to deal with the NULLS

Here is an example (I used 'No Value' to be sure that I was generating it rather than seeing an actual NULL if you follow me)

Select COALESCE(CONVERT(VARCHAR(MAX), MyCol ) , 'No Value') as  MyCol
From
( Select 1 as MyCol
  UNION
  Select NULL as MyCol
) Q
0
 

Author Comment

by:Cook09
ID: 38391645
While I'm looking for an Excel based answer, yet Excel just sends the Query string to SQL Server, and then SQL returns with what one requests.

I think I understand the answer, but what if one has 62 columns? Right now I have code that does the following

       
     sQuery = "SELECT * from " & sSQLTable & ""
            sQuery = "SELECT Top(10000) USI "
            sQuery = sQuery & ", CASE WHEN LO_COND_ID IS NULL THEN 'NULL' ELSE CONVERT(VARCHAR(50),LO_COND_ID) END AS LO_COND_ID "
            sQuery = sQuery & ", CASE WHEN INSTANCE_ID IS NULL THEN 'NULL' ELSE CONVERT(VARCHAR(50),INSTANCE_ID) END AS INSTANCE_ID "
            sQuery = sQuery & ", CASE WHEN SEGMENT = '' THEN 'NULL' ELSE SEGMENT END AS SEGMENT "
            sQuery = sQuery & ", CASE WHEN SAMPLEDEXCLUSION = '' THEN 'NULL' ELSE SAMPLEDEXCLUSION END AS SAMPLEDEXCLUSION"
            sQuery = sQuery & ", CASE WHEN PRTFL = '' THEN 'NULL' ELSE PRTFL END AS PRTFL "
            sQuery = sQuery & ", CASE WHEN PWKGP = '' THEN 'NULL' ELSE PWKGP END AS PWKGP "
            sQuery = sQuery & vbCrLf
            sQuery = sQuery & "From " & sSQLTable & ""  

Open in new window

Only it's for 62 columns total.  I didn't even include the one that looks for a date. How would I write your statement for multiple columns?  Again, I'm seeing if there is a common and simple solution.
0
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
ID: 38391750
You are correct - you would have to replicate my expression for each column.

I don't quite know how you are transferring the data from the database to the excel sheet.  

My only idea would be a VBA loop to either
a) prepare the sql statement using an approach similar to my previous post so that the results come back pre-formatted
b) process the results returned from the database and format each value and write to the appropriate cell - this would allow a simpler SQL statement

And you have already considered a VBA loop so I have nothing else - sorry!
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Cook09
ID: 38392682
I'm transferring it back as a recordset using ADODB.  The reason I ask in how to write your response for multiple columns, is that after looking at it further, I'm not quite sure I fully understand it. I'm not familiar with the Q at the end of the statement. Plus there's the discussion of a NULL versus a "", that we have been having.  For instance ISNULL doesn't always work, but "" does.

  It's not the fact that this would have to be duplicated 62 times.  That's the beauty of computers, do it once and reuse it.  The names could be stored in Name Manager.  I already have 62 lines of code, one for for each column.  However, if on the next one, the text and numeric fields are in a different order, then I have to go through line by line to verify.  That's where having one statement which can handle any data type would be advantageous--- after the first one is completed --- .

Could there be another or second solution?  If not, I can use VBA to post process, but was trying to get creative and efficient, and pre-process.  I would have thought that the issue of data types has come up before and someone has found a way to deal with any data type.
0
 
LVL 12

Accepted Solution

by:
Paul_Harris_Fusion earned 1500 total points
ID: 38393904
Sorry to be unclear - the Q at the end of my statement was just so that I could use the subselect (the union part) to imitate a table so that I did not have to create a table to test the select part.

To use against an existing table it would be more like:
Select COALESCE(CONVERT(VARCHAR(MAX), MyCol ) , 'No Value') as  MyCol
From MyTable

If you are receiving an ADO recordset back,  you could write a single VBA function that goes through each record ,  and each field within the record,  and copy the value to the appropriate place in the worksheet (replacing any nulls with 'NULL' as you go).
Your sql statement would then be nice and simple since you are handling the formatting once the data is in your recordset.
0
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
ID: 38393965
With regards to the NULL versus '' issue,  the sql I posted converts NULL to 'No Value'  but does not convert empty strings.

I think your best bet is to format the returned data rather than monkey about with the querying
0
 

Author Closing Comment

by:Cook09
ID: 38394870
Well, at least I have a statement to convert the two, and will spend more effort in the VBA issue.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question