0

I'm trying to import the data from CSV file using VBA. Here i'm trying retrieve the data without opening the .csv file

Connection used

con.Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & short_path & ";" & _
    "Extended Properties=""text; HDR=Yes;ReadOnly=true; FMT=Delimited; IMEX=1;ImportMixedTypes=Text;""")

and for that sql used

str_sql = "Select * from [" & str_filename & "]"
rst.Open str_sql, con, adOpenStatic, adLockOptimistic, adCmdText

OLEDB is trying to read the data. So after pasting data from recordset to excel, comments i.e. Long strings are getting truncated. I believe by default it is scanning first 8 rows to determine the datatype.

This is working perfectly when I have the comments/long strings which are more than 255 characters in first 8 rows.

However, the problem comes when more than 255 characters comment/long string is on 9th or '+ rows. so all later comments are truncated to 255.

I'm wandering around the web to find out the solution but got Nowhere.

Note. If I had the small size CSV file then I can do the open/copy/paste/filter/calculation very easily by using VBA to open file and then do the rest.

However, In my scenario the files size is 100+ MB. So i cant open. tried but failed because it goes into non responding mode and crashes later.

so how to achieve this in VBA and Excel.

1S1a4m9
  • 27
  • 7
  • 1
    Try using a schema.ini and use memo data type - see here: https://stackoverflow.com/q/29624757/16578424 and https://learn.microsoft.com/en-us/sql/odbc/microsoft/schema-ini-file-text-file-driver?view=sql-server-ver15 – Ike Nov 08 '21 at 14:11
  • https://stackoverflow.com/questions/52516715/adodb-imported-csv-change-data-type – Nathan_Sav Nov 08 '21 at 14:15
  • Thanks. Sure, I will have a look. Also, will this work on ''.xlsx'' document as well? – 1S1a4m9 Nov 08 '21 at 18:05

0 Answers0