Toad World® Forums

Excel format issue with Automation

I’m exporting the results of my query to Excel using the Automation tool. The issue is, the formatting looks very bad. The first column contains a fixed width number and the column in Excel is huge. There are also several other columns that are overly large for their contents. There are only 9 records in the output so I can see that there are none with really large values that would cause Excel to format the column width so big. Any ideas?

What is the datatype for the large column? If it’s a large varchar then it might be exporting the full column width even though the data is not that large. Perhaps a string function like substr might work to reduce the size.

Thanks for your response. I had thought of that as well, but these are defined as a number and they are not big numbers so there is no reason why the Excel column width should be so large.

From: Kevin Stern [mailto:bounce-KevinStern@toadworld.com]
Sent: Tuesday, June 18, 2013 3:30 PM
To: toaddatapoint@toadworld.com
Subject: RE: [Toad Data Point - Discussion Forum] Excel format issue with Automation

What is the datatype for the large column? If it’s a large varchar then it might be exporting the full column width even though the data is not that large. Perhaps a string function like substr might work to reduce the size.

When the file is exported, it is sent in an email. When you open it up it says that there are errors in the file that it has to repair before opening. I’ve looked at the log of these repair - shown below. And, I don’t know why I’m getting this message. I don’t choose any kind of style from the TOAD automation tool.

Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.

Repaired Records: Format from /xl/styles.xml part (Styles)

From: KelleyJ [mailto:bounce-kelleyjohnson2013@toadworld.com]
Sent: Tuesday, June 18, 2013 3:50 PM
To: toaddatapoint@toadworld.com
Subject: RE: [Toad Data Point - Discussion Forum] Excel format issue with Automation

Thanks for your response. I had thought of that as well, but these are defined as a number and they are not big numbers so there is no reason why the Excel column width should be so large.

From: Kevin Stern [mailto:bounce-KevinStern@toadworld.com]
Sent: Tuesday, June 18, 2013 3:30 PM
To: toaddatapoint@toadworld.com
Subject: RE: [Toad Data Point - Discussion Forum] Excel format issue with Automation

What is the datatype for the large column? If it’s a large varchar then it might be exporting the full column width even though the data is not that large. Perhaps a string function like substr might work to reduce the size.

It sounds like it could be something set in the Export Template that your Automation script is using. I would make sure that the template is specifying output format Excel and there is also an Auto Fit checkbox which may need to be checked.