Why does TransferText trim when importing string

[Ray C]

I'm using the following line to import a text delimited file :

DoCmd.TransferText acImportDelim, "MySpec", "MyTable", "D:\Export\File.txt",
False

One of the fields in the file has names that have extra spaces at the end.
For example: "Company","John Doe "

After the import, the table contains "John Doe" (no spaces after Doe).
How can I import the file so that spaces are also imported. In other words,
prevent Access from trimming the field. I need to re-export the field as it
was imported.

Thanks in advance
Ray

 

[Douglas J. Steele]

I don't believe there's any way to accomplish what you're trying to do.

Access tends to remove trailing blanks.

 

[Mark A. Sam]

Ray,

I may be wrong, but I don't believe you can import the field without the
trimming. I suggest that you could create another table and write code to
move the data record by record concantinating the extra spaces into the
fields.

God Bless,

Mark A Sam

 

[Mark A. Sam]

Doud, you can add them in though. I didn't think a textfield would hold
trailing spaces, so I tried it on a form on a bound textbox, [Field1] =
"Test " then checked the field and the spaces stayed.

 

[Ray C]

Mark, I tried to add spaces to a bound field and when I returned to the
record, the spaces aren't there. Access seems to always trim all the trailing
spaces before saving the record.

I'm shocked at this, it took me weeks to convince my "Oracle-fanatic" client
to use MS Access to build a simple import-export tool and now I have to tell
him that something THIS simple cannot be done? The Oracle guys who receive my
file will laugh at this...Thanks a lot Microsoft !!

 

[Ray C]

I figured it out.
Instead of using TransferText, I used the Open For Input statement,
processed each line by placing each field into an array and then using the
array to generate an INSERT statement for each record. This preserved the
spaces at the end.

I hope this helps anyone who might run into this problem in the future.

Ray

 

[Mark A. Sam]

Good job. You need to add the text in a way other than typing it into the
field or textbox. Personally I prefer that Access trim out the extra spaces
when typing in. I have had experience with some SQL server fields and
didn't like the fixed field format on some fields. But importing is another
story and I think maybe there should be an option to accept the text as is
rather than trimming.

 

[Mark A. Sam]

Mark, I tried to add spaces to a bound field and when I returned to the
record, the spaces aren't there. Access seems to always trim all the
trailing
spaces before saving the record.

I'm shocked at this, it took me weeks to convince my "Oracle-fanatic"
client
to use MS Access to build a simple import-export tool and now I have to
tell
him that something THIS simple cannot be done? The Oracle guys who receive
my
file will laugh at this...Thanks a lot Microsoft !!

You can write a procedure in Access to do this. It wouldn't be that
difficult at all.