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]