CODEWITHSUNDEEP
sql-server-2008excel-2010
Martin Smellworse
Martin Smellworse

Reputation: 1752

remove all non printing characters from spreadsheet

I need to import the data in an Excel spreadsheet to SQL Server 2008. 88 columns and about 5 thousand rows.

All sorts of hidden characters seem to be causing problems.

I have no experience of Excel. I read that CLEAN(A1) will remove all non printing characters from the cell.

How do you remove non printing characters from a whole spreadsheet?

Will CLEAN remove the double inverted commas that appear around some of the data when I import it into SQL Server (these are not visible in the spreadsheet). These seem to cause issues. In SQL Server you see a double inverted comma at the beginning of the field, but the data is truncated etc.

Upvotes: 1

Views: 1688

Answers (1)

chuff
chuff

Reputation: 5876

CLEAN removes the non-printing characters with ASCII codes 1 to 32. It's worth trying to import a small CLEAN'ed test dataset which you know has hidden characters to see whether those characters are the source of the problem.

The easiest way to apply CLEAN to a whole worksheet is to insert an additional worksheet into the workbook, enter =CLEAN(Sheet1!A1) into cell A1 of the new worksheet, and copy the formula down and across to convert all the data cells in the original sheet.

Another option would be to save the data sheet as a PRN, TXT, or CSV file (which also will eliminate the non-printing characters), and then try to import the new file, or a test version of it, to your database. As you undoubtedly know, these file types are options in the "Save as type" dropdown menu when you use Save As rather than Save to save the worksheet.

Finally, if you are still having trouble - or just want jump to the chase - the following procedure strips out everything except the "keyboard" characters (Asc 33-126) and writes the result to another sheet. You will need to adjust the name of the source sheet and range and the name of the destination sheet.

Sub CleanHighChars()
    Dim srcRng As Range, destRng As Range
    Dim strArray As Variant
    Dim i As Long, j As Long, k As Long
    Dim numRows As Long, numCols As Long
    Dim ascNum As Long
    Dim str As String, newStr As String, char As String

'   // Source sheet and range
    Set srcRng = Worksheets("Sheet1").Range("A1:A100")
    numRows = srcRng.Rows.Count
    numCols = srcRng.Columns.Count

'   // Destination sheet
    With Worksheets("Sheet2")
        Set destRng = .Range(.Cells(1, 1), .Cells(numRows, numCols))
    End With
    strArray = srcRng
    For i = 1 To numRows
        For j = 1 To numCols
            str = strArray(i, j)
            For k = 1 To Len(str)
                char = Mid(str, k, 1)
                ascNum = Asc(char)
                If ascNum > 32 And ascNum < 127 Then
                    newStr = newStr & char
                End If
            Next k
            strArray(i, j) = newStr
            newStr = vbNullString
        Next j
    Next i
    destRng = strArray
End Sub

Upvotes: 1

Related Questions