Category Archives: Uncategorized

Convert CSV data into a SQL table

Recently I have been working with some report data that is stored statically in a database in CSV format. I had a need to have that CSV data displayed in a tabular format. I searched around and couldn’t find anything written to do this in SQL, so I decided to write a stored procedure to do this very thing. It will take a CSV and parse it out into a SQL table. For instance if I have report data that looks like this…

"FirstName","LastName","DOB","Children"
"John","Smith","1/5/1980","Bob,Sally,Joe,Chris"
"Jane","Smith","2/25/1982","Bob,Sally,Joe,Chris"
"Bruce","Wayne","5/13/1975",""
"Peter","Parker","5/23/1970",""
"","","",""

It would be transformed into this …

FIRSTNAMELASTNAMEDOBCHILDREN
JohnSmith1/5/1980Bob,Sally,Joe,Chris
JaneSmith2/25/1982Bob,Sally,Joe,Chris
BruceWayne5/13/1975 
PeterParker5/23/1970 
    

Note that in the last column the commas are preserved because they are within the quotation mark text qualifiers. Below is the stored procedure that does this. Feel free to use it, and leave a comment if you found it useful.

-- ==========================================================================================
-- Author:  Davin Studer
-- Create date: 4/5/2011
-- Description: This will take a CSV input
-- and transform it into a table
--
-- Params:
-- @string          - The CSV string
-- @textQualifier   - Character that denotes a string within the CSV
-- @columnDelimiter - Character that denotes different columns ... doesn't have to be a comma
-- @rowOneIsHeader  - Does the first row contain column names?
-- ==========================================================================================
create procedure [dbo].[CSVToTable]
    @string nvarchar(max) = '',
    @textQualifier varchar(1) = '',
    @columnDelimiter varchar(1) = ',',
    @rowOneIsHeader bit = 0
as
begin
    -- set nocount on added to prevent extra result sets from
    -- interfering with select statements.
    set nocount on;
 
    -- We need and input string
    if isnull(@string, '') = ''
    begin
        raiserror ('Please supply an CSV string', 15, 1)
        return
    end
 
    -- We need and column delimiter
    if isnull(@columnDelimiter, '') = ''
    begin
        raiserror ('Please supply a column delimiter.', 15, 1)
        return
    end
  
    -- Make sue the user doesn't pass null as a value
    if isnull(@textQualifier, '') = ''
    begin
        set @textQualifier = ''
    end
 
    -- Make sue the user doesn't pass null as a value
    if isnull(@rowOneIsHeader, '') = ''
    begin
        set @rowOneIsHeader = 0
    end
 
    declare
        @columns int = 1,
        @columnNames nvarchar(max) = '',
        @stop bit = 0,
        @position int = 0,
        @temp nvarchar(1) = '',
        @dataStart int = 0,
        @sql nvarchar(max) = '',
        @qualifierToggle bit = 0,
        @tempString nvarchar(max) = '',
        @delimiterReplacementUTFNumber int = 2603
 
    -- Get rid of the ##tempCSVSplitToTable table if it exists
    if object_id('tempdb..##tempCSVSplitToTable') is not null
    begin
        drop table ##tempCSVSplitToTable
    end
 
    -- Get rid of white space
    set @string = rtrim(ltrim(@string))
 
    -- Set the EOL to char(13)
    set @string = replace(@string, char(13) + char(10), char(13))
    set @string = replace(@string, char(10), char(13))
 
    -- Deal with the delimiter character within the text qualifier characters
    if @textQualifier <> ''
    begin
        while @position <> len(@string)
        begin
            set @temp = substring(@string, @position, 1)
            if @temp = @textQualifier
            begin
                if @qualifierToggle = 0
                begin
                    set @qualifierToggle = 1
                end
                else
                begin
                    set @qualifierToggle = 0
                end
            end
            if @temp = @columnDelimiter and @qualifierToggle = 1
            begin
                set @tempString = @tempString + nchar(@delimiterReplacementUTFNumber) -- replace with UTF delimiter replacement character
            end
            else
            begin
                set @tempString = @tempString + @temp
            end
            set @position = @position + 1
        end
 
        set @string = @tempString
    end
 
    -- Get rid of text qualifier ... we don't need it now
    if @textQualifier <> ''
    begin
        set @string = replace(@string, @textQualifier, '')
    end
 
    -- Get column names
    set @position = 1
    while @stop = 0
    begin
        set @temp = substring(@string, @position, 1)
        if @temp = @columnDelimiter
        begin
            set @columns = @columns + 1
            set @columnNames = @columnNames + ','
        end
        else if @temp = char(13)
        begin
            set @stop = 1
        end
        else
        begin
            set @columnNames = @columnNames + @temp
        end
        set @position = @position + 1
    end
 
    set @dataStart = @position
 
    if @rowOneIsHeader = 0
    begin
        set @dataStart = 1
        set @position = 1
        set @columnNames = ''
        while @position - 1 < @columns
        begin
            set @columnNames = @columnNames + ',Column' + cast(@position as varchar(1))
            set @position = @position + 1
        end
        set @columnNames = substring(@columnNames, 2, len(@columnNames))
    end
 
    -- Build ##tempCSVSplitToTable table
    set @sql = @sql + 'create table ##tempCSVSplitToTable (' + char(13) + '['
    set @stop = 0
    set @position = 1
    while @stop = 0
    begin
        set @temp = substring(@columnNames, @position, 1)
        if @temp <> ','
        begin
            set @sql = @sql + @temp
        end
        else
        begin
            set @sql = @sql + '] nvarchar(max),' + char(13) + '['
        end
   
        set @position = @position + 1
 
        if @position - 1 = len(@columnNames)
        begin
            set @sql = @sql + '] nvarchar(max)'
            set @stop = 1
        end
    end
    set @sql = @sql + ')' + char(13)
    exec (@sql)
 
    -- insert values into ##tempCSVSplitToTable table
    set @position = @dataStart
    while @position - 1 < len(@string)
    begin
        set @stop = 0
        set @sql = 'insert into ##tempCSVSplitToTable ([' + replace(@columnNames, ',', '],[') + ']) values ('''
        while @stop = 0
        begin
            set @temp = substring(@string, @position, 1)
 
            -- end of column
            if @temp = @columnDelimiter
            begin
                set @sql = @sql + ''','''
            end
            -- EOL
            else if @temp = char(13) or datalength(@temp) = 0
            begin
                set @stop = 1
            end
            -- deal with apostrophe in data
            else if @temp = ''''
            begin
                set @sql = @sql + ''''''
            end
            -- column data that isn't an apostrophe
            else
            begin
                set @sql = @sql + @temp
            end
 
            set @position = @position + 1
        end 
        set @sql = @sql + ''')'
 
        -- Get rid of any UTF delimiter replacements that were put in to take the place of the delimiter character within the text qualifier
        if @textQualifier <> ''
        begin
            set @sql = replace(@sql, nchar(@delimiterReplacementUTFNumber), @columnDelimiter)
        end
 
        exec(@sql)
    end
 
    select * from ##tempCSVSplitToTable
 
    -- destroy ##tempCSVSplitToTable table
    if object_id('tempdb..##tempCSVSplitToTable') is not null
    begin
        drop table ##tempCSVSplitToTable
    end
end

IE6, IE7, & IE8 On My Box … At the Same Time!

IE6IE7IE8I just installed Windows 7 on my desktop machine. Today I set up XP mode … cause I can. And the first thing I did was to create two virtual machine … one running IE6 and one running IE7. So there you have it. I can now run IE6, IE7, and IE8 on one box. The image is a screenshot of my box with the proof. This will make website debugging so much easier. Although for the most part I’ve kinda let IE6 go, but at least I can know how bad it will look.

Pivoting SQL Data

So, the other day I was trying to pivot some SQL data that was basically in this format…

RECNUMBERCOLNUMBERFIELDNAMEFIELDVALUE
11FirstNameDavin
12LastNameStuder
21FirstNameMelissa
22LastNameStuder

I wanted to see it like this …

RECNUMBERFIRSTNAMELASTNAME
1DavinStuder
2MelissaStuder

It took me a while to figure out the syntax for this, but here it is. It uses the SQL pivot keyword.

This first portion dynamically creates the column names to be used in the pivot.

declare @cols varchar(max)
 
select @cols = stuff (
    (
        select '], [' + FieldName
        from table
        group by FieldName
        order by min(ColNumber)
        for
        xml path('')
    ),
    1,
    2,
    ''
) + ']'

This next part creates a query within a string that we will run with the SQL exec command.

declare @query varchar(max)
 
set @query = '
    select RecNumber, ' + @cols + '
    from (
        select RecNumber, FieldName, FieldValue
        from table
    ) dta
    pivot
    (
        min(FieldValue)
        for FieldName in (' + @cols + ')
    ) pvt
'

All that is left to do is to execute the query that was created. I ended up putting this into a stored procedure.

exec(@query)

Google being evil … slightly?

So, I was looking at Google’s homepage in IE8 today and I noticed something funny. I was curious, so I decided to open up Google in the other major browsers to see if the funniness followed. Nope! Only in IE. Below are screenshots of the major browsers (save Google Chrome of course). See if you can spot the difference. I realize that Google can do whatever it wants with its one home page(even though Microsoft can’t do what it wants with its OS, but don’t get me started) and granted IE has its faults, but this seems a bit mean to me … especially since I was using IE8, which is the most standards compliant IE to date(meaning the features that they have implemented conform to industry standards). What do you think, is it “evil” to single out one competing browser in this way?
Firefox:
FF

Opera:
Opera

Safari:
Safari

IE8:
IE8

Clearing a float

I found out a cool way to clear a floated element the other day without having to use a clearing div/break or whatever your favorite method of clearing is. In fact you don’t have to add any extraneous html to achieve the clear. Give it a shot yourself. Here is the key …

.box {
    border: 1px solid #999999;
}
 
#header {
    width: 217px;
    margin: 0px 0px 5px 0px;
    padding: 5px;
}
 
#container {
    overflow: hidden; /*This is the magic.  It will clear the footer without needing a clearing div*/
}
 
#left {
    width: 100px;
    float: left;
    margin: 0px 0px 5px 0px;
    padding: 5px;
}
 
#right {
    width: 100px;
    float: left;
    margin: 0px 0px 5px 5px;
    padding: 5px;
}
 
#footer {
    width: 217px;
    padding: 5px;
}

The HTML

<html>
    <body>
        <div id="header" class="box">
            Header
        </div>
        <div id="container">
            <div id="left" class="box">
                Left
            </div>
            <div id ="right" class="box">
                Right
            </div>
        </div>
        <div id="footer" class="box">
            Footer
        </div>
    </body>
</html>

Chaining SQL Record Filters

So, I’ve actually been using this technique for a while, but recently on a project I have been working on I again ran into the need for it.  Many times creating an application you might find yourself with a bunch of checkboxes (or anything for that matter) that you might use to build a SQL where clause, such as …

select field1, field2, field3
from table
where
    checkbox1 = 0
    and checkbox2 = 1
    and checkbox3 = 1

The problem is that when you are building the where clause you have to check to see if you are on the first item.  If so, then do not add an “and”, otherwise add the “and”.  Or say you have a static SQL query that you often use and you want to quickly comment out the first item in the where clause but you don’t want to totally delete it.  You may have to do this …

select field1, field2, field3
from table
where
    --checkbox1 = 0
    /*and*/ checkbox2 = 1
    and checkbox3 = 1

I find this to be somewhat awkward to do.  If you are dynamically building a where clause it adds more code to have to decipher, or if you have a static query it adds unneeded edits.  I prefer the below method …

select field1, field2, field3
from table
where
    1 = 1
    and checkbox1 = 0
    and checkbox2 = 1
    and checkbox3 = 1

By adding the “1 = 1” I can simply add every piece of the where clause with the “and” in front and I don’t have check if I am on the first item or not.  Also it makes it easy to selectively comment out parts of my static query without having to do much typing as the below example shows.

select field1, field2, field3
from table
where
    1 = 1
    --and checkbox1 = 0
    and checkbox2 = 1
    --and checkbox3 = 1

For me this has been very helpful in many projects, including my current one.  Maybe somebody else will find this approach helpful as well.

Windows Live Writer and Picasa Playing Nice

Melissa and I use Picasa to sort all of our digital pictures. It’s a pretty good image cataloging program. One of the cool features that Picasa has is a button at the bottom that says “BlogThis!”. It’s pretty handy. All you have to do is select a photo or multiple photos and hit the “BlogThis!” button. It will then copy the images up to blogger and create a new post based on those images. Well, recently Melissa and I switched to using Windows Live Writer to create our blog posts. Sure, we could use Blogger’s interface, but honestly I can’t stand it, and Windows Live Writer has a pretty slick interface. Also, it will hook up to most of the major blogging engines (BloggerWordPressTypepad, etc).

Well, the only problem is that I could not find a button for Picasa that would open the images in Windows Live Writer. Bummer! So, I decided to make my own. It was a fun project. I’ve never made a Windows installer file before, so it took a bit of learning, but here is the final product. If you use Picasa and Windows Live Writer and would like to have a button in Picasa for creating blog posts in Live Writer, then click the below link. I copied the button into the Live Writer Gallery, so that everyone can create blog posts easily from Picasa.

Also, if you download this and find it useful please leave a comment on this post. It would be nice to know if people find this plugin helpful. Thanks.

Windows Live Gallery – Live Writer button for Picasa

Google Calendar

So, I’ve been working on creating a php script that pulls Google Calendar data. I couldn’t find many examples of how to do it on the web. So, after much trial and error here is what I’ve come up with. Use as you wish.

<?php
/**********************************************************/
/* The username/password used to login to Google Calendar */
/*                                                        */
/* This is the only part you should have to edit in this  */
/* script.                                                */
/**********************************************************/
$google_username = '???';
$google_password = '???';

/**************************************************************************/
/* Include Zend framework and load libraries, used to interact with Gdata */
/* Get the Zend framework at http://framework.zend.com/download           */
/**************************************************************************/
require_once 'Zend/Loader.php';
Zend_Loader::loadClass('Zend_Gdata');
Zend_Loader::loadClass('Zend_Gdata_AuthSub');
Zend_Loader::loadClass('Zend_Gdata_ClientLogin');
Zend_Loader::loadClass('Zend_Gdata_Calendar');

/******************/
/* Login to Gdata */
/******************/
$authService = Zend_Gdata_Calendar::AUTH_SERVICE_NAME;
$httpClient = Zend_Gdata_ClientLogin::getHttpClient($google_username, $google_password, $authService);

/***********************************************************************/
/* Replaces [link:url]Text[/link] with a properly formatted anchor tag */
/* The url must have the http:// or https:// at the front              */
/*                                                                     */
/* Params:                                                             */
/*  $text - String of text                                             */
/***********************************************************************/
function createLink($text) {
    $pattern1 = '/\[\/link\]/';
    $pattern2 = '/(\[link:)([^\]]*)(\])/';

    $replacement1 = '</a>';
    $replacement2 = '<a href="$2" target="_blank" rel="noopener">';

    $text = preg_replace($pattern1 , $replacement1, $text);
    $text = preg_replace($pattern2 , $replacement2, $text);

    return $text;
}

/*****************************************************************************************/
/* Replaces [email:email address]Text[/email] with a properly formatted email anchor tag */
/*                                                                                       */
/* Params:                                                                               */
/*  $text - String of text                                                               */
/*****************************************************************************************/
function createEmail($text) {
    $pattern1 = '/\[\/email\]/';
    $pattern2 = '/(\[email:)([^\]]*)(\])/';

    $replacement1 = '</a>';
    $replacement2 = '<a href="mailto:$2">';

    $text = preg_replace($pattern1 , $replacement1, $text);
    $text = preg_replace($pattern2 , $replacement2, $text);

    return $text;
}

/*****************************************************/
/* Replaces line breaks in the text with <br /> tags */
/*                                                   */
/* Params:                                           */
/*  $text - String of text                           */
/*****************************************************/
function createLineBreaks($text) {
    $text = str_replace("\n", "<br />", $text);

    return $text;
}

/*****************************************************************/
/* This function applies the formatting functions defined above. */
/*                                                               */
/* Params:                                                       */
/*  $text - String of text                                       */
/*****************************************************************/
function formatText($text) {
    $text = createLink($text);
    $text = createEmail($text);
    $text = createLineBreaks($text);

    return $text;
}

/*****************************************************************/
/* This function formats the date/time shown to the user.        */
/*                                                               */
/* Params:                                                       */
/*  $start - Starting date/time                                  */
/*  $end - Ending date/time                                      */
/*****************************************************************/
function formatDate($start, $end) {
    if (date("H:i:s", $start) == "00:00:00" && date("H:i:s", $end) == "00:00:00") //All day event
    {
        $end = $end - 1; //For all day events the end day is one greater than it really is

        if(date("M j, Y", $start) == date("M j, Y", $end)) //Single day all day event
        {
            $date = date("M j, Y", $start);
        }
        else //All day event spanning multiple days
        {
            if (date("Y", $start) == date("Y", $end))
            {
                if (date("M", $start) == date("M", $end)) //All day event spanning multiple days in same month
                {
                    $date = date("M j", $start) . " - " . date("j, Y", $end);
                }
                else //All day event spanning multiple months
                {
                    $date = date("M j", $start) . " - " . date("M j, Y", $end);
                }
            }
            else // All day event spanning multiple years
            {
                $date = date("M j, Y", $start) . " - " . date("M j, Y", $end);
            }
        }
    }
    else //Time range event
    {
        if(date("M j, Y", $start) == date("M j, Y", $end)) //Single day range event
        {
            $date = date("M j, Y (g:i a", $start) . " - " . date("g:i a)", $end);
        }
        else //Range event spanning multiple days
        {
            if (date("Y", $start) == date("Y", $end))
            {
                if (date("M", $start) == date("M", $end)) //Range event spanning multiple days in same month
                {
                    $date = date("M j (g:i a)", $start) . " - " . date("M j (g:i a), Y", $end);
                }
                else //Range  event spanning multiple months
                {
                    $date = date("M j (g:i a)", $start) . " - " . date("M j (g:i a), Y", $end);
                }
            }
            else //Range event spanning multiple years
            {
                $date = date("M j, Y (g:i a)", $start) . " - " . date("M j, Y (g:i a)", $end);
            }
        }
    }

    return $date;
}

/******************************************************************/
/* Create a definition list of the events in human-readable form. */
/* You can use CSS to style the list.                             */
/*                                                                */
/* Params:                                                        */
/* $client - GData $httpClient created above                      */
/*  $feed - The GData feed to read                                */
/******************************************************************/
function listEvents($client, $feed)
{
    $gdataCal = new Zend_Gdata_Calendar($client);
    $eventFeed = $gdataCal->getCalendarEventFeed($feed);

    $html = $html . "<dl>\n";

    foreach ($eventFeed as $event)
    {
        foreach ($event->when as $when)
        {
            $start = strtotime($when->startTime);
            $end = strtotime($when->endTime);
        }
        $date = formatDate($start, $end);

        foreach ($event->where as $where)
        {
            $location = $where->valueString;
        }

        $title = $event->title->text;
        if ($location != "")
        {
            $title = "<a href=\"" . $location . "\" target=\"_blank\">" . $title . "</a>";
        }
        $content = formatText($event->content->text);

        $html = $html . " <dt><span class=\"eventdate\">" . $date . "</span><span class=\"eventtitle\"> - " . $title . "</span></dd>\n";
        $html = $html . " <dd><span class=\"eventcontent\">" . $content . quot;</span></dd>\n";
    }

    $html = $html . "</dl>\n";

    return $html;
}
?>
<html>
    <body>
<?php
Echo listEvents($httpClient, "http://www.google.com/calendar/feeds/yourfeedhere");
?>
    </body>
</html>

Get Firebug

For those of you who are web developers there is a great extension for Firefox that has just recently come out. I’ve used it for about a week, and I am in love with it … not as much as I love my wife though. It is the greatest thing for web developers since Jolt. By the way you may want to check out their site, since it has some information about the plug in that you may not realize just playing around with it at first … there are lots of features. Go get it and try it for yourself.

Firebug