Copying Data into Excel file - 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

General SQL Server Forums

Script Library

Copying Data into Excel file

Author

Topic

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2010-12-31 : 04:58:44

Hi all,
Hope you'll be having a good time and happy New year

I am facing another issue related to the OpenRowSet function, with following code

set @db_file='Excel 8.0;Database='+ltrim(rtrim(@ReportFilePath))+';'
go
Insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''' + @db_file + ''',
''SElect * FROM [ITSMetaInfo$]''
)
SELECT * FROM TestDB.dbo.MasterSheetMeta

This throws the following error message:

Msg 8152, Level 16, State 4, Line 2
String or binary data would be truncated.
The statement has been terminated.

Which seems that the columns width in excel page is some how smaller to the data length in the Table's column. In order to check the Length of the columns created by the OpenRowSet i have run the following code

Select * into testdb.dbo.temptab from openrowSet .....

And examined the columns in the TempTab, where all columns were of the following datatype and length: nvarchar(510)

Also, i have tried the BCP utility option for copying data into excel file using following Code:

DEclare @ReportFilePath varchar(500),@query varchar(1000),@generatingExcelFile varchar(1000)
set @ReportFilePath='E:\New Folder\MetaInfoTemplate.xls'--+ltrim(rtrim(convert(varchar(10),GETDATE(),112)))+'.xls'
SET @query = 'SELECT * FROM TestDB.dbo.MasterSheetMeta'
SET @generatingExcelFile='bcp "'+@query+'" queryout "' +@ReportFilePath+'" -T -c'
--print @generatingExcelFile
exec master..xp_cmdshell @generatingExcelFile

However this returns the following error:

SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.
SQLState = 01000, NativeError = 2
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).
NULL

The file and Instance is on the same Machine, and i am connected via Windows Authentication. Furthermore the database compatibility version is 80 which is restored on the SQL server 2008 (named instance).

Please advise how to overcome this issue ?

Thanks!
MIK

mikgri
Starting Member

39 Posts

Posted - 2011-01-04 : 13:26:25

try this:

execute ('Insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
'''+ @db_file +''',
''SElect * FROM [ITSMetaInfo$]''
)
SELECT * FROM TestDB.dbo.MasterSheetMeta')

Make sure that @ReportFilePath exist and there is spread sheet named ITSMetaInfo with same column names as in TestDB.dbo.MasterSheetMeta table

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-05 : 00:05:35

Thanks mikgri, Thanks for your response. yes the syntax is ok and is working .. the error message shows that the copying of data is started however due to excessive data size of a column the operation has been stopped. As I have mentioned;

[SNIP]
Which seems that the columns width in excel page is some how smaller to the data length in the Table's column. In order to check the Length of the columns created by the OpenRowSet i have run the following code
Select * into testdb.dbo.temptab from openrowSet .....
And examined the columns in the TempTab, where all columns were of the following datatype and length: nvarchar(510)
[SNIP]

Thanks!

mikgri
Starting Member

39 Posts

Posted - 2011-01-05 : 10:15:51

It looks like that everything more then 255 characters will be truncated.
Look into sp_makewebtask extended stored procedure, maybe it will help you.
Thanks.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-20 : 01:42:29

Hi there

many thanks for your responses!

I have been able to resolved with the help of BCP statement. However is there any way i can append data to an existing file. Currently, the BCP is creating the file onto the specified location and import data into it. But what if i want to append the data into the existing file located on the specified location? Any help/idea.

Cheers!
MIK

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-01-20 : 01:48:44

you can always use DOS COMMAND to append the 2 files

KH
[spoiler]Time is always against us[/spoiler]

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-20 : 04:57:57

thanks khtan for the idea... i have tried it but i am getting a file which is not opening ..

For test purposes i have created two Excel Files with few testing rows .. One contain the Header info e.g. ColumnName1,ColumnName2 and the Second one contains the Data e.g.
MyName1,123456

1) D:\ExcelTests\Test1.xlsx
2) D:\ExcelTests\Test2.xlsx

I used the follwoing command to merge (in order words to append the data of test2.xlsx into the test1.xlsx)

Copy test1.xlsx + test2.xlsx Test.xlsx

Yes this created a new file but that file (Test.xlsx) is not opening, so that i can see as if the rows are correctly appended! Can you help what could be the issue?

Cheers!
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources