+ Reply to Thread
Results 1 to 29 of 29

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

  1. #1
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 2016 Professional 64 bit
    Posts
    108

    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], AS Theme,COUNT(clng(A.[position])) AS Var, G.[label] AS [Attribute NameFROM `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.[geneidGROUP BY clng(G.[position]),G.[labelORDER 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)

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,681

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

    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

  3. #3
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,681

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

    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

  4. #4
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 2016 Professional 64 bit
    Posts
    108

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

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

  5. #5
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    7,295

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

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

  6. #6
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 2016 Professional 64 bit
    Posts
    108

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

    Quote Originally Posted by rorya View Post
    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.

  7. #7
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,770

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

    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.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  8. #8
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 2016 Professional 64 bit
    Posts
    108

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

    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.
    Last edited by junoon; 11-19-2019 at 12:15 PM.

  9. #9
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,681

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

    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

  10. #10
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,770

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

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

  11. #11
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 2016 Professional 64 bit
    Posts
    108

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

    @CK76 here are the 2 csv files.
    Attached Files Attached Files

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,143

    Re: Excel VBA - "[Microsoft][ODBC Text Driver] Record is too Large." - Joining 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

  13. #13
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 2016 Professional 64 bit
    Posts
    108

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

    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?

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,143

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

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

  15. #15
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 2016 Professional 64 bit
    Posts
    108

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

    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.

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,143

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


  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,143

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

    I just show you the result from the MS Query.
    Attached Files Attached Files

  18. #18
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,143

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

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

  19. #19
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 2016 Professional 64 bit
    Posts
    108

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

    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.

  20. #20
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,143

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

    Yep, got same error...

    What about my last proposal? csv to xlsx?

  21. #21
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 2016 Professional 64 bit
    Posts
    108

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

    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?

  22. #22
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,143

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

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

  23. #23
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 2016 Professional 64 bit
    Posts
    108

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

    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

  24. #24
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,143

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

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

  25. #25
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,770

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

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

  26. #26
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 2016 Professional 64 bit
    Posts
    108

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

    Hi @Jindon,

    I am getting the following error:

    Attachment 650660

    at the SQL query line.

  27. #27
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,143

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

    Can not open your attachment.

  28. #28
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 2016 Professional 64 bit
    Posts
    108

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

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

  29. #29
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,143

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

    Then change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Join 2 csv files in different paths having spaces, with ODBC MICROSOFT ACCESS TEXT DRIVER
    By junoon in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-06-2019, 08:49 AM
  2. [SOLVED] [microsoft][ODBC Driver Manager] Data source name not found and no default driver specifie
    By FoxyladyAccountant in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 06-03-2019, 01:22 PM
  3. [Microsoft][ODBC Excel Driver]Invalid bookmark,Run time error -2147217906 (80040e0e)
    By Kalpesh93 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-25-2015, 05:38 AM
  4. Replies: 5
    Last Post: 08-12-2015, 05:45 PM
  5. Error -2147217900: [Microsoft][ODBC Excel Driver] <tablename$> is NOT a valid name
    By basubdd in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-06-2013, 11:34 AM
  6. Replies: 0
    Last Post: 03-28-2012, 11:24 AM
  7. Microsoft ODBC Excel Driver : Numeric Field Overflow Error
    By jacksonmathews in forum Excel General
    Replies: 0
    Last Post: 07-06-2009, 04:33 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1