Older versions of the ERP package that I work with stored lengthy text data in LONG RAW columns. Newer versions of the ERP package store lengthy text data in BLOB columns. When tables containing those columns are queried using the Microsoft Query tool, which allows the data stored in tables to be retrieved into an Excel worksheet, the text found within the LONG RAW and BLOB columns appears in the Microsoft Query preview window, but those columns are silently excluded when the data is brought back into Microsoft Excel. There must be a work around. Sure, there is an easy solution for BLOB columns using a combination of the UTL_RAW.CAST_TO_VARCHAR2 and DBMS_LOB.SUBSTR functions, but is there a solution for LONG RAW columns?
First, we will build a table to somewhat mimic a table found in the old version of the ERP package (I think that I only omitted the WORKORDER_SUB_ID column):
CREATE TABLE T5( WORKORDER_TYPE CHAR(1), WORKORDER_BASE_ID VARCHAR2(30), WORKORDER_LOT_ID VARCHAR2(3), WORKORDER_SPLIT_ID VARCHAR2(3), SEQUENCE_NO NUMBER(12,0), TYPE CHAR(1), BITS_LENGTH NUMBER(12,0), BITS LONG RAW);
Now, let’s build a bit of code using the Visual Basic for Applications macro editor in Excel to populate the table. I will use late binding, like what is required in VBS macros, so that I can use ADO without adding ADO references in the Visual Basic editor (see the previous articles in the Excel category for directions that permit early binding).
Const adCmdText = 1
Const adCmdStoredProc = 4
Const adParamInput = 1
Const adVarNumeric = 139
Const adBigInt = 20
Const adDecimal = 14
Const adDouble = 5
Const adInteger = 3
Const adLongVarBinary = 205
Const adNumeric = 131
Const adSingle = 4
Const adSmallInt = 2
Const adTinyInt = 16
Const adUnsignedBigInt = 21
Const adUnsignedInt = 19
Const adUnsignedSmallInt = 18
Const adUnsignedTinyInt = 17
Const adDate = 7
Const adDBDate = 133
Const adDBTimeStamp = 135
Const adDBTime = 134
Const adVarChar = 200
Const adUseClient = 3
Const adOpenKeyset = 1
Const adLockOptimistic = 3
Sub CreateRows()
Dim i As Integer
Dim strUsername As String
Dim strPassword As String
Dim strDatabase As String
Dim strSQL As String
Dim intLength As Integer
Dim strTempBits As String
Dim bytBits() As Byte 'An array of bytes
Dim dynData As Object 'ADO Recordset object used to retrieve the user's data
Dim dbDatabase As Object 'ADO database connection object
On Error Resume Next
Set dynData = CreateObject("ADODB.Recordset")
Set dbDatabase = CreateObject("ADODB.Connection")
strDatabase = "MyDB"
strUsername = "MyUser"
strPassword = "MyPassword"
'Connect to the database
'Oracle connection string
dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";ChunkSize=1000;FetchSize=100;"
dbDatabase.ConnectionTimeout = 40
dbDatabase.CursorLocation = adUseClient
dbDatabase.Open
'Retrieve a recordset with 0 rows that will allow us to insert into the table
strSQL = "SELECT"
strSQL = strSQL & " WORKORDER_TYPE," & vbCrLf
strSQL = strSQL & " WORKORDER_BASE_ID," & vbCrLf
strSQL = strSQL & " WORKORDER_LOT_ID," & vbCrLf
strSQL = strSQL & " WORKORDER_SPLIT_ID," & vbCrLf
strSQL = strSQL & " SEQUENCE_NO," & vbCrLf
strSQL = strSQL & " TYPE," & vbCrLf
strSQL = strSQL & " BITS_LENGTH," & vbCrLf
strSQL = strSQL & " BITS" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & " T5" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & " 1=2"
dynData.Open strSQL, dbDatabase, adOpenKeyset, adLockOptimistic, adCmdText
For i = 1 To 1000
dynData.AddNew
dynData("workorder_type") = "W"
dynData("workorder_base_id") = "WO" & String(6 - Len(Format(i)), "0") & Format(i)
dynData("workorder_lot_id") = "1"
dynData("workorder_split_id") = "0"
dynData("sequence_no") = 10
dynData("type") = "D"
strTempBits = "WO" & String(6 - Len(Format(i)), "0") & Format(i) & "/" & "1" & " This is a very long description." & String(1000, ".")
bytBits = StrConv(strTempBits, vbFromUnicode)
intLength = Len(strTempBits)
dynData("bits_length") = intLength
dynData.Fields("bits").AppendChunk bytBits
dynData.Update
Next i
dynData.Close
dbDatabase.Close
Set dynData = Nothing
Set dbDatabase = Nothing
End Sub
When the above code is executed, the test table T5 should contain 1,000 rows. Now let’s see if we are able to retrieve the rows from the database into Excel’s worksheet. We will start out by switching to the Data tab in Excel (2010) and then select From Microsoft Query:
The next step is to select a suitable (32 bit, even on 64 bit computers) ODBC entry for the database.
Now let’s enter a simple SQL statement to retrieve the data contained in the test table T5 – note that Microsoft Query will complain if the ; character is included at the end of the SQL statement, but this is done to try to keep Microsoft Query from attempting to re-write the SQL statement (this occasionally eliminates a couple of problems):
As you can see, the Microsoft Query preview window shows the text data that is contained within the LONG RAW BITS column as we had hoped:
Now if we tell Microsoft Query to return the rows to Excel, we see that the LONG RAW BITS column was thrown away:
No problem, we will just try a trick to convert the LONG RAW column to a BLOB using the TO_LOB function and then convert the resulting BLOB to a VARCHAR2. However, that trick simply does not work because the TO_LOB function can only be used in a INSERT INTO … SELECT, or CREATE TABLE … AS SELECT type SQL statement according to the documentation:
So, let’s create a new table to allow us to temporarily convert the LONG RAW column to a BLOB column:
Now, back in Microsoft Query, we change the SQL statement as follows:
SELECT WORKORDER_TYPE, WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, SEQUENCE_NO, TYPE, BITS_LENGTH, UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BITS,32000,1)) BITS FROM T5_TEMP WHERE TYPE='D';
When we return the query results into Excel this time, the LONG RAW column that was converted to a BLOB column in the second table, has its column values converted to a VARCHAR2, and those values actually makes it into Excel (although the column alias “BITS” is lost):
But, I don’t want to go through the process of creating a table to temporarily hold the results of a LONG RAW to BLOB conversion so that I can display the characters in Excel – that would be far too messy if there were many tables. If we tell Excel to record a macro while we bring in data using the Microsoft Query tool, we see a macro that looks like this:
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=MyODBC;UID=MyUser;;DBQ=MyDB;DBA=W;APA=T;EXC=F;FEN=T;QTO=F;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NL" _
), Array("S;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F;")), _
Destination:=Range("$A$1")).QueryTable
.CommandText = Array( _
"SELECT" & Chr(13) & "" & Chr(10) & " WORKORDER_TYPE," & Chr(13) & "" & Chr(10) & " WORKORDER_BASE_ID," & Chr(13) & "" & Chr(10) & _
" WORKORDER_LOT_ID," & Chr(13) & "" & Chr(10) & " WORKORDER_SPLIT_ID," & Chr(13) & "" & Chr(10) & " SEQUENCE_NO," & _
Chr(13) & "" & Chr(10) & " TYPE," & Chr(13) & "" & Chr(10) & " BITS_LENGTH," & Chr(13) & "" & Chr(10) & _
" UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BITS,32000,1)) " & Chr(13) & "" & Chr(10) & "FROM" & Chr(13) & "" & Chr(10) & "" _
, " T5_TEMP" & Chr(13) & "" & Chr(10) & "WHERE" & Chr(13) & "" & Chr(10) & " TYPE='D';")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_OR1125"
.Refresh BackgroundQuery:=False
End With
While the above is interesting (and probably explains why the BITS column alias was lost), it is probably of little help for our problem. We need something better, our own macro, something like the following (note that the following macro writes the data to a text file, and then uses an Excel function to quickly bring that text file into an Excel worksheet. This approach should be much faster than visiting each cell in the worksheet to write the query results to each cell).
Sub DisplayData()
Dim i As Integer
Dim lngRow As Long
Dim intFileNum As Integer
Dim strUsername As String
Dim strPassword As String
Dim strDatabase As String
Dim strSQL As String
Dim strOut As String
Dim intLength As Integer
Dim strTempBits As String
Dim snpData As Object 'ADO Recordset object used to retrieve the user's data
Dim dbDatabase As Object 'ADO database connection object
On Error Resume Next
Set snpData = CreateObject("ADODB.Recordset")
Set dbDatabase = CreateObject("ADODB.Connection")
strDatabase = "MyDB"
strUsername = "MyUser"
strPassword = "MyPassword"
'Connect to the database
'Oracle connection string
dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";ChunkSize=1000;FetchSize=100;"
dbDatabase.ConnectionTimeout = 40
dbDatabase.CursorLocation = adUseClient
dbDatabase.Open
'Retrieve a recordset with 0 rows that will allow us to insert into the table
strSQL = "SELECT"
strSQL = strSQL & " WORKORDER_TYPE," & vbCrLf
strSQL = strSQL & " WORKORDER_BASE_ID," & vbCrLf
strSQL = strSQL & " WORKORDER_LOT_ID," & vbCrLf
strSQL = strSQL & " WORKORDER_SPLIT_ID," & vbCrLf
strSQL = strSQL & " SEQUENCE_NO," & vbCrLf
strSQL = strSQL & " TYPE," & vbCrLf
strSQL = strSQL & " BITS_LENGTH," & vbCrLf
strSQL = strSQL & " BITS" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & " T5" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & " TYPE='D'"
snpData.Open strSQL, dbDatabase
Application.ScreenUpdating = False
lngRow = 1
intFileNum = FreeFile
Open "C:\LongRawToVarchar.txt" For Output As #intFileNum
strOut = ""
For i = 0 To snpData.Fields.Count - 1
strOut = strOut & snpData.Fields(i).Name & vbTab
'ActiveSheet.Cells(lngRow, i + 1).Value = snpData.Fields(i).Name
Next i
Print #intFileNum, strOut
Do While Not snpData.EOF
lngRow = lngRow + 1
strOut = ""
For i = 0 To snpData.Fields.Count - 2 'All Except the last column
If Not (IsNull(snpData.Fields(i).Value)) Then
'Switch out Ascii 13 & Ascii 10 combinations for just Ascii 10 so that line breaks do not cause problems in the resulting file
strOut = strOut & Replace(snpData.Fields(i).Value, vbCrLf, vbLf) & vbTab
'ActiveSheet.Cells(lngRow, i + 1).Value = snpData.Fields(i).Value
Else
strOut = strOut & vbTab
End If
Next i
'Handle the LONG RAW column
strTempBits = Replace(StrConv(snpData("bits"), vbUnicode), vbCrLf, vbLf)
strOut = strOut & strTempBits & vbTab
'ActiveSheet.Cells(lngRow, snpData.Fields.Count).Value = strTempBits
Print #intFileNum, strOut
snpData.MoveNext
Loop
snpData.Close
'Close the data file
Close #intFileNum
'Read the text file just written to disk into the worksheet
With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\LongRawToVarchar.txt", Destination:=ActiveSheet.Range("A1"))
.Name = "Page1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True 'Tabs are the delimiter
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.Refresh BackgroundQuery:=False
End With
Application.ScreenUpdating = True
dbDatabase.Close
Set snpData = Nothing
Set dbDatabase = Nothing
End Sub
If we did not want to write the data out to a text file, we could just comment out the section titled as “‘Read the text file just written to disk into the worksheet“, comment out the Print# lines, and uncomment the lines that begin with “‘ActiveSheet.Cells(“.
For example, if we do not want to write the results to a temp file, our macro would look like this:
Sub DisplayData2()
Dim i As Integer
Dim lngRow As Long
Dim strUsername As String
Dim strPassword As String
Dim strDatabase As String
Dim strSQL As String
Dim strOut As String
Dim intLength As Integer
Dim strTempBits As String
Dim snpData As Object 'ADO Recordset object used to retrieve the user's data
Dim dbDatabase As Object 'ADO database connection object
On Error Resume Next
Set snpData = CreateObject("ADODB.Recordset")
Set dbDatabase = CreateObject("ADODB.Connection")
strDatabase = "MyDB"
strUsername = "MyUser"
strPassword = "MyPassword"
'Connect to the database
'Oracle connection string
dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";ChunkSize=1000;FetchSize=100;"
dbDatabase.ConnectionTimeout = 40
dbDatabase.CursorLocation = adUseClient
dbDatabase.Open
'Retrieve a recordset with 0 rows that will allow us to insert into the table
strSQL = "SELECT"
strSQL = strSQL & " WORKORDER_TYPE," & vbCrLf
strSQL = strSQL & " WORKORDER_BASE_ID," & vbCrLf
strSQL = strSQL & " WORKORDER_LOT_ID," & vbCrLf
strSQL = strSQL & " WORKORDER_SPLIT_ID," & vbCrLf
strSQL = strSQL & " SEQUENCE_NO," & vbCrLf
strSQL = strSQL & " TYPE," & vbCrLf
strSQL = strSQL & " BITS_LENGTH," & vbCrLf
strSQL = strSQL & " BITS" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & " T5" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & " TYPE='D'"
snpData.Open strSQL, dbDatabase
Application.ScreenUpdating = False
lngRow = 1
strOut = ""
For i = 0 To snpData.Fields.Count - 1
ActiveSheet.Cells(lngRow, i + 1).Value = snpData.Fields(i).Name
Next i
Do While Not snpData.EOF
lngRow = lngRow + 1
strOut = ""
For i = 0 To snpData.Fields.Count - 2 'All Except the last column
If Not (IsNull(snpData.Fields(i).Value)) Then
'Switch out Ascii 13 & Ascii 10 combinations for just Ascii 10 so that line breaks do not cause problems in the resulting file
strOut = strOut & Replace(snpData.Fields(i).Value, vbCrLf, vbLf) & vbTab
ActiveSheet.Cells(lngRow, i + 1).Value = snpData.Fields(i).Value
Else
strOut = strOut & vbTab
End If
Next i
'Handle the LONG RAW column
strTempBits = Replace(StrConv(snpData("bits"), vbUnicode), vbCrLf, vbLf)
strOut = strOut & strTempBits & vbTab
ActiveSheet.Cells(lngRow, snpData.Fields.Count).Value = strTempBits
snpData.MoveNext
Loop
snpData.Close
Application.ScreenUpdating = True
dbDatabase.Close
Set snpData = Nothing
Set dbDatabase = Nothing
End Sub
The resulting worksheet would then need a bit more clean up than it did when we wrote out the data to a text file and brought the text file into the worksheet with a QueryTable:
So, now you know how to retrieve text contained in a LONG RAW column (our T5 table) or text contained in a BLOB column (our T5_TEMP table, as stored by the recent releases of the ERP package) and display the text in an Excel worksheet with other columns retrieved by a query.
I fully recognize that LONG RAW columns are deprecated, but is anyone able to identify a more direct way to transform a LONG RAW column value into a VARCHAR2 using just a SQL statement with built-in Oracle Database functions, without requiring an intermediate temporary table?
Recent Comments