Excel VBA - "[Microsoft][ODBC Text Driver] Record is too Large." - Joining 2 csv files.

[junoon]

I am joining 2 csv files using a `Transform` (Pivoting) query with `Microsoft Access Text Driver (ADODB)` in Excel 2016 using VBA. Both csv files are small i.e. 1 is not more than 10 rows and the other may be not more than 20 rows. The `Transform` query works fine with most csv files. I am only picking up 1 or 2 columns from both the joining files - even though the columns are just 10-15 columns.

However, recently with one of the joining files having 60 rows, i started getting an error:

Observations:

• I saw that if i tried reducing the number of rows in one of the files from 60 rows to 30 rows, it works!
• If i run the same query in `MSAccess`, it works fine even with one of the files having 60 rows.
• I tried replicating scenario by choosing the `Microsoft Access Text Driver` and importing the 2 csv files in `MSQuery`, then running the `Transform query`, but got the same `Record is too Large` error.
• I tried changing the ODBC driver connection string to `SQL ACE Engine`, but still the same.
• Initially i thought it could be that the filepath length may be > 255 characters, but that is not the case. It is within the range.
• Initially i thought it could be spaces in the filepath, but i have found a solution of enclosing filepath in `back-ticks`, which handles filepaths with spaces, if using the `Microsoft Access Text Driver` to join files.
• Someone suggested to convert the `TextValue` field IN `File2.csv` containing long text (not greater than 80-100 characters) as a `MEMO field`. I am not sure how to convert it in a `SELECT TRANSFORM Query`.

Here is a snapshot of the SQL Query & VBA code.

TRANSFORM FIRST(A.[textValue]) AS TextValue  SELECT clng(G.[position]) AS [G-Value], 1 AS Theme,COUNT(clng(A.[position])) AS Var, G.[label] AS [Attribute Name] FROM `C:\ABC\DEF GHI\JKL MNO PQR\TEST 1\SUBTEST 1\2019\DOIT 1\1-2`\File1.csv AS G,`C:\ABC\DEF GHI\JKL MNO PQR\TEST 2\SUBTEST 2\2019\DOIT 2\1-2`\File2.csv AS A WHERE G.[geneid] = A.[geneid] GROUP BY clng(G.[position]),G.[label] ORDER BY clng(A.[position]) Asc PIVOT clng(A.[position]) 

The connection string i am using is:

    Set oCon = CreateObject("ADODB.Connection")
    strCon = "Driver=Microsoft Access Text Driver (*.txt, *.csv);DriverId=27;Dbq=" & sFullDirectory & ";Extensions=asc,csv,tab,txt;FIL=text"
    
    With oCon
        .cursorlocation = adUseClient
        .connectionstring = strCon
        .Open
    End With

    Set oRs = CreateObject("ADODB.Recordset")
    With oRs
        .cursortype = adOpenStatic
        .locktype = adLockBatchOptimistic
        .activeconnection = oCon
        .Open (strSQL)    '----> Error: Record is too Large.
    End With 

Questions:


How do i resolve this issue without revamping much of my code (I am using other `Select-Join` queries that depend on the Microsoft Access Text Driver)?

Is there a way to make the ADO query engine recognize the `TextValue` column as a `Memo field` in the `TRANSFORM-SELECT` Query itself? I don't want to create a temporary table using (MAKE or INSERT INTO) to change the data type.
e.g. TRANSFORM CMemo( FIRST(A.[textValue]) ) AS TextValue??

Images of the CSV files:

File1.csv (1 row only)

File2.csv (60 rows)

[Pepe Le Mokko]

Administrative note
Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile does not indicate your version.
Thanks

[Pepe Le Mokko]

Administrative Note:

Hi and welcome
although we value privacy as much as anyone else, it could be important that members have a rough idea of your location. You might in the future post questions which are bound to your regional settings.
So, please update your profile ( country will suffice, no need to be more precise).
Thank you for helping us to help you

[junoon]

Apologies @Admins. I didn't realize that due to the urgency of my post. Will do it right now. Thanks for the reminder.

[romperstomper]

Posting pictures of your csv files really doesn't help anyone to help you.

[junoon]

Posting pictures of your csv files really doesn't help anyone to help you.

I am sorry, but due to certain restrictions, i cannot post a sample csv file for now, so had to share pictures of the sample csv. If you think my post is not upto your mark or understandable to general public, i shall delete it to avoid any inconvenience to anyone.

[CK76]

You really can't avoid it using Text Driver. It's hard limitation on the driver if I recall.

Since you have Office 2016, you should have access to Get & Transform. Use that instead of VBA code.
It uses Microsoft.Mashup.OleDb and should be able to handle your issue.

[junoon]

Hi @CK76,

Thanks for your reply.

The issue is, if i replicate this scenario by importing the 2 files in `MSAccess` and run the same query, it seems to easily generate the crosstab. Why is it able to do so, as i am itself using a `Microsoft Access Text Driver`? Is there some `Extended Properties` for the Driver that i am missing, that may do the job?

BTW, these are small csv files. The maximum rows in the File2.csv is 60 rows and that too, i am only pulling only 2 columns from it viz., `geneid` and `TextValue` columns. So don't know why it shouldn't work.

If you mean Get & Transform i.e. Power query, i am aware of it. But cannot use Power Query transformations due to other reasons.

Can you please share an example on how to use the `Microsoft.Mashup.OleDb` Driver to run the SQL query? I have other sheets that have similar queries that also use the `Microsoft Access Text Driver` with other csv files. So if `Microsoft.Mashup.OleDb` works, then i will use it for generating other sheets data also.

Thanks.

[Pepe Le Mokko]

Administrative Note:

Sorry for bothering again

Please don't quote entire posts unnecessarily. They clutter threads and make them hard to read.
Use the "Quick reply" instead
Thanks

[CK76]

Why is it able to do so, as i am itself using a `Microsoft Access Text Driver`?

Can't remember the exact limit, but text field has character limit. So if one file has longer text in a field, that can cause issues. As well, there is limit to number of char that can be used in given row (record). I believe that was 2000 char or 4000 bytes.

If you can't use Get & Transform then... you can't use `Microsoft.Mashup.OleDb`.

You'll need to use some other method to transform (ex. Read text stream, or using Freefile() load the result into array and transform via code etc).

As Rory mentioned, it's bit hard to help you without sample file (raw data & output).

[junoon]

@CK76 here are the 2 csv files.

[jindon]

One way.
Add a Text Driver.
1) go to Windows Settings and enter ODBC in search box on the top.
2) hit [Add] and select [Microsoft Text Driver] then [Finish]
3) Enter the Driver name, e.g. TextDriver and the explanation in both boxes. and [OK]

Now you can see TextDriver* when you go to [Data] - [OtherDataSource] - [MicrosoftQuery]

HTH

[junoon]

Hi @Jindon,

I could not find ODBC in Window 10 Enterprise (64-bit) settings, but had to do a Search. Found one for 32-bit and 64-bit:

Attachment 650587
Attachment 650588

The 32 bit one has a `Microsoft Text Driver` that i added manually.

Now you can see TextDriver* when you go to [Data] - [OtherDataSource] - [MicrosoftQuery]

I did not understand your above suggestion. What is the next step that i need to take to implement this in my VBA code to run the Query?

[jindon]

I'm not with my pc now.

That was for 32 bit version.
If 64, select Ms access text driver instead.

When you get ms query, you should be able to add csv files as table.
So that you can edit the query...

[junoon]

Hi @Jindon,

I had already done that with MS Query, but got the same "Record is too large." error (though you can see the attached csv's contain very small data). See my `Observations` section in my post.

[jindon]

Are you sure?

I had no problem.

https://www.stata.com/support/faqs/d...ring-odbc-win/

[jindon]

I just show you the result from the MS Query.

[jindon]

Other way is to save csv files as xlsx and read them from there.

[junoon]

Hi Jindon,

I am getting trouble with the TRANSFORM Query - not with the SELECT Query. Please try the Query i had shared in my OP.

TRANSFORM FIRST(A.[textValue]) AS TextValue  SELECT clng(G.[position]) AS [G-Value], 1 AS Theme,COUNT(clng(A.[position])) AS Var, G.[label] AS [Attribute Name] FROM `C:\ABC\DEF GHI\JKL MNO PQR\TEST 1\SUBTEST 1\2019\DOIT 1\1-2`\File1.csv AS G,`C:\ABC\DEF GHI\JKL MNO PQR\TEST 2\SUBTEST 2\2019\DOIT 2\1-2`\File2.csv AS A WHERE G.[geneid] = A.[geneid] GROUP BY clng(G.[position]),G.[label] ORDER BY clng(A.[position]) Asc PIVOT clng(A.[position])

[jindon]

Yep, got same error...

What about my last proposal? csv to xlsx?

[junoon]

Cannot change csv to xlsx as csv's are located on network drive and they are used with ADO connections to generate other sheets. Is there some Extended Properties for the connection that we can change? OR can we change the datatype of the TextValue column to a Memo or LongText field?

Strangely this transform query works well in MSAccess. Why? What is happening in the background that can help determine a similar solution using the Access Text Driver?

[jindon]

You can create schema.ini file in the same folder where csv are located to define field details etc...

[junoon]

I did that for `TextValue` column but did not help. Was getting some incorrect format error.

[File2.csv]
Format=CSVDelimited
ColNameHeader=True
MaxScanRows=0
Col4=TextValue Text Width 255 'or Col4=TextValue LongChar
CharacterSet=ANSI

[jindon]

This is to import csv files in a workbook temporary and connect and delete at the end... seems working...
If you want to test, you need to select the csv files in order File1 then File2

Sub test()
    Dim fn, i As Long, wb As Workbook, temp
    fn = Application.GetOpenFilename("CSVFiles,*.csv", , , , True)
    If Not IsArray(fn) Then Exit Sub
    If UBound(fn) <> 2 Then Exit Sub
    GetWorkbook fn
    Dim cn As Object, rs As Object
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    With cn
        .Provider = "Microsoft.Ace.OLEDB.12.0"
        .Properties("Extended Properties") = "Excel 12.0; HDR=Yes;"
        .Open ThisWorkbook.Path & "\temp.xlsx"
    End With
    rs.Open "TRANSFORM FIRST(A.[textValue]) AS TextValue  SELECT clng(G.[position]) AS [G-Value], 1 AS Theme," & _
        "COUNT(clng(A.[position])) AS Var, G.[label] AS [Attribute Name] FROM `" & fn(1) & "$` AS G, `" & _
        fn(2) & "$` AS A WHERE G.[geneid] = A.[geneid] " & _
        "GROUP BY clng(G.[position]),G.[label] ORDER BY clng(A.[position]) Asc PIVOT clng(A.[position]);", cn, 3
    For i = 0 To rs.Fields.Count - 1
        Cells(1, i + 1) = rs.Fields(i).Name
    Next
    [a2].CopyFromRecordset rs
    Set cn = Nothing: Set rs = Nothing
    Kill ThisWorkbook.Path & "\temp.xlsx"
End Sub

Private Sub GetWorkbook(fn)
    Dim i As Long, temp, wb As Workbook
    temp = Application.SheetsInNewWorkbook
    Application.SheetsInNewWorkbook = UBound(fn)
    Set wb = Workbooks.Add
    Application.SheetsInNewWorkbook = temp
    For i = 1 To UBound(fn)
        With wb.Sheets(i).QueryTables.Add(Connection:= _
            "TEXT;" & fn(i), Destination:=wb.Sheets(i).Range("$A$1"))
            .FieldNames = True
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .AdjustColumnWidth = True
            .TextFileParseType = xlDelimited
            .TextFileCommaDelimiter = True
            .Refresh BackgroundQuery:=False
        End With
        fn(i) = wb.Sheets(i).Name
    Next
    wb.SaveAs ThisWorkbook.Path & "\temp.xlsx", 51
    wb.Close
End Sub

Can not think any other...

[CK76]

Since, you can't use PQ... I'd recommend going with jindon's approach of saving csv as temp.xlsx file.

[junoon]

Hi @Jindon,

I am getting the following error:

Attachment 650660

at the SQL query line.

[jindon]

Can not open your attachment.

[junoon]

Ok, i see Application.GetOpenFilename reverses the sequence in which the files were selected. That is why i was getting the above error. Better would be a loop to select 1 file at a time using FileDialog.

[jindon]

Then change

    Dim fn, i As Long, wb As Workbook, temp
    fn = Application.GetOpenFilename("CSVFiles,*.csv", , , , True)
    If Not IsArray(fn) Then Exit Sub
    If UBound(fn) <> 2 Then Exit Sub

to

    Dim fn(1 To 2), i As Long
    For i = 1 To 2
        fn(i) = Application.GetOpenFilename("CSVFiles,*.csv", , "Select " & i & IIf(i = 1, " st ", " nd ") & "csv file")
        If fn(i) = False Then Exit Sub
    Next
    GetWorkbook fn