Problem exporting to Excel

Question

Problem exporting to Excel

raymond donnelly

Mr or Mrs. 500

Points: 580
More actions
May 4, 2005 at 3:58 pm

#185876

I'm having a problem when i try to export a text field from my database to a column in excel. I originally got a buffer error because the amount of data was too large, but i have since fixed that. Now the problem I am having is that the actual column of data will not display more than 255 characters. I've scoured the microsoft knowledge bases, but have not been able to find anything relevant to this specific issue.
This article fixed my overflow error http://support.microsoft.com/default.aspx?scid=kb;en-us;281517, but i can't find anything that tells me why my actual document won't display more than 255 characters in that column.
Any ideas? Any help is greatly appreciate.
Thanks,
Ray

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

David.Poole SSC Guru Points: 75656 More actions · Answer 1

I could be wrong here but I think the export to Excel uses the old JET libraries and that is the reason the TEXT data gets restricted to 255 characters.

LinkedIn Profile

David.Poole SSC Guru Points: 75656 More actions · Answer 2

I forgot to add, I built an Excel export routine that spat out an HTML table but the page header had a ContentType="application/vnd.ms-excel"

This means that although the file is really an HTML file it will be opened in Excel if you try and access it via a browser.

LinkedIn Profile

raymond donnelly Mr or Mrs. 500 Points: 580 More actions · Answer 3

is there no way around those limitations of 255 chars?

philcart SSC-Forever Points: 47794 More actions · Answer 4

Don't know if this suit you needs, but how about trying this?

http://www.sqldts.com/default.aspx?237

If that doesn't fit then there is a workaround here,

http://www.sqldts.com/default.aspx?297

--------------------
Colt 45 - the original point and click interface

raymond donnelly Mr or Mrs. 500 Points: 580 More actions · Answer 5

Thanks, appreciate the input greatly. Unfortunately, the workaround reference is for flat files, not Excel. So the "Max characters per delimited column" isn't relevant. Not sure where to go from here, but thanks for attempting to help. It really is appreciated.

Ray

raymond donnelly Mr or Mrs. 500 Points: 580 More actions · Answer 6

Actually, another question i had, and please excuse my ignorance, but can you do the same thing via the ActiveX task and bypass the Jet drivers altogether? I'm just not familiar enough with ActiveX.

Thanks,

Ray

Jim Collet Old Hand Points: 365 More actions · Answer 7

you can create an excel file and export to that from an ActiveX task, and it should overcome the field length issue you were having.

a few other things you could try would be: create a tab delimited text file (with or without the quotes around the string data) or a csv (with or without the quotes).

here is some code i borrowed from a vb 6 app that creates an excel and exports to it (it should not have a limit to the size of the field exported - we use it to export comments and they are often times very long):

Dim oExcelApp, xlb, xls, sExcelSheetName

Set oExcelApp = CreateObject("Excel.Application")

Set xlb = oExcelApp.Workbooks.Add

Set xls = xlb.Worksheets.Add

sExcelSheetName = "Sheet Name Here"

xls.Name = sExcelSheetName

xls.Activate

'Set xls= oExcelWorkbook.ActiveSheet

'from here, the code i have actually transfers the records from a 'grid to the cells, so it wont help much, but let me see if i can 'rewrite it

Dim rs

Dim conn

Dim sSQL

Dim i, j

'create conn here

Set conn = CreateObject("adodb.connection")

Set rs = CreateObject("adodb.recordset")

conn.connectionstring = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=dbname;Data Source=servername"

conn.open

sSQL = "select * from tablename" 'or "exec spName" & params etc

Set rs = conn.execute(sSQL)

j = 1

MsgBox "start export"

Do While Not rs.EOF

For i = 0 To rs.fields.Count - 1

If j = 1 Then 'column headers

xls.cells(j, i + 1) = rs.fields(i).Name

End If

xls.cells(j + 1, i + 1) = rs.fields(i)

j = j + 1

Loop

'clean up everything here:

rs.Close

Set rs = Nothing

conn.Close

Set conn = Nothing

xls.Visible = -1 'xlSheetVisible

xlb.Application.UserControl = True

xlb.Worksheets(xls.Name).Activate

xlb.Parent.Windows(xlb.Name).Visible = True

xlb.Application.DisplayAlerts = True

xlb.SaveAs "c:/test.xls"

'MsgBox "done"

xlb.Close

oExcelApp.Quit

Set oExcelApp = Nothing

Set xlb = Nothing

Set xls = Nothing

this is bare bones: i would get fancy and make the file name, sheet name and path dynamic as well as check to see if the file already exists, otherwise the pacakge will stop and ask you if you want to overwrite. keep on eye on your processes running in the task manager and make sure excel actually quits, otherwise you might end up with lots of 'invisible' excel processes running which could lead to a crash if it gets to be too many.

Jim Collet Old Hand Points: 365 More actions · Answer 8

i made a small correction to the DO loop:

Do While Not rs.EOF

For i = 0 To rs.fields.Count - 1

If j = 1 Then 'column headers

xls.Cells(j, i + 1) = rs.fields(i).Name

Else

xls.Cells(j, i + 1) = rs.fields(i)

End If

rs.movenext

End If

j = j + 1

Loop

philcart SSC-Forever Points: 47794 More actions · Answer 9

Excel can open tab seperated text files without any trouble.

--------------------
Colt 45 - the original point and click interface