Excel User DSN Error

In a previous post, I described how to set up an ODBC data source on your machine for use in an Excel workbook (Import Data into Excel using a SQL Query). Now if you send this workbook to your colleagues and they try to refresh the data, they are likely to get the following error message:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

This is because your colleagues also need to set up the data source on their machine (step 1 of Import Data into Excel using a SQL Query). They will also need to edit the connection string in the Excel workbook. To do this, open the workbook and go to “Data”, and then “Connections”.

Select the connection in the dialog which pops up and then click on “Properties”.

In the “Properties” dialog, click on the “Definition Tab”. The connection string, which specifies information about the data source and how to connect to it, can now be seen. Your colleague will need to edit the user ID (UID) and workstation ID (WSID). They will also need to edit the DSN and Description fields if they entered a different name and description when connecting to the data source. Your colleague can find their WSID, or computer name, under System on the Control Panel.

Import Data into Excel using a SQL Query

(Excel 2010, Windows 7)
Follow the steps below if you want to import data into Excel from a SQL Server database using a SQL query.
Step 1: Create the data source on your machine.
Type “ODBC” into the Start Menu search and then click on “Data Sources (ODBC)”.

The ODBC Data Source Administrator should open.

Click on “Add” and then enter a Name and Description for the SQL Server which you wish to connect to.

When you have successfully created your data source, you should see it listed as a user DSN (Data Source Name).

Step 2: Connect to the data source from within Excel.
Open Excel. Click on the “Data” tab, then “From Other Sources” and finally “From Microsoft Query”.

Select the data source you created in step 1, un-tick “Use the Query Wizard to create/edit queries” and then click OK.

Close the “Add Tables” dialog which pops up, and then click on the “SQL” button in the “Microsoft Query” dialog.

Paste your SQL into the “SQL” dialog which pops up.

Click OK and then click OK again when asked “SQL Query can’t be represented graphically. Continue anyway?” Finally, click on the “Return Data” button to return the data to an Excel sheet.

Excel and SQL Server epoch dates and treatment of the year 1900

Excel stores a date and time as a number where the integer part represents the number of days since 1900-01-00 and the fractional part represents the time as a fraction of a 24 hour day e.g. 18/03/2014 21:09 as the number 41716.88125. Now the integer part is actually out by 1 day as Excel incorrectly assumes that the year 1900 is a leap year and so counts 1900-02-29 as a day.

So, if we calculate the number of days between the Epoch date and a test date we can see that 1900-02-29 is counted as a day:

Epoch Date	Test Date	Difference in Days
00/01/1900 00:00	28/02/1900 00:00	59
00/01/1900 00:00	01/03/1900 00:00	61

Now if we import the number representing a datetime in Excel into SQL Server and then in SQL Server convert it to a datetime, we can expect to be out by 2 days for dates after 1900-02-28: 1 day because SQL Server doesn’t count 1900-02-29 as a day plus another day because SQL Server uses 1900-01-01 as its Epoch date:

SELECT CONVERT(DATETIME, 0);
--returns 1900-01-01 00:00:00.000
SELECT DATEDIFF(DAY, CONVERT(DATETIME, 0), CONVERT(DATETIME, '1900-02-28'));
--returns 58
SELECT DATEDIFF(DAY, CONVERT(DATETIME, 0), CONVERT(DATETIME, '1900-03-01'));
--returns 59

T-SQL: Count Number of Times a Particular Character or Substring occurs in a String

To count the number of times a particular substring (e.g. ‘Two’) occurs in a string (e.g. ‘One, Two, One, Two’), follow these steps:

1. Replace each occurrence of the substring by an empty string e.g.

SELECT REPLACE('One, Two, One, Two','Two','')

2. Calculate the length of the new string (12 characters) e.g.

SELECT LEN(REPLACE('One, Two, One, Two','Two',''))

3. Subtract this from the length of the original string (18 characters) e.g.

SELECT LEN('One, Two, One, Two') - LEN(REPLACE('One, Two, One, Two','Two',''))

4. Divide the result (18 – 12 = 6) by the number of characters in the substring (6/3 = 2 occurrences) e.g.

SELECT (LEN('One, Two, One, Two') - LEN(REPLACE('One, Two, One, Two','Two','')))/LEN('Two')

The count can be made case sensitive by applying the appropriate collation e.g.

SELECT (LEN('One, Two, One, Two') - LEN(REPLACE('One, Two, One, Two' COLLATE Latin1_General_CS_AS,'Two','')))/LEN('Two')

In Excel we can use a very similar formula e.g. if our string “One, Two, One, Two” is in cell A1:

=(LEN(A1)-LEN(SUBSTITUTE(A1,"Two","")))/LEN("Two")

T-SQL: Field Terminator of a BULK INSERT occurs within a Data Field

Suppose you want to import data from an Excel worksheet into a SQL Server database table using the BULK INSERT statement. The first step would be to save the worksheet as a CSV file. You could then run the BULK INSERT statement with

FIELDTERMINATOR = ','

and

ROWTERMINATOR = '\n'

However, if the field terminator (a comma) occurs within any data field, you’ll get the error

Msg 4864, Level 16, State 1, Line 4
Bulk load data conversion error (type mismatch or invalid character for the specified codepage)…

One solution is to re-save the Excel worksheet as a CSV file but with a list separator that does not occur within any of the data fields. To change the list separator that Excel uses, follow these steps

1. Open the Control Panel
2. Navigate to Region and Language
3. Under Formats, Additional Settings, you should find List Separator
4. Change the List Separator to something that does not occur within any of the data fields

VBA to List all Files in a Particular Folder

Suppose you want to list all the files in a particular root folder, including any files in any subfolders. The VBA for doing so can be found at http://easyprograming.com/index.php/vb-script/34-list-all-files-and-subfolders-inside-a-folder-using-vbscript. Below I have taken this VBA and added an input box into which the user enters the path of the root folder e.g. C:\Documents. A CSV file called OutputFiles.csv is created to store the list of files. The location of OutputFiles.csv can be changed by editing the string “C:\OutputFiles.csv”

Option Explicit
Option Base 1
Public fso
Public ObjOutFile
Sub GetUserInput()
    Dim rootFolderPath As String
    
    'Get the user to enter the root folder
    rootFolderPath = InputBox(Prompt:="Enter root folder path: ", Title:="Enter root folder path")
    
    'Check the input is not null
    If rootFolderPath = vbNullString Then
        MsgBox "No folder address entered", vbCritical
        Exit Sub
    End If
    
    'Check the folder exists
    If Len(Dir(rootFolderPath, vbDirectory)) = 0 Then
        MsgBox "The folder " & vbCrLf & rootFolderPath & vbCrLf & " does not exist", vbCritical
        Exit Sub
    End If
    
    'Add a slash at the end of the root folder path if not present
    If Right(rootFolderPath, 1) <> "\" Then
        rootFolderPath = rootFolderPath & "\"
    End If

    'Create the File System Object
    Set fso = CreateObject("Scripting.FileSystemObject")

    'Create the output file
    Set ObjOutFile = fso.CreateTextFile("C:\OutputFiles.csv")
    
    'Write the CSV headers
    ObjOutFile.WriteLine ("Type,File Name,File Path")
    
    'Call the GetFiles function to get all files
    GetFiles (rootFolderPath)
    
    'Close the output file
    ObjOutFile.Close
    
    'Display completed message
    MsgBox "Macro completed", vbInformation
End Sub
Function GetFiles(ByVal FolderName As String)
'http://easyprograming.com/index.php/vb-script/34-list-all-files-and-subfolders-inside-a-folder-using-vbscript
    On Error Resume Next
    Dim ObjFolder
    Dim ObjSubFolders
    Dim ObjSubFolder
    Dim ObjFiles
    Dim ObjFile
    Set ObjFolder = fso.GetFolder(FolderName)
    Set ObjFiles = ObjFolder.Files
    
    'Write all files in this folder to output file
    For Each ObjFile In ObjFiles
        ObjOutFile.WriteLine ("File," & ObjFile.Name & "," & ObjFile.Path)
    Next
    
    'Get all subfolders in this folder
    Set ObjSubFolders = ObjFolder.SubFolders
    
    For Each ObjFolder In ObjSubFolders
        'Write subfolder name and path
        ObjOutFile.WriteLine ("Folder," & ObjFolder.Name & "," & ObjFolder.Path)
        
        'Call the GetFiles function to get all files
        GetFiles (ObjFolder.Path)
    Next
End Function

VBA Function for the Last Non-blank Row in a Worksheet

The VBA function below returns the number of the last row with a non-blank cell. It is a modified version of the functions found at www.ozgrid.com/VBA/ExcelRanges.htm, and includes VBA to unfilter the data before searching for the last non-blank row.

Function LastRowInWorksheet(ByRef wksheet As Worksheet) As Long
'-----------------------------------------------------------------------'
'   Returns the last row in the worksheet with a non-blank cell         '
'   http://www.ozgrid.com/VBA/ExcelRanges.htm                           '
'-----------------------------------------------------------------------'
    Dim lastRow As Long
   
    'Make all the data on the worksheet available
    On Error Resume Next
    wksheet.ShowAllData
    On Error GoTo 0
   
    'Check there are non-blank cells in the worksheet
    If Application.WorksheetFunction.CountA(wksheet.Cells) = 0 Then
        lastRow = 0
    Else
        lastRow = wksheet.Cells.Find(What:="*", After:=wksheet.Range("A1"), _
              SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious).Row
    End If

    'Assign a value to the function
    LastRowInWorksheet = lastRow
End Function

Calculating the Last Friday of the Month

Suppose you have to prepare an end of month report that only includes transactions up to the last Friday of the current month. Here I’ll explain a formula for calculating the last Friday of the month which I found on the forum http://www.ozgrid.com/forum/showthread.php?t=26622

The Excel formula is given as (slightly modified)

=DATE(YEAR(A1),MONTH(A1)+1,0)-MOD(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)+2,7) 

but what does this mean? Here’s a breakdown

Formula	Returns
DATE(YEAR(A1),MONTH(A1)+1,0)	Last day of the month
WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)	Day of the week (1 to 7)
MOD(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)+2,7)	Number of days (0 to 6) since Friday

E.g.

Today’s date	Last day of the month	Day of the week	Number of days since Friday	Last Friday of the month
16/07/2013	31/07/2013	3	5	26/07/2013

In SQL a similar formula can be used as on the forum http://stackoverflow.com/questions/13988633/last-friday-of-a-given-month-in-sql-server

The SQL query given is (slightly modified)

SELECT DATEADD(DAY,DATEDIFF(DAY,'1900-01-05',DATEADD(MONTH,DATEDIFF(MONTH,'1900-01-01',GETDATE()),'1900-01-31'))/7*7,'1900-01-05')

Here’s a breakdown of the parts

SELECT
      GETDATE() -- today
      , DATEDIFF(MONTH,'1900-01-01',GETDATE()) -- number of months between 1900-01-01 and today
      , DATEADD(MONTH,DATEDIFF(MONTH,'1900-01-01',GETDATE()),'1900-01-31') -- last day of current month
      , DATEDIFF(DAY,'1900-01-05',DATEADD(MONTH,DATEDIFF(MONTH,'1900-01-01',GETDATE()),'1900-01-31')) -- number of days between 1900-01-05 (a Friday) and last day of current month
      , DATEDIFF(DAY,'1900-01-05',DATEADD(MONTH,DATEDIFF(MONTH,'1900-01-01',GETDATE()),'1900-01-31'))/7 -- number of full weeks between 1900-01-05 (a Friday) and last day of current month
      , DATEDIFF(DAY,'1900-01-05',DATEADD(MONTH,DATEDIFF(MONTH,'1900-01-01',GETDATE()),'1900-01-31'))/7*7 -- number of days in number of full weeks between 1900-01-05 (a Friday) and last day of current month
      , DATEADD(DAY,DATEDIFF(DAY,'1900-01-05',DATEADD(MONTH,DATEDIFF(MONTH,'1900-01-01',GETDATE()),'1900-01-31'))/7*7,'1900-01-05') -- last Friday of current month