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.
The connection string i am using is:
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)
Bookmarks