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

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:

HTML Code: 

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.

PHP 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:

PHP Code: 

    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)

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

Originally Posted by rorya

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 here are the 2 csv files.

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

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.

Please Login or Register  to view this content.

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?

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...

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.

Are you sure?

I had no problem.

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

I just show you the result from the MS Query.

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

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.

Please Login or Register  to view this content.

Yep, got same error...

What about my last proposal? csv to xlsx?

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?

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

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

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

Please Login or Register  to view this content.

Can not think any other...

Hi @Jindon,

I am getting the following error:

Attachment 650660

at the SQL query line.

Can not open your attachment.

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.

Then change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.