excel ace.oledb vba connection to csv returning only first column

Question

In VBA Excel (Office 365) trhough using a connection string like "Provider=Microsoft.ACE.OLEDB.12.0;data source=..." and using the command string SELECT * FROM [text;HDR=Yes;FMT=Delimited;Delimiter=';';database=KKS].[kks.csv]; the results are: when the CSV source looks like this:

rng;key;component;
"0";"A";"Grid and distribution systems";
"0";"B";"Power transmission and auxiliary power supply";

The schema.ini is:

[kks.csv]
ColNameHeader=True
Format=CSVDelimited
Decimalsymbol=(,)
Delimiter=(;)

I have tried any combination for the connection string and for schema.ini (also removing it completely) and the results are the same at best. Any suggestion? Thank you!

Answer 1

change the Schema.ini as below.

[kks.csv]
Format = Delimited(;)