开发者

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

开发者 https://www.devze.com 2023-01-15 08:48 出处:网络
I want to clarify how I could import data from .csv into table with 3 columns (see CR Ranking.csv below). My query:

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;I开发者_JAVA技巧nfo;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.


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]')
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号