imported field has leading spaces

[joeb]

I am working on a database that uses data provided by a
third party in a weekly TXT file.

I was given the field specifications for the fixed-width
file and have created a table using the same fields. I
have given these fields the appropriate datatypes for
their intended uses and have used field lengths for the
text fields that matched the field lengths given in the
field specs.

The problem is with a transaction number field. Although
this contains only numbers, it was set up as a text field
in the original specs and I have done the same in my
table. This field has a specified length of 10 characters
but is currently only using 8. When I import the txt file,
the data in this field shows 2 spaces before the actual 8
characters. I know several workarounds for this but I was
hoping to learn something about how the import works so
that I can avoid the problem it altogether.

Any assistance would be appreciated.

Thanks in advance...Joe

 

[John Nurick]

Hi Joe,

When I import a 10-character fixed width field containing 8-digit
numbers with 2 leading spaces to a Number (Long) field, it works fine:
the leading spaces disappear as you'd expect. So I infer that if you're
getting leading spaces, you're importing this data into a text field.

In that case, the way Access works is to import all the characters in
the field except any trailing spaces. There is no way to modify the
behaviour of the text import routine, so in your terms there is nothing
for it but work-rounds. (The one I'd use is to define a dummy
2-character field and skip importing it.)

 

[Joeb]

Hi John,

Thanks for your reply. I'm disappointed that Access
doesn't provide a neater solution to this. Your "work
around" is significantly more direct and efficient than
any I had conceived. I've read a considerable amount about
Access and Visual BAsic in the last few days and its
telling me (as are you, I think) that I'm not using the
right datatype. I am rethinking it and will try for the
best balance of functionality and size.

Thanks again for all of your help.
....Joe

-----Original Message-----
Hi Joe,

When I import a 10-character fixed width field containing 8-digit
numbers with 2 leading spaces to a Number (Long) field, it works fine:
the leading spaces disappear as you'd expect. So I infer that if you're
getting leading spaces, you're importing this data into a text field.

In that case, the way Access works is to import all the characters in
the field except any trailing spaces. There is no way to modify the
behaviour of the text import routine, so in your terms there is nothing
for it but work-rounds. (The one I'd use is to define a dummy
2-character field and skip importing it.)

I am working on a database that uses data provided by a
third party in a weekly TXT file.

I was given the field specifications for the fixed-width
file and have created a table using the same fields. I
have given these fields the appropriate datatypes for
their intended uses and have used field lengths for the
text fields that matched the field lengths given in the
field specs.

The problem is with a transaction number field. Although
this contains only numbers, it was set up as a text field
in the original specs and I have done the same in my
table. This field has a specified length of 10 characters
but is currently only using 8. When I import the txt file,
the data in this field shows 2 spaces before the actual 8
characters. I know several workarounds for this but I was
hoping to learn something about how the import works so
that I can avoid the problem it altogether.

Any assistance would be appreciated.

Thanks in advance...Joe

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[John Nurick]

Hi Joe,

I don't feel that this is a problem that needs a solution (though there
are plenty of other problems that do<g>). Remember that there's
_nothing_ in a fixed width file to define where the fields begin or end:
it's necessary either to know the structure from another source or to
infer it from the appearance of the data.

If you define the field in question as a numeric field, the value
imported as a number and there's no such thing as a leading space. But
if you define it as a text field I feel it's unreasonable to complain
when Access imports what's in the field: there might be more reason to
complain because Access drops the trailing spaces.

Hi John,

Thanks for your reply. I'm disappointed that Access
doesn't provide a neater solution to this. Your "work
around" is significantly more direct and efficient than
any I had conceived. I've read a considerable amount about
Access and Visual BAsic in the last few days and its
telling me (as are you, I think) that I'm not using the
right datatype. I am rethinking it and will try for the
best balance of functionality and size.

Thanks again for all of your help.
...Joe

-----Original Message-----
Hi Joe,

When I import a 10-character fixed width field containing 8-digit
numbers with 2 leading spaces to a Number (Long) field, it works fine:
the leading spaces disappear as you'd expect. So I infer that if you're
getting leading spaces, you're importing this data into a text field.

In that case, the way Access works is to import all the characters in
the field except any trailing spaces. There is no way to modify the
behaviour of the text import routine, so in your terms there is nothing
for it but work-rounds. (The one I'd use is to define a dummy
2-character field and skip importing it.)

I am working on a database that uses data provided by a
third party in a weekly TXT file.

I was given the field specifications for the fixed-width
file and have created a table using the same fields. I
have given these fields the appropriate datatypes for
their intended uses and have used field lengths for the
text fields that matched the field lengths given in the
field specs.

The problem is with a transaction number field. Although
this contains only numbers, it was set up as a text field
in the original specs and I have done the same in my
table. This field has a specified length of 10 characters
but is currently only using 8. When I import the txt file,
the data in this field shows 2 spaces before the actual 8
characters. I know several workarounds for this but I was
hoping to learn something about how the import works so
that I can avoid the problem it altogether.

Any assistance would be appreciated.

Thanks in advance...Joe

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.