Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1236
  • Last Modified:

Import CSV into Sql Server with OPENROWSET

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
kevbob650
Asked:
kevbob650
1 Solution
 
macentrapCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now