Exporting data of type TEXT to Excel

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Exporting data of type TEXT to Excel

Exporting data of type TEXT to Excel

Rate Topic

Display Mode

Topic Options

Author

Message

Wayne Atherton-398733

Posted Monday, June 22, 2009 11:15 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, October 30, 2009 1:34 PM
Points: 4, Visits: 32

Hi there,

I have some xml stored in a table col of type TEXT, can anyone assist me getting this correct using the following:

INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\ExtractTables\ExcelFiles\SQL2000\SQL2000Tables.xls',
'SELECT * FROM [ApplicationXMLConfig$]')
SELECT xmlconfig from DatabaseName.dbo.Applications

I keep getting a truncate error message because the values are greater than 8000 chars (i think)

Post #739596

GSquared

Posted Monday, June 22, 2009 12:18 PM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:55 AM
Points: 13,529, Visits: 8,088

I'm pretty sure Excel won't allow you to do that.

Why not export the XML to text files instead of Excel?

- GSquared, RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #739644

Wayne Atherton-398733

Posted Monday, June 22, 2009 12:41 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, October 30, 2009 1:34 PM
Points: 4, Visits: 32

The SQL script is part of a SQL file that contains several exports to excel sheets.

I have them all working fine, but because the xmlconfig col of the applications table contains fully qualified XML I need to find a way of exporting this information as well.

I could create a txt file, but I'd prefer to keep all exporting to excel as the SQL script is part of a batch file run to obtain backups from remote sites.

Post #739657

« Prev Topic | Next Topic »

Permissions