5

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.

zmische
  • 809
  • 3
  • 13
  • 23
  • Been trying to convert a simple Jet csv connection string to ACE drivers. This is the first working example of accessing a csv file from 64bit ACE I have seen after browsing about 75 various web pages. Thank you! Appearently my major flaw was using double quotes around the Database attribute (does not work if quoted) – DarrenMB Aug 02 '12 at 14:23
  • Create a schema.ini file with the column definitions. Here is a link http://msdn.microsoft.com/en-us/library/ms974559.aspx –  May 13 '13 at 23:11

1 Answers1

3

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]')
Jeff Moden
  • 3,271
  • 2
  • 27
  • 23
  • Confirmed that this works Excel 2010 x64 and SQL Server Express 2008 R2. SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Text;HDR=YES;FMT=Delimited;Database=C:\Data\;"', 'SELECT * FROM [table.csv]') – fomoz Mar 02 '12 at 21:27
  • `FORMAT=Delimited(;)` does not work to read from a semi-colon delimited file, although this will read from a comma-delimited file – Rachel Nov 26 '12 at 18:13
  • Goodnight I've been wrestling with this for a while! My problem was solved by the "[" brackets in the [filename.txt] - I was getting many of the OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server type of errors. +1, thank you! – Chris Townsend Dec 05 '14 at 21:58