Please quick help on importing text

  • Thread starter Thread starter Al
  • Start date Start date
A

Al

I am importing from a text file using the following line of
code:

DoCmd.TransferText acImportDelim,
"CBC_Import_Specification", tblName, Me![FileFolder], False

everything is working fine. I only have a little problem.
because of the fact that the text file that I am importing
from has columns by tab spacing, when I import to the table
in access some fields have square characters equal to the
number of tab spacing. I used trim and LTrim but It did not
work. It is hard to go to the text files and fix them all,
they are too many. Any Ideas?
thanks
Al
 
Al,
don't know if you tried it yet, but I'd suggest opening
your "CBC_Import_Specification" and modifying it to
use "Tab" as a text delimiter. The one way I know to
modify the specification is:
- start import of any text file.
- on the first screen that appears click "Advanced..."
(lower left corner)
- then click "specs"
- open the spec that you need
- once it loads change the delimiter to {tab}
save that spec and use it in you code.
It worked fine on my computer, the "squares" didn't go to
the table.
 
Yes, However, I must keep the delimiter as "=" sign. in the
same time I need to get rid of the tab
spacing.Unfortunately, the import specs do not allow for
more than one delimiter.
any other Ideas, please
thanks
-----Original Message-----
Al,
don't know if you tried it yet, but I'd suggest opening
your "CBC_Import_Specification" and modifying it to
use "Tab" as a text delimiter. The one way I know to
modify the specification is:
- start import of any text file.
- on the first screen that appears click "Advanced..."
(lower left corner)
- then click "specs"
- open the spec that you need
- once it loads change the delimiter to {tab}
save that spec and use it in you code.
It worked fine on my computer, the "squares" didn't go to
the table.


-----Original Message-----
I am importing from a text file using the following line of
code:

DoCmd.TransferText acImportDelim,
"CBC_Import_Specification", tblName, Me![FileFolder], False

everything is working fine. I only have a little problem.
because of the fact that the text file that I am importing
from has columns by tab spacing, when I import to the table
in access some fields have square characters equal to the
number of tab spacing. I used trim and LTrim but It did not
work. It is hard to go to the text files and fix them all,
they are too many. Any Ideas?
thanks
Al
.
Click to expand...
.
Click to expand...
 
This is a little manual, but how about once the records
are in access do a search/replace on a whole table (make
sure it looks in any part of field) and replace the square
with nothing?
-----Original Message-----
Yes, However, I must keep the delimiter as "=" sign. in the
same time I need to get rid of the tab
spacing.Unfortunately, the import specs do not allow for
more than one delimiter.
any other Ideas, please
thanks
-----Original Message-----
Al,
don't know if you tried it yet, but I'd suggest opening
your "CBC_Import_Specification" and modifying it to
use "Tab" as a text delimiter. The one way I know to
modify the specification is:
- start import of any text file.
- on the first screen that appears click "Advanced..."
(lower left corner)
- then click "specs"
- open the spec that you need
- once it loads change the delimiter to {tab}
save that spec and use it in you code.
It worked fine on my computer, the "squares" didn't go to
the table.


-----Original Message-----
I am importing from a text file using the following
Click to expand...
line
of
code:

DoCmd.TransferText acImportDelim,
"CBC_Import_Specification", tblName, Me![FileFolder], False

everything is working fine. I only have a little problem.
because of the fact that the text file that I am importing
from has columns by tab spacing, when I import to the table
in access some fields have square characters equal to the
number of tab spacing. I used trim and LTrim but It did not
work. It is hard to go to the text files and fix them all,
they are too many. Any Ideas?
thanks
Al
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
I would like to do this programatically, since this is
going to be used by different users. however, out of
curiousity, how do you refer to the square in the search
for field? (this is a representation of a tab space!). I
think there should be a function like ltrim that can
recognize this characters such as tab or enter,etc...
Al
-----Original Message-----
This is a little manual, but how about once the records
are in access do a search/replace on a whole table (make
sure it looks in any part of field) and replace the square
with nothing?
-----Original Message-----
Yes, However, I must keep the delimiter as "=" sign. in the
same time I need to get rid of the tab
spacing.Unfortunately, the import specs do not allow for
more than one delimiter.
any other Ideas, please
thanks
-----Original Message-----
Al,
don't know if you tried it yet, but I'd suggest opening
your "CBC_Import_Specification" and modifying it to
use "Tab" as a text delimiter. The one way I know to
modify the specification is:
- start import of any text file.
- on the first screen that appears click "Advanced..."
(lower left corner)
- then click "specs"
- open the spec that you need
- once it loads change the delimiter to {tab}
save that spec and use it in you code.
It worked fine on my computer, the "squares" didn't go to
the table.



-----Original Message-----
I am importing from a text file using the following line
of
code:

DoCmd.TransferText acImportDelim,
"CBC_Import_Specification", tblName, Me![FileFolder],
False

everything is working fine. I only have a little problem.
because of the fact that the text file that I am importing
from has columns by tab spacing, when I import to the
table
in access some fields have square characters equal to the
number of tab spacing. I used trim and LTrim but It did
not
work. It is hard to go to the text files and fix them all,
they are too many. Any Ideas?
thanks
Al
.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
I've used the Replace() function to remove Carriage
returns from text with nothing which. The Carriage
Returns were Chr$(13)+Chr$(10). Look up the Replace
function in your help.

I thing the format is something like goodstring=Replace
([startingstring],"]","") You might have to
substitute "]" with the correct Chr$(#).

Let me know if it works.

Kevin
-----Original Message-----
I would like to do this programatically, since this is
going to be used by different users. however, out of
curiousity, how do you refer to the square in the search
for field? (this is a representation of a tab space!). I
think there should be a function like ltrim that can
recognize this characters such as tab or enter,etc...
Al
-----Original Message-----
This is a little manual, but how about once the records
are in access do a search/replace on a whole table (make
sure it looks in any part of field) and replace the square
with nothing?
-----Original Message-----
Yes, However, I must keep the delimiter as "=" sign. in the
same time I need to get rid of the tab
spacing.Unfortunately, the import specs do not allow for
more than one delimiter.
any other Ideas, please
thanks
-----Original Message-----
Al,
don't know if you tried it yet, but I'd suggest opening
your "CBC_Import_Specification" and modifying it to
use "Tab" as a text delimiter. The one way I know to
modify the specification is:
- start import of any text file.
- on the first screen that appears click "Advanced..."
(lower left corner)
- then click "specs"
- open the spec that you need
- once it loads change the delimiter to {tab}
save that spec and use it in you code.
It worked fine on my computer, the "squares" didn't go to
the table.



-----Original Message-----
I am importing from a text file using the following line
of
code:

DoCmd.TransferText acImportDelim,
"CBC_Import_Specification", tblName, Me![FileFolder],
False

everything is working fine. I only have a little problem.
because of the fact that the text file that I am importing
from has columns by tab spacing, when I import to the
table
in access some fields have square characters equal to the
number of tab spacing. I used trim and LTrim but It did
not
work. It is hard to go to the text files and fix them all,
they are too many. Any Ideas?
thanks
Al
.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Kevin,
Thank you, the following worked:
Replace([textboxname], Chr(9), "")
Al
-----Original Message-----
I've used the Replace() function to remove Carriage
returns from text with nothing which. The Carriage
Returns were Chr$(13)+Chr$(10). Look up the Replace
function in your help.

I thing the format is something like goodstring=Replace
([startingstring],"]","") You might have to
substitute "]" with the correct Chr$(#).

Let me know if it works.

Kevin
-----Original Message-----
I would like to do this programatically, since this is
going to be used by different users. however, out of
curiousity, how do you refer to the square in the search
for field? (this is a representation of a tab space!). I
think there should be a function like ltrim that can
recognize this characters such as tab or enter,etc...
Al
-----Original Message-----
This is a little manual, but how about once the records
are in access do a search/replace on a whole table (make
sure it looks in any part of field) and replace the square
with nothing?

-----Original Message-----
Yes, However, I must keep the delimiter as "=" sign. in
the
same time I need to get rid of the tab
spacing.Unfortunately, the import specs do not allow for
more than one delimiter.
any other Ideas, please
thanks
-----Original Message-----
Al,
don't know if you tried it yet, but I'd suggest opening
your "CBC_Import_Specification" and modifying it to
use "Tab" as a text delimiter. The one way I know to
modify the specification is:
- start import of any text file.
- on the first screen that appears click "Advanced..."
(lower left corner)
- then click "specs"
- open the spec that you need
- once it loads change the delimiter to {tab}
save that spec and use it in you code.
It worked fine on my computer, the "squares" didn't go
to
the table.



-----Original Message-----
I am importing from a text file using the following
line
of
code:

DoCmd.TransferText acImportDelim,
"CBC_Import_Specification", tblName, Me! [FileFolder],
False

everything is working fine. I only have a little
problem.
because of the fact that the text file that I am
importing
from has columns by tab spacing, when I import to the
table
in access some fields have square characters equal to
the
number of tab spacing. I used trim and LTrim but It did
not
work. It is hard to go to the text files and fix them
all,
they are too many. Any Ideas?
thanks
Al
.

.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top