cmiles – blog

I frequently encounter Excel Worksheets with Smart Tags scattered around wanting very badly to share their ‘Number Stored as Text’warning. Sometimes this warning is completely incorrect – the cell value really is a string even though it looks like a number (UPCs and US Zip Codes come to mind); sometimes the cell value really should be should be a number…

The problem with numeric values identified as ‘Numbers Stored as Text’is that they can cause trouble with formulas, sorting and PivotTables – a broad enough range of activities/items to cause problems for just about anyone using the data.

Unfortunately simply changing the number format or alignment of the cells involved does not solve the problem. I like the information in the links below – they cover most of the helpful answers/suggestions that I have seen:
Daily Dose of Excel – Number Stored as Text (the post AND comments are good reading)
Jim’s Help Pages – Problems with imported data (also very good reading: Excel KB articles)

Notes On Solutions:
[please see the comments below from Gary Bouwman for some interesting information about these solutions and working in other languages]

‘Number Stored as Text’Smart Tag Menu -
This works, but there are often better solutions. If you only have a few cells to convert the context menu from the Smart Tag may work for you (and does not require any code) – but with large ranges the conversion process can be slower than some of the methods detailed below. (Selection hint: When selecting ranges that you want to convert via the Smart Tag Menu make sure that the Smart Tag comes up on the first cell you select. If the start of your selection is a ‘normal’cell the Smart Tag will not appear, even if your final selection includes cells that trigger the Smart Tag) (Note: Smart Tags will not appear in older versions of Excel!)

Formulas -
The links above mention the use of formulas – I think that Paste Special is usually a better option. Paste Special does not require you to find room for an extra row/column for your formulas – and (depending on your needs) does not require extra effort/key strokes to get the final version of the data into the correct position on the sheet or converted from formulas into values.

Paste Special -
The links above detail using paste special – a very good solution! Paste Special is available directly in the UI and is quite fast and easy – in code it can also be a good solution. The Daily Dose of Excel article specifically recommends the combination of ‘Copy Blank Cell/Paste Special/Add’. The ‘Copy Blank Cell/Paste Special/Add’combination usually is the best – ‘Copy Cell with Value of 1/Paste Special/Multiply’is also effective but can convert blank cells to zeros which is a problem in some data. (Side Note: In code I dislike needing to find a blank cell to copy before the paste special, but I have NEVER worked with a sheet that has every cell filled so it would not be hard to find a blank cell – this is purely a matter of taste…)

cell.Value = CDbl(Cell.Value) -
This style of coding (which could be any number of conversions such as CInt) does the job – but I have found it to be slow with large amounts of data.

[Range].Value = [Range].Value -
This solution is simple, fast and usually a very good option. I love the simplicity of this code – unfortunately it does not work on one of the reports I frequently use. I have not read about other people having failures – but for me [Range].Value = [Range].Value fails consistently on data I need to use. Because of the problems I have had I tend to use TextToColumns (which I have not (yet) seen fail).

TextToColumns -
This is an interesting method that runs quite quickly. TextToColumns works on a single column at a time and is a decent solution both from code and through the UI. The heart of the vb.net code that I use is below. This code is much more complex than [Range].Value = [Range].Value and the range that can be used is limited to a continuous selection in a single column – but for me TextToColumns has proven to be more robust than [Range].Value = [Range].Value, faster than [CellRange].Value = CDbl([CellRange].Value) and convenient since I am usually dealing with entire columns of a table.

This code block needs two variables defined: rangeToConvert (an Excel.Range that must be a continuous selection in a single column) and Delimiter (String).

    Dim foundPreExistingDelimiter As Excel.Range = _
      rangeToConvert.Find(What:=Delimiter, _
        After:=rangeToConvert.Cells(1, 1), _
        LookIn:=Excel.XlFindLookIn.xlValues, _
        LookAt:=Excel.XlLookAt.xlPart, _
        SearchOrder:=Excel.XlSearchOrder.xlByRows, _
        SearchDirection:=Excel.XlSearchDirection.xlNext, _
        MatchCase:=False)
 
    If foundPreExistingDelimiter IsNot Nothing Then
      Throw New System.ArgumentException( _
      "Tab Delimiter used in the TextToColumns function " & _
      "is found in the Range to Convert.")
    End If
 
    'The optional Fiedinfo:= is ommitted, I could not think of 
    'a use beyond the 'general' format since this is meant to 
    'eliminate Numbers Stored as Text rather than wrap 
    'TextToColumns()
 
    rangeToConvert.TextToColumns(Destination:=rangeToConvert, _
      DataType:=Excel.XlTextParsingType.xlDelimited, _
      TextQualifier:=Excel.XlTextQualifier.xlTextQualifierNone, _
      ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
      Comma:=False, Space:=False, Other:=False, OtherChar:=Delimiter, _
      TrailingMinusNumbers:=True)

TextToColumns could be a real mess if the parameters given to TextToColumns cause some of your cells to be split into multiple values (the main purpose of this function after all…) – I decided to deal with this potential problem by coding the delimiter character as a variable and checking to see if it exists in the rangeToConvert. If the delimiter is found an exception is thrown and the conversion is stopped (from a calling routine the exception makes it easy to wrap the conversion in a try-catch block and surround it with a For-Each loop that runs thru a list of possible delimiter characters).

Hope this was useful -
CM

Filed under: .net, Excel

This idea and code is super simple – but I use it multiple times each day and thought that someone might enjoy it. This routine loops thru each cell in the current selection, if the cell has a value it is skipped – if it does not have a value the value from the cell above it is copied into the cell. The same results can be achieved with copy/paste or dragging – but for the kind of data I work with filling from above is often much more efficient. (note – this routine can be a slow, but to keep things light and simple I have not bothered with turning off screen updating or other Excel modes that can increase speed).

    Dim completeRange As Excel.Range = rng(xlApp.Selection)

    For Each loopCell As Excel.Range In completeRange

      Dim currentString As String

      Try
        currentString = valstr(loopCell)
      Catch ex As Exception
        Continue For
      End Try

      If currentString = "" Then
        loopCell.Value = loopCell.Offset(-1, 0).Value
      End If

    Next loopCell

    Interaction.AppActivate("Microsoft Excel")

I use Interaction.AppActivate(“Microsoft Excel”) to return focus to Excel from my form and save an extra keystroke.

This code uses a helper function (below) to get the string value from the range.

    Public Shared Function valstr(ByVal RangeToConvert As Excel.Range) _
     As String

        Dim StringResult As String = CType(RangeToConvert.Value, String)

       Return StringResult

    End Function

Enjoy!
CM

Filed under: .net, Excel

PivotTables are a powerful tool in Excel – one interesting feature is the ability to use large sets of data without having the raw data present on an Excel Worksheet or available through the connection types that Excel/Microsoft provide. This expands the number of possible datasources and frees the PivotTable from the data storage limits of a single worksheet.

The PivotCache.Recordset property is the key to this functionality, it takes an ADO recordset and uses it as the data for the PivotTable (from your language of choice!). Good stuff – but this poses an immediate problem in .net code if you are restricted to/committed to getting your data via ADO.NET – how to create the ADO recordset?

There are two possible solutions that I have found –

XML Conversion
One approach to the problem is to use XML and transform the data. I think the links below provide good information and code for this method.

http://support.microsoft.com/kb/316337/
http://www.codeproject.com/dotnet/ADOConversion.asp
http://codebetter.com/blogs/brendan.tompkins/archive/2004/04/27/12229.aspx

‘Direct’ Conversion
The XML solutions above are fascinating – but in my own code I liked the approach that is covered in this article:

http://www.codeproject.com/cs/database/DataTableToRecordset.asp

My coding skills are NOT good enough to easily follow the xml conversion examples – likely part of the reason that I am partial to the ‘Direct’ approach. However, even if your xml coding knowledge is vastly superior to mine, it is interesting to notice the difference in code length and complexity between the XML solutions (longer/more complex) and direct conversion method (shorter/simple!).

I have used the code below for several months in an application that: creates a datatable from a query to a user-instance of SQL Express 2005 (using ADO.NET), converts the datatable (with the code below) into an ADO recordset and pushes it into an Excel PivotTable via the RecordSet property. Performance may be an issue in some situations – in my application there is certainly a pause during the conversion process, but at this point it seems acceptable (the conversion does not seem to take any longer than getting the queried data from the DB).

Imports System.Data
Imports System.IO
Imports Microsoft.Office.Interop
 
 
Public Class ConvertDataTableToAdoRs
 
  Public Shared Function ConvertToRecordset(ByVal inTable As DataTable) _
    As ADODB.Recordset
 
    '=============================================
    '
    'This is a VB conversion of the code found here:
    'http://www.codeproject.com/cs/database/DataTableToRecordset.asp 
    'Please see the original link for a C# version and to read the 
    'original article.
    '
    '=============================================
 
    Dim result As ADODB.Recordset = New ADODB.Recordset()
    result.CursorLocation = ADODB.CursorLocationEnum.adUseClient
 
    Dim resultFields As ADODB.Fields = result.Fields
    Dim inColumns As System.Data.DataColumnCollection = inTable.Columns
 
    For Each inColumn As DataColumn In inColumns
      resultFields.Append(inColumn.ColumnName, _
          TranslateType(inColumn.DataType), _
          inColumn.MaxLength, _
          ADODB.FieldAttributeEnum.adFldIsNullable, _
          Nothing)
    Next
 
    result.Open(System.Reflection.Missing.Value _
            , System.Reflection.Missing.Value _
            , ADODB.CursorTypeEnum.adOpenStatic _
            , ADODB.LockTypeEnum.adLockOptimistic)
 
    For Each dr As DataRow In inTable.Rows
      result.AddNew(System.Reflection.Missing.Value, _
                System.Reflection.Missing.Value)
 
      For columnIndex As Integer = 0 To inColumns.Count - 1
        resultFields(columnIndex).Value = dr(columnIndex)
      Next
    Next
 
    Return result
  End Function
 
 
 
  Shared Function TranslateType(ByVal columnType As Type) As ADODB.DataTypeEnum
    Select Case columnType.UnderlyingSystemType.ToString()
 
      '=============================================
      '
      'This is a VB conversion of the code found here:
      'http://www.codeproject.com/cs/database/DataTableToRecordset.asp 
      'Please see the original link for a C# version and to read the 
      'original article.
      '
      '=============================================
 
      Case "System.Boolean"
        Return ADODB.DataTypeEnum.adBoolean
 
      Case "System.Byte"
        Return ADODB.DataTypeEnum.adUnsignedTinyInt
 
      Case "System.Char"
        Return ADODB.DataTypeEnum.adChar
 
      Case "System.DateTime"
        Return ADODB.DataTypeEnum.adDate
 
      Case "System.Decimal"
        Return ADODB.DataTypeEnum.adCurrency
 
      Case "System.Double"
        Return ADODB.DataTypeEnum.adDouble
 
      Case "System.Int16"
        Return ADODB.DataTypeEnum.adSmallInt
 
      Case "System.Int32"
        Return ADODB.DataTypeEnum.adInteger
 
      Case "System.Int64"
        Return ADODB.DataTypeEnum.adBigInt
 
      Case "System.SByte"
        Return ADODB.DataTypeEnum.adTinyInt
 
      Case "System.Single"
        Return ADODB.DataTypeEnum.adSingle
 
      Case "System.UInt16"
        Return ADODB.DataTypeEnum.adUnsignedSmallInt
 
      Case "System.UInt32"
        Return ADODB.DataTypeEnum.adUnsignedInt
 
      Case "System.UInt64"
        Return ADODB.DataTypeEnum.adUnsignedBigInt
 
    End Select
 
    'Note Strings are not cased and will return here:
    Return ADODB.DataTypeEnum.adVarChar
 
  End Function
 
 
  Public Shared Sub DataTableToRange(ByVal anchorCell As Excel.Range, _
  ByVal tableToCopy As DataTable, _
  Optional ByVal tableHeader As String = "")
 
    If tableHeader <> "" Then
      Try
        anchorCell.Value = tableHeader
        anchorCell = anchorCell.Offset(1, 0)
      Catch ex As Exception
      End Try
    End If
 
    Dim tableHeaderOffset As Integer = 0
 
    For Each loopHeaders As DataColumn In tableToCopy.Columns
      Try
        anchorCell.Offset(0, tableHeaderOffset).Value = loopHeaders.ColumnName
      Catch ex As Exception
      End Try
 
      tableHeaderOffset += 1
 
    Next
 
    anchorCell.Offset(1, 0).CopyFromRecordset(ConvertToRecordset(tableToCopy))
 
  End Sub
 
End Class

The last function in this class is an interesting way to get information in Excel – certainly there are many other ways to get data into Excel from a DataTable without conversion to an ADO recordset. However, DataTableToRange is quick and easy with the conversion code written and ready-to-use.

Comments welcome!
CM

Filed under: .net, Excel

This past week I have revisited some of the different techniques for moving Excel data into a .net datatable in an effort to improve the speed/reliability of my code. In a perfect world Excel would only be a destination for data, I think there is a certain amount of futility in using Excel as a data source – but in practice Excel often seems like the only available option to collect a wide variety of data (reports to Excel seem common – end-user direct database access on the other hand…).
This post does not contain any information that cannot be found in other articles/posts/blogs, but I thought an overview with links might be useful.

The most useful list I have ever seen of links relevant to this topic:
http://blogs.msdn.com/pranavwagh/articles/excel_ado.aspx

The title of the post is ‘USING ADO AND ADO.NET WITH EXCEL: Resources and Known Issues’ (it also covers automation and general excel information).

Methods-Notes-Ideas

Jet/ADO.NET

There are many examples online using Jet (I think the two links below give enough examples for a good overview). Jet is fast and can query saved/closed files. If your data is very consistent (no values in a single column that Jet will confuse the type of) and you are interested in saved files (rather than information in a running instance of Excel) this may be a good choice – it certainly seems to retrieve data quite quickly.

The chance that Jet will incorrectly identify the data type of a column and introduce errors into the data (esp. in situations where the exact data is unpredictable (dynamic!)) makes me reluctant to use this solution. Also, this style solution may be a poor choice for the end user if you want to import what they see on screen (the file on-screen must be saved before changes can be read, it does not seem like a good user-interface choice to me (and automation of saving the file seems problematic/dangerous)). These links have good information:
http://blog.lab49.com/?p=196
http://support.microsoft.com/?scid=kb;en-us;316934&spid=1249&sid=global

Range.Value/Arrays

Excel can return a range of values as a 2-dimensional Object array. This method is fairly fast (although maybe not seem quite as fast as Jet), and the values are far more predictable (in my opinion). This solution is interesting when you want to extract the information a user is seeing on-screen. For the most part this solution is direct and simple (although beware confusing differences formatting can cause between .value and displayed value).
I like this solution and have been using it frequently. For small sets of cells it may be just as effective to loop through each cell in the range and retrieve the range.value – this is even slower (and less reliable see: http://support.microsoft.com/kb/216400/) – but may allow you to bypass the Object array and go directly into your DataTable or list. This link has code examples:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q302094

Taking Information from the Clipboard

M.B. mentioned this solution to me before I saw an online code example. This is a simple idea, copy the data to the clipboard in Excel and then pull it off the clipboard in .net. This is an interesting solution, the .net application does not need an Excel reference (no version conflicts and pias!) and you could write your program to take information from a wide variety of programs. I like the user-interface options – most users are comfortable with copy/paste . The link below will get you started:

http://www.codeguru.com/vb/controls/vbnet_controls/datagridcontrol/article.php/c6393/

The problems I have had with this technique are related to the choice of formats available on the clipboard. Csv seems to be the best choice (other opinions?), which means that commas in your data will be a problem. Replacing commas in Excel with a substitute string (and then reversing the replace in .net) is a possibility, but it detracts from the simplicity of the solution.
I think that cell formats can be more problematic here than in the range.value methods (maybe my prejudice from seeing monetary values so often – the copy to clipboard will can pick up the dollar sign, very likely not what you want – although likely a pretty safe string manipulation to remove…)

Here is the code that I use to go from an excel range (tableRange in the example – which is declared at the class level in my code) to a streamreader (for use in .net) via the clipboard.

  Public Function WftToStreamreaderViaClipboardCsv( _
    ByVal commaSubstituteString As String) _
    As StreamReader
 
    'Because the .Find inherits the current settings I set everything each time.
    'Later I will transform the stream with:
    '
    '   Dim commaSepChar As Char = ","c
    '   splitArray = rowFromStream.Split(commaSepChar)
    '
    '   For loopElements As Integer = 0 To ColumnCount - 1
    '     splitArray(loopElements) = _
    '       splitArray(loopElements).Replace(commaSubstituteString, ",")
    '   Next
    '
    'Because of the coding choice I throw an exception if the range contains the 
    'comma substitution string even if commas are not present in the data.
 
    Dim foundPreExistingSubstitutionString As Excel.Range = _
      _tableRange.Find(What:=commaSubstituteString, _
        After:=_tableRange.Cells(1, 1), _
        LookIn:=Excel.XlFindLookIn.xlValues, _
        LookAt:=Excel.XlLookAt.xlPart, _
        SearchOrder:=Excel.XlSearchOrder.xlByRows, _
        SearchDirection:=Excel.XlSearchDirection.xlNext, _
        MatchCase:=False)
 
    If foundPreExistingSubstitutionString IsNot Nothing Then
      Throw New ArgumentException("The Comma Substitution String" _
        & "is found in the original text and are not valid.")
      Return Nothing
    End If
 
    Dim rangeWithCommas As Excel.Range = _tableRange.Find(What:=",", _
        After:=_tableRange.Cells(1, 1), _
        LookIn:=Excel.XlFindLookIn.xlValues, _
        LookAt:=Excel.XlLookAt.xlPart, _
        SearchOrder:=Excel.XlSearchOrder.xlByRows, _
        SearchDirection:=Excel.XlSearchDirection.xlNext, _
        MatchCase:=False)
 
    Dim reverseCommaSubstitutionNeeded As Boolean = False
 
    Do While rangeWithCommas IsNot Nothing
      'Although the find should be limited to finding xlValues this is
      'a x2 that a formula is not being modified. The Try...Catch block
      'is cautious but better than false results.
 
      If CType(rangeWithCommas.HasFormula, Boolean) = False Then
        Dim stringToModify As String = ""
        Try
          stringToModify = CType(rangeWithCommas.Value, String)
        Catch ex As Exception
          Throw
        End Try
 
        rangeWithCommas.Value = stringToModify.Replace(",", _
          commaSubstituteString)
        reverseCommaSubstitutionNeeded = True
      End If
 
      rangeWithCommas = _tableRange.Find(What:=",", After:=rangeWithCommas, _
        LookIn:=Excel.XlFindLookIn.xlValues, LookAt:=Excel.XlLookAt.xlPart, _
        SearchOrder:=Excel.XlSearchOrder.xlByRows, _
        SearchDirection:=Excel.XlSearchDirection.xlNext, _
        MatchCase:=False)
 
    Loop
 
    _tableRange.Copy()
 
    If Clipboard.ContainsData(DataFormats.CommaSeparatedValue) = False Then
      Return Nothing
    End If
 
    Dim streamFromClipboard As New StreamReader _
        (CType(Clipboard.GetData(DataFormats.CommaSeparatedValue), Stream))
 
    If reverseCommaSubstitutionNeeded Then
      'Returns the Excel values to their original with comma values
 
      rangeWithCommas = _tableRange.Find(_tableRange.Find( _
        What:=commaSubstituteString, _
        After:=_tableRange.Cells(1, 1), _
        LookIn:=Excel.XlFindLookIn.xlValues, _
        LookAt:=Excel.XlLookAt.xlPart, _
        SearchOrder:=Excel.XlSearchOrder.xlByRows, _
        SearchDirection:=Excel.XlSearchDirection.xlNext, _
        MatchCase:=False))
 
      Do While rangeWithCommas IsNot Nothing
        If CType(rangeWithCommas.HasFormula, Boolean) = False Then
          Dim stringToModify As String = ""
          Try
            stringToModify = CType(rangeWithCommas.Value, String)
          Catch ex As Exception
            Throw
          End Try
          rangeWithCommas.Value = _
            stringToModify.Replace(commaSubstituteString, ",")
        End If
 
        rangeWithCommas = _tableRange.Find(What:=commaSubstituteString, _
        After:=rangeWithCommas, _
        LookIn:=Excel.XlFindLookIn.xlValues, _
        LookAt:=Excel.XlLookAt.xlPart, _
        SearchOrder:=Excel.XlSearchOrder.xlByRows, _
        SearchDirection:=Excel.XlSearchDirection.xlNext, _
        MatchCase:=False)
      Loop
 
    End If
 
    Return streamFromClipboard
 
  End Function

Other Options

I am not familiar with VSTO but I believe that the databinding of named ranges might be worth looking into if you/your target machines are all running Excel 2003(+) and the cost of VSTO is not an issue.

Save as – Excel has a number of save as options (such as tab-delimited) that can be useful to work with.

XML – XML is becoming more important in Office – there may be some possibilities here although I would guess solutions are going to be pretty specific to various office versions for the time being.

DTS/Sql Server – this steps out a little from .net DataTables and overlap with the Jet discussion but may be options also depending on the project.

All comments welcome,

CM

Filed under: .net, Excel