OPENROWSET - 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 2008 Forums

Transact-SQL (2008)

OPENROWSET

Author

Topic

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-10-25 : 14:51:52

Hi i want to insert a CSV file into a table on the database

SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=\\fileserver1\Repository\SNF\FY 2011\HIPPS\;Extensions=CSV;','SELECT * FROM HIPPSFY2011.csv')

the above openrowset works fine so i tried the following.

USE BRSDATA
GO
INSERT INTO HIPPS
SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=\\fileserver1\Repository\SNF\FY 2011\HIPPS\;Extensions=CSV;','SELECT * FROM HIPPSFY2011.csv')

but i t gives me an error

Msg 295, Level 16, State 3, Line 1
Syntax error converting character string to smalldatetime data type.

this is the structure of the table

CREATE TABLE [dbo].[HIPPS](
[HIPPS] [char](5) NOT NULL,
[HIPPS_FROM] [smalldatetime] NOT NULL,
[HIPPS_THRU] [smalldatetime] NULL,
[HIPPS_PROV] [char](3) NULL,
[HIPPS_DESCRIPTION] [varchar](800) NULL,
CONSTRAINT [PK_HIPPS_] PRIMARY KEY CLUSTERED
(
[HIPPS] ASC,
[HIPPS_FROM] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

the HIPPS_FROM and HIPPS_THRU column in CSV are of date type

ANy help
thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-25 : 15:16:28

Bad data?:

SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=\\fileserver1\Repository\SNF\FY 2011\HIPPS\;Extensions=CSV;','SELECT * FROM HIPPSFY2011.csv')
WHERE IsDate(HIPPS_FROM) = 0 OR IsDate(HIPPS_THRU) = 0

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-10-25 : 17:10:03

Thanks Tara. I have Null values for HIPPS_THRU. So would SQL Server not let me insert the data from the CSV file?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-25 : 23:19:01

Is it just a string that says "null"? I'm not sure that you can use a date/time data type for that. You'd probably need varchar.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-10-26 : 10:14:03

in the CSV file, the HIPPS_THRU column is shortdate type and when the cell does not have a date it should import as a NULL into the table. I filled the empty cells with a date and it seems to import now but if it is a empty cell, i think thats where it gives me an error..
database - sql server 2000
client (my desktop) - sql server 2008
.CSV - Office 2010

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources