openrowset csv file - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

openrowset csv file

Author

Topic

AA
Starting Member

1 Post

Posted - 2009-02-26 : 15:33:19

I am using openrowset to import from a .csv file:
select *
from OpenRowset('MSDASQL.1', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=\\server\Input\;','select * from file1.csv')

One of the columns has: N/A for the first few rows, followed by some rows with the value: 0 One row has a value like: 0.0023.
The query is returning this value as 0 too?

I thought the text driver imported data as is and didn't try to be smart like the excel driver and figure out the data type.

How can I resolve this.

Thanks in Advance.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-04 : 07:28:30

Worked for me:

Query:
SELECT * FROM
OPENROWSET ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\temp;',
'SELECT * from book1.csv');

Results:
1,2,3,4,5,6,7 ------Header Row
N\A NULL 0 NULL 0.23 NULL
N\A NULL b3 NULL NULL b6 NULL
0.23 4 c 4 NULL c NULL
d 5 d 5 5 d NULL
e 6 e 6 6 e NULL
0.23 7 f 7 7 NULL NULL
g 8 g 8 8 g NULL

subbu_cse
Starting Member

1 Post

Posted - 2011-01-13 : 00:12:31

the same i have tried for sql server 2008 r2. i am unable to get the result and getting the access denied error.

note that my csv file is not in network drive. it is in local system and having full permission.

could you please help me on this

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-14 : 05:18:07

quote:
Originally posted by subbu_cse

the same i have tried for sql server 2008 r2. i am unable to get the result and getting the access denied error.

note that my csv file is not in network drive. it is in local system and having full permission.

could you please help me on this

The file should be in server's directory

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources