Unable to transpose array

[JackDanIce]

Hi,

I have the following code which I'm testing to read data into an array via ADO:

Sub ExistingCode()

Dim filepath as string: filepath = "D:\"
Dim filename as string: filename = "Test.csv"

'ADO test
Dim arr()   As Variant
Dim arr2    As Variant

arr = f_ADO_Out(filepath, filename)
ReDim arr2(LBound(arr, 2) To UBound(arr, 2), LBound(arr, 1) To UBound(arr, 1))
arr2 = Application.Transpose(arr)

End Sub

Line in bold has error "Run-time error '13': Type mismatch"

f_ADO_Out is a public function that reads data into an array

Public Function f_ADO_Out(ByRef strPath As String, ByRef strName As String) As Variant
    
    Dim objConnection       As Object
    Dim objRecordset        As Object
    
    Dim sql                 As String
       
    Const adOpenStatic      As Long = 3
    Const adLockOptimistic  As Long = 3
    
    Const adCmdText         As Long = &H1
       
    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordset = CreateObject("ADODB.Recordset")
        
    'Create connection to source file
    objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strPath & ";" & "Extended Properties=""text;HDR=No;FMT=Delimited;"""
    
    'SQL query to set up a "table of data" to read into Excel
    sql = "Select * FROM [@1]"  'sql = "Select * FROM [" & strName & "] "
    
    objRecordset.Open Replace(sql, "@1", strName), objConnection, adOpenStatic, adLockOptimistic, adCmdText
    f_ADO_Out = objRecordset.GetRows
    
    Set objConnection = Nothing
    Set objRecordset = Nothing
    
End Function

Realised the output from the function is transposed and because this is something I'm bolting into existing code, I prefer to transpose before using the data.

I'm passing valid arguments into the function and do not believe the inputs are the problem. I also tried to transpose within the function but I get the same error; thoughts on how to fix please?

TIA,
Jack

 

[GTO]

I am about to logout, but I still am missing why you are having issues with Transpose. If I am reading correctly, you are reading from a closed csv, right? Could you post some sample data that we could chunk into a .csv and replicate the error?

Mark

PS. What year Excel are you running this in? I believe 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source' is for Excel 2003 and earlier.

 

[JackDanIce]

Good questions Mark,

Using Excel 2010, perhaps the provider part needs updating?

Confirm, I am reading from a closed csv file, sample data includes (excuse the formatting, unable to download screen capture tools at work):

Row 1 unique (actual values)
secid 20160607

Row 2 and onwards (actual values)
ISINT0004821432 100.228
SED436885 100.169

(blue column A contents, black column B contents)
For rows 2 and onwards, the maximum size found in today's file is a string of length 25.

~10% of cells in column A are blank but have adjacent value in column B (142 blanks out of 6554 rows, excl. header)
Column B are positive mixed numbers upto 4 d.p. in precision

 

[xenou]

FMT=Delimited would normally assume a comma separated file, which this is not. Also you have HDR=No but it's actually yes, and what do the blanks actually look like? Is there a delimiter such as tab or space? Is the blank supposed to be a null or an empty string in your data?

 

[JackDanIce]

Did wonder about the HDR argument, I cobbled the ADO code together from various sites and Qs on here so some of the understanding of it is still missing, will change that.

The current code generates an input CSV file (workbooks.add - stuff goes in - workbook.save as <path + name>, FileFormat:=xlCSV) and some script.

I copy and paste script into a separate system, which generates output file in same location as input CSV file.

From previous discussions with tech team here, I'm pretty certain the output file *is* a CSV file of FileFormat xlCSV type, which I think relates to some compatibility with Windows as the script is run in a Unix environment (I think).

The blanks may be null or an empty string, I'm not sure. The cells themselves do not contain data (tested using basic Excel functions of total count of range - count blanks in range = COUNTA of range and reconciled by doing a manual count of blank cells within range).

Haven't had much time this afternoon to work on this further, hopefully can tomorrow and update.

 

[xenou]

K. It may be a file of type csv but if it has no commas in the file than check results as its not really comma-separated at that point no matter what the file extension says (or, technically, it's going to treat each row as a single field because with no commas all the data in each row would be treated as the first and only field). Also watch what happens to your blanks - if imported into Excel after washing through ADO then probably they will be either empty strings or true blank cells.

Note I might be wrong about the FMT=Delimited so take it with a grain of salt. I *think* it defaults to comma-delimited.

 

[JackDanIce]

Hi Mark, understood, file extension vs actual contents.

This is my full ADO function, with comments:

Private Function f_ADO_Out(ByRef fpath As String, ByRef fName As String) As Variant
    
    Dim objConnection   As Object
    Dim objRecordset    As Object
    
    Dim arr()           As Variant
    
    Dim x               As Long
    Dim sql             As String
    Dim s1              As String
        
    Const adOpenStatic As Long = 3
    Const adLockOptimistic As Long = 3
    Const adCmdText = &H1
    
    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordset = CreateObject("ADODB.Recordset")
        
    'Create connection to source file
    'objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & fpath & ";" & "Extended Properties=""text;HDR=No;FMT=Delimited;"""
    'objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data  Source=" & fpath & ";" & "Extended  Properties=""text;HDR=Yes;FMT=Delimited;"""
    objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & fpath & ";" & "Extended Properties=""text;HDR=No;"""
    
    'SQL query to set up a "table of data" to read into Excel
    sql = "Select * FROM [" & fName & "] "
    
    With objRecordset
        .Open sql, objConnection, adOpenStatic, adLockOptimistic, adCmdText
        
        'Neither line (indepedently) errors, however, can't use both due to EOF after first line is executed
        f_ADO_Out = .GetRows
        'arr = .GetRows
                    
    End With
    
    'At this point, locals windows show:
    'Type Variant/Variant(0 to 1, 0 to 4355) or 4356 when HDR=No
    
    'Prints a single row of data (only 1 column of the 2 columns in the CSV file, suspect due to Ubound of 1st dim  = 1
    Sheets("Test").Range("A2").Resize(UBound(f_ADO_Out, 1), UBound(f_ADO_Out, 2)).Value = f_ADO_Out
   
    'Prints 2 rows of data, (transposed), but otherwise matches the data in columns in the csv file where blanks appear blank (cell length of contents = 0, I cannot tell if they are defined as Null or vbnullstring or Empty in the array itself after data is read in
    'Similar, in the source CSV file, the cells appear blank meaning length of contents = 0 but unable to determine if "value" is Null, vbNullString or Empty
    Sheets("Test").Range("A6").Resize(UBound(f_ADO_Out, 1) + 1, UBound(f_ADO_Out, 2)).Value = f_ADO_Out
    
    'This errors with type mismatch
    arr = Application.Transpose(f_ADO_Out)
    
    Set objConnection = Nothing
    Set objRecordset = Nothing

End Function

It's the type mismatch error that only generates when I try to transpose the data that is confusing me the most, especially given it writes to the sheet fine when I do not transpose it. If my variable types are wrong, it's not clear why and what I need to correct.

As much as I was trying to avoid it, I may have to try the earlier suggestion of writing my own transpose function specifically for this.

 

[Trebor76]

Hi JackDanIce,

Must say I feel a little out of league with the calibre of people offering you advice, but I had a similar problem recently transposing a dictionary of items and got around it by using the transpose function twice like so:

wsConsSheet.Range("A7:B" & objMyUniqueData.Count + 6) = WorksheetFunction.Transpose(WorksheetFunction.Transpose(objMyUniqueData.Items)) '6 added to account for headers

Not sure if it will help but I thought it was worth a shot sharing nonetheless.

Regards,

Robert

 

[RoryA]

If you do in fact have Null values in your array, you would get a Type Mismatch error when using Application.Transpose.

 

[JackDanIce]

Thank you Trebor and Rory

@Trebor, I'll try your suggestion shortly and revert back. And thank you for posting a reply, you've experienced a similar problem and found a solution.. surely that's the idea of replying on this board!

@Rory that would explain why it can print the array contents 'as is' without issue (ignoring the Ubound stuff where I need to +1 because it's being returned in base 0).
I'll speak to the tech team about having their Unix script account for blanks too.

 

[JackDanIce]

Brief update:

@Rory the locals window does show the variable arr holding "Null" with type Variant/Null
@Trebor, haven't tried your suggestion yet, but given Rory's comment and the error I'm experiencing, fairly sure this is the cause of it.

Will speak to tech team here to change this.