SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Copying Data into Excel file
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 12/31/2010 :  04:58:44  Show Profile  Reply with Quote
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 - 01/04/2011 :  13:26:25  Show Profile  Reply with Quote
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
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 01/05/2011 :  00:05:35  Show Profile  Reply with Quote
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!
Go to Top of Page

mikgri
Starting Member

39 Posts

Posted - 01/05/2011 :  10:15:51  Show Profile  Reply with Quote
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.
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 01/20/2011 :  01:42:29  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 01/20/2011 :  01:48:44  Show Profile  Reply with Quote
you can always use DOS COMMAND to append the 2 files


KH
Time is always against us

Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 01/20/2011 :  04:57:57  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000