Import from csv (into different columns) via Openrowset and Microsoft.ACE.OLEDB.12.0

Question

I want to clarify how I could import data from .csv into table with 3 columns (see CR Ranking.csv below). My query:

 select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=C:\Work\;HDR=Yes;', 
 'SELECT * FROM [CR Ranking.csv]');

Outputs results into one coulmn:

header: Category;INfo;Rank
row 1: Category 1;Info;1
row 2: Category 2;INfo2;2
row 3: Category 3;INfo3;3

IS it possible via Openrowset to split data from .csv into 3 columns? I think that I missed something from Openrowset params, perhaps it's very easy?

And YES: i'm using 64bit ODBC drivers from Office 2010 to get drivers for .csv, .txt, etc... That's why provider is: 'Microsoft.ACE.OLEDB.12.0', and that's why this conection string doesn't seem to work:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\';Extended Properties="text; HDR=NO; FMT=Delimited";

CR Ranking.csv:

Category;INfo;Rank
Category 1;Info;1
Category 2;INfo2;2
Category 3;Info3;3

UPD 1: Is it possible to do without format file?

UPD 2: I made via Format file - it was easy. Sorry for disturbance.

Answer 1

I know this is an old post but thought I'd respond anyway.

I've not tried this using ACE but I've found several posts that say something like the following will work. Notice the custom Delimited(;) setting...

select top 50 * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=c:\temp\captell;HDR=Yes;FORMAT=Delimited(;)', 'SELECT * FROM [DASDGIGS0013046591395.TXT]')