1

I'm trying to use next code to import data from CSV file:

 select * FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Text;Database=C:\Users\Evgeny\Desktop;HDR=Yes;FORMAT=Delimited(;)', 
    'SELECT * FROM [test.csv]'
);

And my data have next structure, where 1st string has column names:

"Name1";"Name2";"Name3"
"Value1";"Value2";"Value3"

But after the query is executed, the result is only one column, like this:

Name1
Value1

What can be a problem? How can I solve it not usung other drivers, becouse on my job place I can't do that.

Evgeny
  • 597
  • 2
  • 7
  • 16

1 Answers1

1

Try using OPENROWSET with BULK

SELECT * FROM OPENROWSET(
BULK 'FILE.csv',
SINGLE_CLOB) AS DATA;

Or use Microsoft Text Driver:

You can also follow my answer at Import error using Openrowset it may helps you

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • thank you. But now the simplest way is to save file as .xlsx=( – Evgeny Jun 06 '17 at 08:40
  • I didn't understand you. Are you asking to import xlsx or csv – Hadi Jun 06 '17 at 17:54
  • if you follow this link https://stackoverflow.com/questions/41883658/import-error-using-openrowset/41945440#41945440 you will find that using oledb provider from importing csv is not best practice. because when importing a column that contain multiple data types it will cause problems – Hadi Jun 06 '17 at 21:27
  • the problem is that with csv it does not work, but when you convert file to .xlsx ACE.OLEDB.12.0 for excel 12.0 it works properly and will be no " characters. – Evgeny Jun 07 '17 at 06:07