SQL 2012 :: OPENROWSET To Read In Data From Excel 2010 File (XLSX)

Jun 12, 2015

I have a test server (TEST1) running SQL 2012 and Windows 2012R2. One of the developers wants to use OPENROWSET to read in data from an Excel 2010 file (an xlsx file).

I have loaded the Microsoft Drivers in "AccessDatabaseEngine_64.exe" and enabled the Ad Hoc Distributed Queries option in SQL.

This is the sample code we are working with:

SELECT
X.MEMBID
FROM OPENROWSET(
'MSDASQL',
'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=etworkserverFolder1Folder2MEMBIDs.xlsx',
'SELECT * FROM [Sheet1$]'
) AS X

I can run the sample query from my laptop with SSMS (I have admin rights) and I can also run it as SA from my laptop. So all is good, right?

But if I RDC into TEST1, I cannot run the query. I get this error:

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver] Your network access was interrupted. To continue, close the database, and then open it again.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x574 Thread 0xb74 DBC 0x1d07f08 Excel'.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x574 Thread 0xb74 DBC 0x1d07f08 Excel'.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".But wait! It gets better.

I can run the query as SA from TEST1.

And of course, the developer can't run it either.

And it works fine in the production server.

I'm thinking the basics are there but something isn't right in some permission somewhere.

View 9 Replies


ADVERTISEMENT

SQL 2012 :: SSIS And Excel - Import Some Data From XLSX File

Mar 7, 2014

I am using VS2012 and creating a package on a 64bit machine to import some data from a .xlsx file. My question is that I am getting an error for the Excel connection manager, do I need to install some kind of excel drive or excel itself on the machine in order to be able to import the data?

View 6 Replies View Related

DB Engine :: Server 2012 Import Data From Excel 2010

Oct 11, 2015

I used sql server 2012 express Import and Export Data (32-bit) wizard to import data from excel 2010 to a given table. But I got the following error message: Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Unspecified error". (SQL Server Import and Export Wizard)
 
Error 0xc020901c: Data Flow Task 1: There was an error with Destination - MPRecord.Inputs[Destination Input].Columns[Top1] on Destination - MPRecord.Inputs[Destination Input]. The column status returned was: "The value violated the integrity constraints
for the column.". (SQL Server Import and Export Wizard)
 
Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "Destination - MPRecord.Inputs[Destination Input]" failed because error code 0xC020907D occurred, and the error row disposition on "Destination - MPRecord.Inputs[Destination Input]" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard)
 
Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Destination - MPRecord" (35) failed with error code 0xC0209029 while processing input "Destination Input" (48). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

View 2 Replies View Related

Read XLSX File From ASP

Feb 15, 2008

Hello, I am trying to read a file "xlsx" from ASP, the problem is when I try to delete the file, ASP inform not have permits to delete it, it is as if the connection to file not closing properly, the code is:




Code Snippet




if extension = "xlsx" or extension = "xls" then 'si el archivo es excel

sFilePath = server.MapPath("../tmp/importar/"&archivo)
sDataDir = server.MapPath("../tmp/importar")
sSheetName = "Hoja1"

if extension = "xls" then
sFileConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq="&sFilePath&";DefaultDir="&sDataDir&";"
else
sFileConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};READONLY=TRUE;DBQ="&sFilePath&";DefaultDir="&sDataDir&";"
end if
sFileSQL = "SELECT * FROM [" & sSheetName & "$]"

set oFileCN = server.createobject("ADODB.Connection") 'creo objeto adodb
oFileCN.Open sFileConnectionString 'abro el exel
set oFileRS = oFileCN.Execute(sFileSQL) 'selecciono los registros
if not oFileRS.EOF then 'si es distinto de EOF obtengo un array con los valores de las columnas
aSourceData = oFileRS.getRows()
end if
oFileRS.Close
set oFileRS = nothing
oFileCN.Close
set oFileCN = nothing

'asigno los mails a la variabls "readDatafile"
readDatafile = aSourceData






and the error





Microsoft VBScript runtime error '800a0046'

Permission denied

/gestion/edit_import_db_mail.asp, line 27




Any ideas?, Greetings and forgiveness for my English

View 9 Replies View Related

SQL 2012 :: SSAS Actions Not Appearing In Excel 2010?

Nov 28, 2012

I have an SSAS cube that i want to add some actions to. I've had problems adding a reporting action to the cube so decided just to add a URL action instead. Start simple and build on the concepts...

So i add a new action, give it a name, set the Target Type to Cells, Taget object to All Cells. I've put no condition on the action since i want it to appear all the time.

The action content type is set to URL the action expression is set to [URL] I've also set a caption of "Google" under the additional properties and said that the caption is MDX (I'm aware that it isn't but i do intend to expand on this...).

I then build and deploy my cube, call up excel (2010) and then create a pivot table off the back of the cube but when i right-click the cells in the pivot table and go to "additional actions" it tells me that there are none specified.

[URL]

View 6 Replies View Related

SQL 2012 :: Import Excel XLSX Files Into Temp Table

Feb 18, 2014

I am having with trying to import XLSX files into SQL 2012 64 Bit.

I have installed the Access driver (AccessDatabaseEngine_x64.exe)

I have configured the script to run the following SP

sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1

[Code] ....

So I first create my Temp Table

The run the SP above then I run the insert into the Temp table defined

INSERT INTO tempdb.dbo.TempTRBZ (IsNew,CoID, Zip, City, County,StateCode,Rate,Taxable,TaxShip,TaxLab,CountryID,StateID)

SELECT * FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0','EXCEL 12.0;Database=C:TempNotInTrbzJan.xlsx;HDR=YES','SELECT * FROM [Data$]')

[Code] ....

The error message I get back is

Msg 7303, Level 16, State 1, Line 4
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

What I have set wrong on the import? Using SSIS at this point is not a real option.

View 0 Replies View Related

SQL Server 2012 :: Read Dynamic Columns From Excel File Into SSIS

Nov 11, 2014

I have an excel file which has dynamic columns

i.e. Col1, Col2, Col3 this week. next week i will have a new Col4 in the sheet. This will keep on adding every week.

My problem is to Unpivot the data

Date 8/2/2013 8/9/2013 8/16/2013

Stock 1,561 1,661 1,761

i.e. the abobe table should become as

Date Stock

8/2/2013 1561
8/9/2013 1661
8/16/2013 1,761

How can I unpivot the dynamic columns given that the columns will keep on increasing every week.

View 1 Replies View Related

SQL 2012 :: Excel Spreadsheet Data Connection To Read-only Database

Aug 10, 2015

I've created an excel spreadsheet with a data connection. This data connection uses a query that runs against a read-only database.

The issue I'm having is that the query never seems to finish running against the database, whether I open the Excel spreadsheet to view the data or run the query in SSMS.

I created the connection on the Data ribbon by going to From Other Sources --> From SQL Server and using the Data Connection Wizard.

Is there some kind of setting or property I'm missing that would allow this query to finish running?

View 6 Replies View Related

Usinf OPENROWSET To Read From A .ttx File

Mar 5, 2008


I have a .ttx file similar to this:

"xxxxxxxxx" "dc8" "184:30" "168:00" "00:00" "00:00" "00:00" "00:00" "00:00" "00:00" "00:00" "00:00" "00:00" "00:00" "00:00" 0 0 0 1

I want to read from this file from T-SQL, I saw in a previous post that its possible to do it using OPENROWSET (http://msdn2.microsoft.com/en-us/library/ms190312.aspx)

I don€™t have a clue what parameters i must use on the OPENROWSET T-sql command (provider name to ttx ?)

I would appreciate if anyone could give an example of how to do this

Thanks !

View 7 Replies View Related

Analysis :: Excel 2010 Not Refreshing Data From Cube?

Aug 21, 2015

So I use Excel 2010 connect to a cube I have built.  Then I change some values in the cube via my ETL and re-process the cube.  Then I verify that record is NOT there in the fact table - check!

However, when I refresh the worksheet where the pivot table is pulling data from the cube, but that old record wont go away!

Just realized my cube data source on the dev server, was in fact still configured with my local workstation name. Once I updated that, processed the cube all was well.

View 2 Replies View Related

How To Read CSV File In SQL Server 2005 Using OpenRowSet Function

May 4, 2007

Hi

i want to access a CSV file using OpenRowSet function in SQL Server 2005.



Anyone having any idea; would be of great help.

Regards,

Salman Shehbaz.

View 8 Replies View Related

Integration Services :: SSIS 2008 R2 Package Is Pushing Data Down In Excel 2010 64 Bit

Jul 2, 2015

I have package which pulls data from db table and creates a excel file extract.The flow is like this - A excel file template sits in the input folder folder for processing .The package starts by dropping excel sheet in the excel(which is clearing any data and columns available) once that is done it has script task which creates a new columns for the sheet and gives a sheet name as well .Then a execute sql task runs and pumps data into a table which  serves as a source for the excel extract process .The excel extract process involves pulling data from the table and doing data conversion before it moves it into the oledb destination (excel file on file server).When I run the package I go and see that data is pushed down . I see top rows say 100 are empty and data appears after say 100 rows .

I tried deleting excel file and replacing with new one empty with columns and sheet name only but still it doesnt work?I am trying to understand what is making ssis behave like this and what can I do overcome the problem ?I read on google that we need to bring in file system task will move a template to working directory which is input folder but I dont want it to incorporate that logic as we need to push this package to production ASAP with very minimal change.

View 3 Replies View Related

Certain Numeric Fields Not Read From The Excel File When Using A Excel File Source.

Jul 20, 2006

I have the Excel Connection Manager and Source to read the contents from an Excel file. For some reason couple of numeric fields from the Excel worksheet are brought over as nulls even though they have a value of 300 and 150. I am not sure why this is happening. I looked into the format of the fields and they are set to General in Excel, I tried setting them to numeric and that did not help.

All the other content from the excel file is coming thru except for the 2 numeric fields.

I tried to bring the contents from the excel source to a text file in csv format and for some reason the 2 numeric fields came out as blank.

Any inputs on getting this addressed will be much appreciated.

Thanks,

Manisha

View 5 Replies View Related

How To Query An Excel File By Using Openrowset

Mar 1, 2007

Hi everyone,

I'm trying to query an excel file and I get a mistake. The query is as follows:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:ExcelFile.xls', 'select * from Sheet1')

and I get the following error message:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Book1'. Make sure the object exists and that you spell its name and the path name correctly.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].

I'm thanking any help that you can give.

Thanks,

Oscar.

View 7 Replies View Related

Can Not Access Excel File Using OpenRowset

Aug 8, 2007



I am using SQLServer 2005 SP2. I enabled the Ad Hoc Distributed Queries and DisallowAdhocAccess registry option is explicitly set to 0. Query is working fine when I remote desk to the server and execute when I run same query from my workstation I am getting following error


Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".


Any help is appreciated.
Thanks
--
Farhan

View 8 Replies View Related

OPENROWSET ( CAN WE CREATE FILE WITHOUT EXCEL TEMPLATE??)

Mar 20, 2008

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D: esting.xls;', 'SELECT * FROM [SheetName$]') select * from pubs.dbo.authors
 
I am using similiar query to above to create a excel file, however for this to work, I need to create a template file which has the same columns as the authors table. Is there a way to NOT to define template columns , as I some times will not know which columns will be available... as teh query is dynamic....

View 4 Replies View Related

Import Excel-File (OPENROWSET) On X64 SQL Server

Jan 7, 2008

Hi, recently I encountered the following problem:
I tried to execute a stored procedure on the newly installed SQL 2005 Server (now on x64 Win Server 2003) which imports an Excel-File into a DB table.
We use OPENROWSET to access the Excel data. But I recognized this is dependent on Jet OLE DB which seems is not available for x64 windows.

Is there another way to import excel data using a stored procedure.

thank you in advance, rene

View 12 Replies View Related

Integration Services :: SSIS - Read Multiple Excel Sheets From One Excel File

Sep 13, 2015

We have 10 sheets in Excel File and 10 sheet contains errror data. How to load 9 sheets data in to 1 destination and error data in to other destination?

View 4 Replies View Related

Any Idea Why OpenRowSet To Open Excel File Doesn't Work Well In SQL 2005?

Mar 3, 2007

Maybe it worked once, but in most time it doesn't work, query like below

select top 10 *
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\ws8webablefilessitefiles4000010
eibcactive.xls',
'select * from [crap2$]')


I got error

OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".


but the same query can run without any problem on a SQL 2000 server run on a server in the same network.

Any idea?

View 12 Replies View Related

Delete Data In Excel Using Openrowset?

Feb 23, 2008

Hello,
I would like to delete all data from an excel sheet except the headers row.
Is there any way to do this using Openrowset with JET or something like that?
Thanks
Philippe

View 26 Replies View Related

SQL Server 2012 :: Loading Image From File System Into Query Using Openrowset

May 14, 2014

I have a directory with images and a table in my DB with the path of each file. The main application allow me to create reports where I can display an image, so I was thinking to use a query like:

SELECT [ID]
,[PT_CODE]
,[FILE_PATH],
CASE WHEN [FILE_PATH] IS NOT NULL THEN
(SELECT * FROM OPENROWSET(BULK [FILE_PATH], SINGLE_BLOB) TT)
END
AS IMAGE_LOADED
FROM [DB].[dbo].[TABLE_MR_FILES]But I keep getting the error:

Incorrect syntax near 'FILE_PATH'.I have try multiple combinations without luck to make the OPENROWSET read the path stored in the column [FILE_PATH]. What am I missing?

Note: I am using MSSQL 2012. I don't want to import the images into the DB just load them in the fly as needed by the report runned from the application. I have full access to the DB so if a store procedure is the solution I can go with it.

View 4 Replies View Related

Using .xlsx As Excel Source In SSIS

Oct 17, 2007



Hi,

I am using Excel datasource and Excel destination in a simple SSIS package which i created. I have got two queries regarding this


How does excel datasouce/destination accept .xlsx files, since i observed that they accept only .xsl file

Does excel destination ascept .xlsm files, because i have some macros enabled in destination
Thanks in advance for advice

Warm Regards,
gchanduu.

View 3 Replies View Related

OpenRowSet And OpenDataSource Fail On Production To Open Excel Data

Jun 26, 2006

The following statement fails when using SQL Analyzer under sa but works on all of our development and staging server. All are SQL Server 2005 SP1. We upgraded production over the weekend from SQL Server 2000, creating a new instance machinenameSQL2005.

SELECT *

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

'Excel 8.0;Database=d:data est.xls',

'SELECT * FROM [Sheet1$]')

The error we are getting only in prodcution is:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Any help seriously appreciated,



Tronn

View 44 Replies View Related

Is There A Way To Read A Excel File

Apr 23, 2007

into the sql query analyzer?
Thanks.

Jeff

View 7 Replies View Related

SQL 2012 :: How To Do Selective Read Of File Stored In File Table

Jul 2, 2015

I have a filetable that contains a binary file. I need to do a selective read of the file stored in the file table. I can write a C# CLR function that will open the file, read n bytes the from a starting byte. Or I can write a SQL statement that reads the stream in the filetable into a VARBINARY variable using SUBSTRING beginning at the starting byte (offset from 1) for the same n bytes.

Both give me the same result. However, the SQL statement takes considerably longer to read. I know there is overhead in reading through SQL (interpreted language), but the difference in performance is substantial, and I can only attribute this performance degradation if SQL first tries to "load" the entire stream before it identifies the portion of the stream that it needs to read beginning at the starting byte offset.

I wonder if this is the case or if there is another option to read a stream from a filetable directly through SQL queries that is more efficient.

View 3 Replies View Related

Excel 2010 - Import Multiple Files To Table?

Sep 23, 2014

I have over 600+ Excel .xlsx file that I have been trying to import to Sql database table. I've been trying to complete this task with SSIS but no luck yet. I have seen several videos and read articles but when I run the package the source is validated but I always get an error in the destination. I am using Excel 2010 and SQL Server 2012.

View 3 Replies View Related

Power Pivot :: Downloading Table For Excel 2010

Nov 18, 2015

have just implement Microsoft Dynamics NAV. and I'm trying to learn how to use this Excellent tool powerpivot add on, But when I'm trying to connect it to any SQL table that support Dynamics NAV I have an error message while downloading the table :"do not have enough memory or I should use Excel 64 bits instead 32 bits".when I check CPU memory Utilization we are just using less tha 20%. I'm working on a remote server and the using Excel 2010.

View 3 Replies View Related

Power Pivot :: Insert Hierarchy Slicer In Excel 2010?

Sep 23, 2015

We want to implement cascading slicers in PowerPivot 2010. Does Excel 2010 have 'Insert Slicer Hierarchy' feature in PowerPivot?

View 5 Replies View Related

File System Watcher Can Not Read Excel From SharePoint

May 18, 2007

I used file system watcher to read excel on my pc it worked fine, but when I tried to read the excel from SharePoint it did't work. The FileWatcher box showing the yellow color for long time that I had to stop the ssis.

So my question what is the cause of this. Do i need to set something or am i missing something? Please help.

View 7 Replies View Related

Integration Services :: How To Make SSIS Read Excel File

Dec 7, 2011

I’ve been trying for a while now (won’t say how long), to get BIDS to read a very simple Excel file. 

I’m talking SIMPLE!!  No matter what I don, SSIS keeps throwing an error, and of course it doesn’t say what the error is, so I can’t really debug it. 

I’ve tried this at least 20x with flat files, and it works perfect each time. I’ve done Data Conversions, Sorting, Union All, and several other Transformations; all work perfect. 

Trying to used Excel as a data source, is proving to be a mind numbing experience. Of over 20 different attempts, it hasn’t worked even once. I can make it as simple as you can possibly imagine, and SSIS, refuses to even perform the first step (I’m not even trying to create a table in SQL Server anymore). 

I have the Excel file path (very simple)

I have the Excel version (very simple)

I have the Connection Manager (very simple)

I have the Sheet name (very simple)
 
All I can do is see a preview of the Excel sheet before the process runs.  As soon as I het F5, I get an instant error, for no reason whatsoever, and no debugging options,whatsoever.

View 13 Replies View Related

Importing Xls/xlsx File Into SQL

Jul 7, 2007

Hi all,

Actually, I am working in a leading advertisement company where we were handling all of our data in Access, but because our database is getting really large, we have to shift to SQL because queries were taking long time to execute. I have no idea what SQL is. However, i have a problem. In access, i had to import an xlsx(excel 2007 file) in my database. In access I used to do this:

Sub SendTableToExcel()
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, "Campaigns", _
"C:frequentstuffsCampaigns.xlsx", True
End Sub

The format(ie the col Names and structure) in table Campaign and Excel File, is same. I just need to copy the data from excel into SQL. How can i do the same thing in SQL? To be very precise, I am novice to SQL so if explained in a novice languauge, i would really appreciate it!!

Vikas B

Vikas Bhandari
Visit My Blog
www.excelnoob.blogspot.com

View 3 Replies View Related

XLSX File Upload

Oct 23, 2007

Hi,



I uploaded an xlsx file to my reporting services. When i click on the xlsx file link in the reporting services frontend, a internet explorer window is opened with the xlsx file information (reporting services) and the xlsx file is opened by the excel application.



Is there any way to prevent the Internet explorer window to be opened?


Regards.

View 4 Replies View Related

Pipeline Error-excel Source-data Reader Does Not Read In Meta Data

Apr 16, 2008

Hi all, i got this error:


[DTS.Pipeline] Error: "component "Excel Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

and also this:

[Excel Source [1]] Warning: The external metadata column collection is out of synchronization with the data source columns. The column "Fiscal Week" needs to be updated in the external metadata column collection. The column "Fiscal Year" needs to be updated in the external metadata column collection. The column "1st level" needs to be added to the external metadata column collection. The column "2nd level" needs to be added to the external metadata column collection. The column "3rd level" needs to be added to the external metadata column collection. The "external metadata column "1st Level" (16745)" needs to be removed from the external metadata column collection. The "external metadata column "3rd Level" (16609)" needs to be removed from the external metadata column collection. The "external metadata column "2nd Level" (16272)" needs to be removed from the external metadata column collection.


I tried going data flow->excel connection->advanced editor for excel source-> input and output properties and tried to refresh the columns affected.
It seems that somehow the 3 columns are not read in from the source file?
ans alslo fiscal year, fiscal week is not set up up properly in my data destination?
anyone faced such errors before?

Thanks

View 13 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved