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.