Unable to write data on to the Excel
-
Hi All, I am trying to write a SQL Query data on to Excel directly, but it is giving me the following error. Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". My code is below
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 0
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml; HDR=YES; IMEX=1;Database=C:\Test.xlsx;',
'SELECT [ApplicationName], Date FROM [Sheet1$]')
SELECT [ApplicationName], GETDATE() FROM Application
GO
GOWhen initially it gave me above error I copied the excel file to the Server itself and ran the linked server script as below
exec sp_addlinkedserver @server = N'webdevsql1',
@srvproduct=N'Excel', @provider=N'Microsoft.ACE.OLEDB.12.0',
@datasrc=N'C:\Users\AleemA\Desktop\Dont Remove ThisFolder\LocationsExcel\Test.xlsx;',
@provstr=N'EXCEL 12.0;Hdr=Yes' ;Then ran the script again, now it is giving me little bit different error (ie. Cannot execute the query "SELECT [ApplicationName], Date FROM [Sheet1$]" against OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"). Can anybody please help me in this regards? any help a code snippet, a link or even a suggestion helps me. I am also searching and trying from online, but if you have already familiar may be your experience helps me. Thanks in advance.
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
-
Hi All, I am trying to write a SQL Query data on to Excel directly, but it is giving me the following error. Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". My code is below
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 0
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml; HDR=YES; IMEX=1;Database=C:\Test.xlsx;',
'SELECT [ApplicationName], Date FROM [Sheet1$]')
SELECT [ApplicationName], GETDATE() FROM Application
GO
GOWhen initially it gave me above error I copied the excel file to the Server itself and ran the linked server script as below
exec sp_addlinkedserver @server = N'webdevsql1',
@srvproduct=N'Excel', @provider=N'Microsoft.ACE.OLEDB.12.0',
@datasrc=N'C:\Users\AleemA\Desktop\Dont Remove ThisFolder\LocationsExcel\Test.xlsx;',
@provstr=N'EXCEL 12.0;Hdr=Yes' ;Then ran the script again, now it is giving me little bit different error (ie. Cannot execute the query "SELECT [ApplicationName], Date FROM [Sheet1$]" against OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"). Can anybody please help me in this regards? any help a code snippet, a link or even a suggestion helps me. I am also searching and trying from online, but if you have already familiar may be your experience helps me. Thanks in advance.
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
Hi guys some how I could able to find a way to write selected query values into an xls file but not on the xlsx file yet. If there is a way out for it from somebody can help me, if not for now I will live with it. But my bigger issue is the below script is writing on to Excel file without headers, can somebody please help me in writing on the excel file along with headers? Here is my script
DECLARE @cmd VARCHAR(255)='',
@sqlQuery varchar(max) = '"Select top 5 * From WEB_WebAdmin.dbo.Provider"',
@FilePath varchar(max)='"\\dcqwdbs057\Users\AleemA\Desktop\Dont Remove ThisFolder\LocationsExcel\Test.xls"'
SET @cmd = 'bcp '+ @sqlQuery + ' queryout ' + @FilePath + ' -T -c'
Exec xp_cmdshell @cmdAny help a code snippet, a link or even a suggestion would be greatly helpful, thanks in advance my friends.
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
-
Hi guys some how I could able to find a way to write selected query values into an xls file but not on the xlsx file yet. If there is a way out for it from somebody can help me, if not for now I will live with it. But my bigger issue is the below script is writing on to Excel file without headers, can somebody please help me in writing on the excel file along with headers? Here is my script
DECLARE @cmd VARCHAR(255)='',
@sqlQuery varchar(max) = '"Select top 5 * From WEB_WebAdmin.dbo.Provider"',
@FilePath varchar(max)='"\\dcqwdbs057\Users\AleemA\Desktop\Dont Remove ThisFolder\LocationsExcel\Test.xls"'
SET @cmd = 'bcp '+ @sqlQuery + ' queryout ' + @FilePath + ' -T -c'
Exec xp_cmdshell @cmdAny help a code snippet, a link or even a suggestion would be greatly helpful, thanks in advance my friends.
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
BCP does not support headers, I used to insert the header string with a linefeed into position 0 after the BCP operation. This also indicates a workaround[^]. All of which is a kludge!
Never underestimate the power of human stupidity RAH
-
BCP does not support headers, I used to insert the header string with a linefeed into position 0 after the BCP operation. This also indicates a workaround[^]. All of which is a kludge!
Never underestimate the power of human stupidity RAH
OK I got an Idea I will create a template file with headers then can I append the contents of the headers file + the output file of the bcp into a final file so that I can have both the headers and content. Or you can give me how to append row or data in the beginning of the file. Can you please give me some suggestion or link or code snippet to append header file + content file into a final file using bcp or command shell etc? Any help is appreciated, thanks in advance I am also searching for it.
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
-
BCP does not support headers, I used to insert the header string with a linefeed into position 0 after the BCP operation. This also indicates a workaround[^]. All of which is a kludge!
Never underestimate the power of human stupidity RAH
Thanks buddy, finally at last I could able to do it man !... hah. Here is my code to do it may be can be helpful to somebody else, first I wrote a query to just print Headers and then query to print content then I appended those two files with cmd shell into one final file
DECLARE @cmd VARCHAR(8000)='',
@sqlContentQuery varchar(max) = '"Select top 5 * From WEB_WebAdmin.dbo.Provider"',
@sqlTemplQuery varchar(max) = '"Select ''WA ProviderId'' WebAdmProviderId, ''WA Last Name'' LastName, ''WA First Name'' FirstName, ''WA Date of Birth'' Dob, '- '''PS ProviderId'' WebAdmProviderId, ''PS Last Name'' LastName, ''PS First Name'' FirstName, ''PS Date of Birth'' Dob"',
@ContentFilePath varchar(max)='"\\dcqwdbs057\Users\AleemA\Desktop\Dont Remove ThisFolder\ProviderSearchDataMigration\Content.xls"',
@TemplFilePath varchar(max)='"\\dcqwdbs057\Users\AleemA\Desktop\Dont Remove ThisFolder\ProviderSearchDataMigration\TemplateFile.xls"',
@FinalFilePath varchar(max)='"\\dcqwdbs057\Users\AleemA\Desktop\Dont Remove ThisFolder\ProviderSearchDataMigration\Final.xls"'
SET @cmd = 'bcp '+ @sqlTemplQuery + ' queryout ' + @TemplFilePath + ' -c -C -T'
Exec xp_cmdshell @cmdSET @cmd = 'bcp '+ @sqlContentQuery + ' queryout ' + @ContentFilePath + ' -c -C -T'
Exec xp_cmdshell @cmdSET @cmd = 'copy ' + @TemplFilePath +' + '+ @ContentFilePath +' ' + @FinalFilePath + ''
exec master..xp_cmdshell @cmdThanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
- '''PS ProviderId'' WebAdmProviderId, ''PS Last Name'' LastName, ''PS First Name'' FirstName, ''PS Date of Birth'' Dob"',
-
Thanks buddy, finally at last I could able to do it man !... hah. Here is my code to do it may be can be helpful to somebody else, first I wrote a query to just print Headers and then query to print content then I appended those two files with cmd shell into one final file
DECLARE @cmd VARCHAR(8000)='',
@sqlContentQuery varchar(max) = '"Select top 5 * From WEB_WebAdmin.dbo.Provider"',
@sqlTemplQuery varchar(max) = '"Select ''WA ProviderId'' WebAdmProviderId, ''WA Last Name'' LastName, ''WA First Name'' FirstName, ''WA Date of Birth'' Dob, '- '''PS ProviderId'' WebAdmProviderId, ''PS Last Name'' LastName, ''PS First Name'' FirstName, ''PS Date of Birth'' Dob"',
@ContentFilePath varchar(max)='"\\dcqwdbs057\Users\AleemA\Desktop\Dont Remove ThisFolder\ProviderSearchDataMigration\Content.xls"',
@TemplFilePath varchar(max)='"\\dcqwdbs057\Users\AleemA\Desktop\Dont Remove ThisFolder\ProviderSearchDataMigration\TemplateFile.xls"',
@FinalFilePath varchar(max)='"\\dcqwdbs057\Users\AleemA\Desktop\Dont Remove ThisFolder\ProviderSearchDataMigration\Final.xls"'
SET @cmd = 'bcp '+ @sqlTemplQuery + ' queryout ' + @TemplFilePath + ' -c -C -T'
Exec xp_cmdshell @cmdSET @cmd = 'bcp '+ @sqlContentQuery + ' queryout ' + @ContentFilePath + ' -c -C -T'
Exec xp_cmdshell @cmdSET @cmd = 'copy ' + @TemplFilePath +' + '+ @ContentFilePath +' ' + @FinalFilePath + ''
exec master..xp_cmdshell @cmdThanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
But one small limitation here, bcp is creating files only in xls format (97-2003) but any body know how to do the same thing to get document in xlsx format like (2007) format? For now I can live with it but just want to acquire more knowledge on it. Any help is appreciated, thanks in advance friends :)
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
- '''PS ProviderId'' WebAdmProviderId, ''PS Last Name'' LastName, ''PS First Name'' FirstName, ''PS Date of Birth'' Dob"',