Reading csv file data using selecte statement

indian143

Hi all, I am trying to get all the columns and rows of a csv file using a select statement, when I am trying as in the below query, its retrieving all the rows and columns as one column and one row.

SELECT * FROM OPENROWSET(
BULK 'D:\Users\Abdul\Provider.csv',
SINGLE_CLOB) AS DATA;

I have tried in the following way

select *
into #T
from openrowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=D:\Users\Abdul;Extensions=csv;',
'select * from \Provider.csv') Test;

select *
from #T;

Its giving me the following error:

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Line 11
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

Can any body please help me, am I missing anything in the above query, any help would be very much grateful, thanks in advance. Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."

Victor Nijegorodov

Did you try what was suggested in: [sql server - SQL Bulk import from CSV - Stack Overflow](https://stackoverflow.com/questions/96448/sql-bulk-import-from-csv) [Using OPENROWSET to import CSV files](https://www.sqlservercentral.com/Forums/814177/Using-OPENROWSET-to-import-CSV-files?PageIndex=1)

indian143

hi, I don't have permissions to run the open rowset and it needs the format file creation, I tried creating the format file, but some reason its not letting me do it. I tried the same using the SSIS package, problem is its giving me error as below:

Error: 0xC0202055 at Data Flow Task 1, Source - ProviderDetails_csv [61]: The column delimiter for column "Column 18" was not found.
Error: 0xC0202092 at Data Flow Task 1, Source - ProviderDetails_csv [61]: An error occurred while processing file "C:\xxxxxxx\OPS\ProviderODS-ScriptsAndData\ProviderDetails.csv" on data row 542006.
Error: 0xC0047038 at Data Flow Task 1, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source - ProviderDetails_csv returned error code 0xC0202092. The component returned a failure code when the

But when I trying to import using

if not exists (select * from sysobjects where name='Import_21Columns' and xtype='U')
CREATE TABLE Import_21Columns(
[Column 0] [varchar](max) NULL,
[Column 1] [varchar](max) NULL,
[Column 2] [varchar](max) NULL,
[Column 3] [varchar](max) NULL,
[Column 4] [varchar](max) NULL,
[Column 5] [varchar](max) NULL,
[Column 6] [varchar](max) NULL,
[Column 7] [varchar](max) NULL,
[Column 8] [varchar](max) NULL,
[Column 9] [varchar](max) NULL,
[Column 10] [varchar](max) NULL,
[Column 11] [varchar](max) NULL,
[Column 12] [varchar](max) NULL,
[Column 13] [varchar](max) NULL,
[Column 14] [varchar](max) NULL,
[Column 15] [varchar](max) NULL,
[Column 16] [varchar](max) NULL,
[Column 17] [varchar](max) NULL,
[Column 18] [varchar](max) NULL,
[Column 19] [varchar](max) NULL,
[Column 20] [varchar](max) NULL
)

if not exists (select * from sysobjects where name='Import_18Columns' and xtype='U')
CREATE TABLE [Import_18Columns](
[Column 0] [varchar](max) NULL,
[Column 1] [varchar](max) NULL,
[Column 2] [varchar](max) NULL,
[Column 3] [varchar](max) NULL,
[Column 4] [varchar](max) NULL,
[Column 5] [varchar](max) NULL,
[Column 6] [varchar](max) NULL,
[Column 7] [varchar](max) NULL,
[Column 8] [varchar](max) NULL,
[Column 9] [varchar](max) NULL,
[Column 10] [varchar](max) NULL,
[Column 11] [varchar](max) NULL,
[Column 12] [varchar](max) NULL,
[Column 13] [varchar](max) NULL,
[Column 14] [varchar](max) NULL,
[Column 15] [varchar](max) NULL,
[Column 16] [varchar](max) NULL,
[Column 17] [varchar](max) NULL,
[Column 18] [varchar](max) NULL
)

if not exists (select * from sysobjects