Link to home
Start Free TrialLog in
Avatar of ITSysTech
ITSysTech

asked on

Importing .mdb file into MSSQL shows code instead of text

When we try to import a .mdb file into MSSQL we get this in the comments column. 

{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Verdana;}} 

Open in new window

I've noticed we are able to change the import type but nothing seems to work.


Here is the current import type. Thanks
User generated image

Avatar of Lee
Lee
Flag of United Kingdom of Great Britain and Northern Ireland image

There are two ways to do it. Either from Access using the upsizing wizard or that's what it used to be called. In the database tools menu.

Or use the SQL import/export wizard.

How have you been doing it? 
In SQL Server Management Studio, select the database to hold the imported table(s), right-click and select Tasks and Import data.
This will open the import wizard where you can select the table(s) to import.
Avatar of ITSysTech
ITSysTech

ASKER

We are using SQL import/export wizard. On my first post I attached a photo of what the column import looks like. The issue happens after we used the SQL import/export wizard. thanks
And? That is Rich Text.
It's imported correctly. It's the raw RTF.
If you want the text, and not the RTF, then you will probably need to set the fields text format property to plain text or something other than rich text before you export/import. Be careful though because it might be in RTF for a reason. Back it up first, and have a go.
I've tried using text and ntext but no joy.
If you wish plain text, you must convert/decode it first. Previously, it could be done with a function like this:

Public Function ConvRTFtoText( _
  ByVal strRTF As String, _
  Optional booSingleRun As Boolean) _
  As String

' Converts RTF formatted string to plain text using RTF ActiveX control.
' When finished, Rich Text object is removed if booSingleRun is True.
'
' 2001-05-27. Cactus Data ApS, CPH.
  
  Static objRTF As Object
  Static booErr As Boolean
  Dim strText   As String
  
  On Error GoTo Err_ConvRTFtoText
  
  If objRTF Is Nothing Then
    Set objRTF = CreateObject("RICHTEXT.RichtextCtrl")
  End If
  
  With objRTF
    .TextRTF = strRTF
    strText = .Text
  End With
  
  If booSingleRun = True Then
    Set objRTF = Nothing
  End If
  
  ConvRTFtoText = strText
  
Exit_ConvRTFtoText:
  Exit Function

Err_ConvRTFtoText:
  If booErr = False Then
    MsgBox "Error " & Err.Number & ". " & Err.Description & "!", _
           vbExclamation + vbOKOnly, _
           "Rich Text converter"
    ' Only show error message once per session.
    booErr = True
  End If
  Resume Exit_ConvRTFtoText
  
End Function

Open in new window

but I'm not sure the RichText control is present on newer systems, at least not in my 64-bit setup.
ASKER CERTIFIED SOLUTION
Avatar of Lee
Lee
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
We are importing the .mdb file from an old program. If I open the .mdb file in access it shows the raw RTF format as well. We might need to look into why the original program is outputting the raw RTF. Thanks