Sleeper: Overcoming ADO Recordset 64 Characters limitation for Fields

[sifar786]

I have some aphanumeric data in an Excel Sheet of a Closed Workbook named `temp.xlsx`. I am creating an `ADODB Recordset` by an SQL Query to pull this range into my current Workbook. The problem i am facing is that some of the column headers in the Sheet are more than 64 characters in length. This results in the ADO field names getting truncated to 64 characters once i import the range into an ADO recordset.

    Public Const adCmdText = 1
    Public Const adOpenDynamic = 2
    Public Const adUseServer = 2
    Public Const adOpenStatic = 3
    Public Const adUseClient = 3
    Public Const adLockBatchOptimistic = 4
    Public Const adTypeBinary = 1
    Public Const adTypeText = 2


'    Dim oStream As Object
'    Dim oCmd As Object

    Dim oCon As Object
    Dim oRS As Object
    Dim strSQL As String    
    Dim strFilePath As String    

'    Set oStream = CreateObject("ADODB.Stream")
'    Set oCmd = CreateObject("ADODB.Command")    
   
'    Set xlXML = CreateObject("MSXML2.DOMDocument")
'    Set XL = GetObject(, "Excel.Application")
'    Set WB = XL.ActiveWorkbook
'    xlXML.LoadXML Replace(WB.Sheets("Sheet1").Cells(1, 1).CurrentRegion.value(xlRangeValueMSPersistXML), "rs:name="" ", "rs:name=""") 'xlRangeValueXMLSpreadsheet
'    oRS.Open xlXML, CursorType:=adOpenStatic, LockType:=adLockBatchOptimistic

    Set oCon = CreateObject("ADODB.Connection")    
    Set oRS = CreateObject("ADODB.Recordset")    

   strFilePath = ThisWorkbook.Path & Application.PathSeparator & "temp1.xlsx"        
   With oCon        
       .Provider = "Microsoft.Ace.OLEDB.12.0"        
       .Properties("Extended Properties") = "Excel 12.0; HDR=Yes;IMEX=1;"        
       .CursorLocation = adUseClient        
       .Open strFilePath    
   End With        
   
   strSQL = "SELECT * FROM [Sheet1$]"    
   With oRS        
       .CursorType = adOpenStatic        
       .CursorLocation = adUseClient        
       .LockType = adLockBatchOptimistic        
       .activeconnection = adoCon        
       .Open (strSQL)    
   End With
End Sub

Is there an alternative method to ensure that the full column name characters gets imported into the ADO Recordset from a CLOSED Excel Workbook i.e. overcoming the 64 character limitation of Fields?

Can an ADO.STREAM object do the job maintaining the column names OR Can an MSXML2.DOMDocument.Load File or .LoadXML do the trick?

Any help would be most appreciated.

[snb]

Why do you need more then 64 characters to identify a column/field ?

Use the simpler code in: https://www.snb-vba.eu/VBA_ADODB_recordset_en.html

[sifar786]

@snb the columns are like this in the closed workbook :

NonEMI_Segment_5_UPDATED_Non_Frequent_Legal_Carolisan_Mastroska_Users | NonEMI_Segment_5_UPDATED_Non_Frequent_Legal_Carolisan_Mastroska_Users-Testing
123 | ACDF
98 | BCD
76 | FGI

After getting the data into an ADO Recordset, I have to find the Field names containing "-Testing" by looping and checking with "INSTR" and remove these columns as these are Stat Testing columns that i dont wish to import. Just note the above Column names are different everytime and their length also varies everytime. The only way to remove Stat testing columns is by the word "-Testing" in them. So right now, the ADO recordset truncates the column name to only 1st 64 characters. So no way to identify "-Testing" present in column or not. Therefore the Stat Testing column also gets picked up and added to the ListObject. I hope you understand.

[snb]

Why don't you use:

Sub M_snb()
  with getobject("G:\OF\export.xlsx")
     .sheets(1).rows(1).replace( "*-*","")
     .sheets(1).rows(1).specialcells(4).entirecolumn.delete
     .close -1
  end with
End sub

to remove/delete the unwanted columns.

Why would you use ADODB ?

[sifar786]

@snb the above code gives :

Compile error : 
Syntax error =

BTW I meant i am removing the Stat Testing columns from ADO recordset (after loading the data from the temp.xlsx workbook sheet) by looping the Recordset.Fields and testing them using INSTR function. Sorry, for the confusion.

[p45cal]

What version of Excel are you using?
You should be able to use Get & Transform data or its equivalent depending on your version.

[sifar786]

I am using Excel 2016 Pro 64 bit on Windows 10 Enterprise 64 bit with 8 GB RAM.

If you mean Power Query, the reason i am not using it is because :

- i don't yet know much about using VBA to code Power Query transformation steps.
- most of the people who are going to use my tool, are on earlier versions of issue viz., Excel 2010/2013 32 bit. So code backward compatibility is an issue w.r.t Power Query.

[snb]

In that case you should refrain from using ADODB as well.

[sifar786]

@snb ADODB works fine on Excel 2010/2013 and i usually check the Application.Version to know whether to load Jet or Ace provider. No issues. But the issue i suddenly faced is when one of the csv files for a particular Client Study happened to exceed 64 character limit of Jet/Ace. So trying to find ways to overcome this issue without affecting much of my other code. I have another issue of importing Images from a closed workbook (posted here) that i need to tackle before i can move on to creating the last report which requires both the csv data and images.

So trying to find a best solution to close this issue quickly!

[snb]

You still didn't answer my question: why using ADODB ?

[sifar786]

@snb i use ADODB as i dont want to open csv files, copy and then paste to my current workbook. ADODB is fast, allows some filtering, sorting and makes it easier to import data from a Closed file. I hope that makes sense.

[SamT]

@snb i use ADODB as i dont want to open csv files, copy and then paste to my current workbook. ADODB is fast, allows some filtering, sorting and makes it easier to import data from a Closed file. I hope that makes sense.

But... AODB doesn't work with your files.

[sifar786]

@SamT where did i say it doesn't work or gives an error? The issue is for certain csv data (which i came across issue recently), having column characters >64 characters, it truncates the column names. I thought the post Title was quite obvious.

[SamT]

This results in the ADO field names getting truncated to 64 characters