Solved

Import CSV into Sql Server with OPENROWSET

Posted on 2010-08-16
1
1,061 Views
Last Modified: 2012-05-10
I'm trying to import a simple .csv into Sql Server 8 with OPENROWSET:

Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\IMPORTFILES\test.csv', 'SELECT * FROM [Sheet1$]')

I get the following error:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005:  The provider did not give any information about the error.].

I have verified the .csv file is valid and can be opened in Excel.  I've done this is the past without errors, can't see what's wrong this time.
0
Comment
Question by:kevbob650
1 Comment
 
LVL 7

Accepted Solution

by:
macentrap earned 500 total points
ID: 33450580
You are using .csv wont you be using Microsoft Text Driver??


SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=C:\IMPORTFILES\;Extensions=CSV;','SELECT * FROM test.csv')

With Excel I have used please try to add IMEX=1

I have used this in SQL 2008
UPDATE SL1
SET
SL1.QTY = T1.QTY                  

FROM  

(select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;DATABASE=D:\qty.xls;IMEX=1','Select * from [Results$]'))AS T1,

Stock SL1
WHERE
T1.Code COLLATE DATABASE_DEFAULT  = SL1.Code COLLATE DATABASE_DEFAULT
and T1.Loc  = SL1.Loc
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql query 7 37
Mssql SQL query 14 28
sql calculate averages 18 32
DATETIMEOFFSET feature 1 5
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now