December 18, 2009 at 10:02 am
Hi guys,
I’m converting an access frontend/SQL Server system to asp.net, and I need to export some data into excel for the purposes of delivery routing. The excel export needs to be exported in a particular format for the distributors so it can be imported into their (3rd party) routing program.
I’m using OPENROWSET to write data from a query to a template excel file and all my numbers are being formatted as text (excel adding an apostrophe to the start of the number), this is kinda stopping the import. The SQL Server that is exporting the data does not have office installed and the file will be automatically streamed to the user that requires it, so no chance of manually formatting it before it goes, and to be honest, I'd rather the whole thing was automatic.
I’d like to avoid having to install excel on the server, and I’d certainly like to avoid the user having to do any formatting at their end.
I have so far tried casting the values as int’s in the query, changing the registry settings for Jet on the SQL Server (the ImportMixedTypes and TypeGuessRows properties for the excel engine) and I’ve formatted the .xls file correctly for numbers. My trawl through google hasn’t really turned up any more answers so I’m left with posting here.
Here’s the export code:
Insert into OPENROWSET
(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\.....;',
'SELECT * FROM [sheet1$]'
)
Select …….
No surprises there really.
My question is really for anyone that’s come unstuck with this before, is there anyway to apply some sort of schema to the export, or any way of formatting the data? Or am I better off finding a different way of accomplishing this?
Any help would be much appreciated , if I'm unable to sort this soon I'll come up with an alternative of some description. I just don't like being beaten by something as trivial as this!
P.S. I’ve never had to post to a forum before and I feel so dirty not being able to work it out myself, ugghhh!!!
December 18, 2009 at 10:59 am
Not being an Excel expert, have you looked at background checking and its rules.
In Excel
Click your way down the Menu Bar:
Menu Bar
Tools
Options
Uncheck Enable Background checking
Rules
Uncheck "Number stored as text"
See if that helps. If it does please report back for it may help others.
December 19, 2009 at 10:54 am
Thank you for the reply bitbucket.
This does indeed hide the error message when the file is viewed in Excel, but the values in the cells are still stored as text. I need a way of changing the exported values to numeric, so the formatting is correct when the file is imported into another program.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply