Thanks for the tips.
I am aware of point 1 you mentioned and I do that, but wasn't aware
of point 2 and will have a look at that.

RBS

On 2/24/12, Kjell Rilbe <kjell.rilbe@...> wrote:
> Den 2012-02-24 10:13 skrev Bart Smissaert såhär:
>>
>> I think I have figured this out now and it was just a display problem
>> to do with
>> Excel making numerics from text, so not showing a trailing or leading
>> space.
>>
>
> I've been working VERY MUCH with database data and Excel, by copying and
> pasting data from SQL tools' result grids (FlameRobin and SQL Server
> Management Studio).
>
> What I've learned along the way and would *strongly* recommend:
>>
>>
> 1. ALWAYS format the Excel columns appropriately BEFORE pasting the
> data. If it's a text column, format as text in Excel. If it's a date,
> make sure your SQL and/or grid outputs a format that Excel understands,
> and format the Excel column to match the format you get from SQL. This
> will prevent a whole lot of problems.
>
> 2. At least in Excel 2007, doing large batches of "replace" operations,
> e.g. replacing whole cells containing "NULL" with nothing, is rather
> fast, but before being able to close the replace dialog, it does
> something more which can take FOREVER if tens of thousands of cells were
> replaced. So, I've grown accustomed to doing coalesce("Datacol", '') for
> all nullable varchar columns. For char you need to cast to varchar
> inside the coalesce to avoid getting Excel cells with N spaces for
> char(N). For int and other datatypes, you may be able to get what you
> want by a simple cast to varchar inside the coalesce, but othertimes you
> may need some special formatting of the string. Replacing NULL in Excel
> might be quicker/simpler in those cases.
>
> Kjell
>
> --
> --------------------------------------
> Kjell Rilbe
> DataDIA AB
> E-post: kjell@...
> Telefon: 08-761 06 55
> Mobil: 0733-44 24 64
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>
>