Leading and Trailing blanks are lost on import

[Dominic Olivastro]

I am using Office 2003 on Win XP. I find that if I import a text file from
my mainframe into access, I lose both leading and trailing blanks on text
fields.

I suppose losing trailing blanks can be called a feature, but how do I get
back my leading blanks? Has anyone else noticed this?

Dom

 

[Joseph Meehan]

I am using Office 2003 on Win XP. I find that if I import a text
file from my mainframe into access, I lose both leading and trailing
blanks on text fields.

I suppose losing trailing blanks can be called a feature, but how do
I get back my leading blanks? Has anyone else noticed this?

Dom

Why would you want leading blanks in a database? I am sorry I can't
really answer your question as I have not experience the "problem" as I
would have always considered it a "feature". I do seen to recall something
about this way back when and there may be a way of forcing it, but again
why?

 

[John Vinson]

I am using Office 2003 on Win XP. I find that if I import a text file from
my mainframe into access, I lose both leading and trailing blanks on text
fields.

I suppose losing trailing blanks can be called a feature, but how do I get
back my leading blanks? Has anyone else noticed this?

Dom

I have to agree with Joseph that a table containing values "Fred",
" Fred" and " Fred" and treating them as distinct is going to be more
than a bit awkward for the user! Leading and trailing blanks are
essentially data *presentation* features, and as such should (IMHO)
typically not be stored in the table.

It's obscure, but you *can* create a fixed-text field in an Access
table, using the DDL "Create Table" query. By default, text fields are
a type of varchar and truncate both trailing and leading blanks; a
fixed text field will retain them (causing the database to grow faster
since all the Text fields are occupied to their full length).

John W. Vinson[MVP]

 

[Dominic Olivastro]

That's not a feature, it's a bug. First, leading blanks are not trimmed if
I enter the data by hand or through ADO. Second, larger databases, like
Sybase and Oracle and even MS's own SQL_Server, do not routinely trim
leading blanks. Third, if I want them trimmed, I can always do an update
query.

As to why I need leading spaces, the data is a number with an implied
decimal point after the third character. So that "bb250" (or 2.50) is not
the same as "250" (or 250)

Dom

 

[Joseph Meehan]

That's not a feature, it's a bug. First, leading blanks are not
trimmed if I enter the data by hand or through ADO. Second, larger
databases, like Sybase and Oracle and even MS's own SQL_Server, do
not routinely trim leading blanks. Third, if I want them trimmed, I
can always do an update query.

Your bug, my feature.

As to why I need leading spaces, the data is a number with an implied
decimal point after the third character. So that "bb250" (or 2.50)
is not the same as "250" (or 250)

You have to admit that is not a very common way of setting up data. You
have a rather unusual case. Sorry to hear about it, but for most people it
works fine or better the way it is. I suppose you are not the only one who
feels the way you do however.