read a 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

General SQL Server Forums

New to SQL Server Programming

read a csv file

Author

Topic

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-23 : 13:37:50

1)i have to read a csv file into sql. just return it as a result set. dont have toinsert into any table.
2) basically my intention is to use a select * into <table> kind of statement so that i dont have to create the table upfront. I have around 200 csv dont which can change in every months feed. dont want to create the table upfront.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-23 : 13:43:30

use openrowset function

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-23 : 14:03:21

select * FROM openrowset
(BULK'C:\Users\name\Desktop\PartPrice_GENERAL_MOTORS.csv', Single_BLob) AS csv
iget something like
0x224F452050415254204E554D424552222C224F452050415254205052494345222C2....

what am i doing wrong?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-23 : 14:14:53

dont use openrowset bulk. just use like

SELECT * FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir={your path}','SELECT * FROM ypurtextfile.csv')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-23 : 14:16:21

Turned on sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

now i get
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 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-23 : 14:23:16

[code]SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Text;Database={path here}','SELECT * FROM yourtextfile.csv')[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-23 : 14:26:15

Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-23 : 14:28:35

i have a 64bit os

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-24 : 00:26:33

quote:
Originally posted by AAAV

Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

Ok..Seems like distributed queries are disabled in your db. check it

EXEC sp_configure 'ad hoc distributed queries'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-24 : 07:42:00

Ad Hoc Distributed Queries 0 1 1 1
is there...

i created a table and loaded the csv .. with this
BULK
INSERT GM
FROM 'C:\Users\vin\Desktop\PartPrice_GENERAL_MOTORS.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
now my problem is 1. i want it to ignore the first row which it the column names and 2. I am getting the row as
"00457043" $58.72 "GENERAL MOTORS" 0
i want to get rid of the " marks in the 1st and 3rd column.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-24 : 11:25:55

use a format file then

http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-24 : 12:23:52

--EXEC master.dbo.sp_configure 'show advanced options', 1

--RECONFIGURE

--EXEC master.dbo.sp_configure 'xp_cmdshell', 1

--RECONFIGURE

CREATE TABLE ##GM(
[OE PART NUMBER] [varchar](max) NULL,
[OE PART PRICE] [varchar](max) NULL,
[MSRP Manufacturer] [varchar](max) NULL,
[Core Flag] [varchar](max) NULL
) ON [PRIMARY]

master..xp_cmdshell 'bcp ##GM in C:\PartPrice_GENERAL_MOTORS.csv -f C:\format.txt -T'
bulk insert ##GM from 'C:\PartPrice_GENERAL_MOTORS.csv' with (formatfile = 'C:\format.txt')

Error i get
(8 row(s) affected)
Msg 4832, Level 16, State 1, Line 2
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

with rows output as
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connecti
ons.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
NULL

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-24 : 12:25:15

my data
"OE PART NUMBER","OE PART PRICE","MSRP Manufacturer","Core Flag"
"00457043",$58.72,"GENERAL MOTORS",0
my format file
8.0
4
1 SQLCHAR 0 0 "\"" 0 "OE PART NUMBER" Latin1_General_CI_AS
2 SQLCHAR 0 0 "\"," 1 "OE PART PRICE" Latin1_General_CI_AS
3 SQLCHAR 0 0 ",\"" 2 "MSRP Manufacturer" Latin1_General_CI_AS
4 SQLCHAR 0 0 "\"," 3 "Core Flag" Latin1_General_CI_AS
5 SQLCHAR 0 0 "\r\n" 4 u Latin1_General_CI_AS

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-26 : 13:43:19

any help?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-29 : 02:29:16

Make sure the files exist in Server's directory

Madhivanan

Failing to plan is Planning to fail

amer_md_jamal
Starting Member

1 Post

Posted - 2011-10-24 : 08:40:41

Try this

BULK
INSERT GM
FROM 'C:\Users\vin\Desktop\PartPrice_GENERAL_MOTORS.csv'
WITH
(
FIRSTROW=2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

Regards,
Amer M. J.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources