Import Trailing Spaces

[Mike D]

I am using Access 2003 and have an issue. I am importing a delimited text
file with a pipe delimiter. I have one field named or_name. I need to have
the trailing spaces imported with the field name. Access truncates the spaces
on import, and it does import the leading spaces. This is the only field I
need to have the trailing spaces imported.

I am using the code:
DoCmd.TransferText acImportDelim, "Org_ip Import Specification", "Org_ip",
CurDir$ & "\Org_ip.txt", False, ""

Is there anyplace or any way to import the trailing spaces.

 

[Douglas J. Steele]

No, there isn't.

 

[Clifford Bass]

Hi Mike,

Since it is important, although quite why I would be curious to know,
you can if you create your own import routine in code. Simplified and in
English: Open file, read header, check header to see if expected, open
recordset for adding new data, loop through rest of records, parsing data
into recordset columns as appropriate, and including trailing spaces where
desired, close recordset, close file.

Clifford Bass

 

[Mike D]

When building the DB tables, this field is concatenated from several files
in another propriatary program. We have to QA the Data to insure the length
of the concatenated fields are <>33 for this info to be used properly withing
another different propriatry program.
So far, while playing with this, I have found that if i import that
particular file into excel and then into Access, the spaces are present in
the table.

 

[Clifford Bass]

Hi Mike,

That is a good alternative, if it is not too much of a pain to do all
the time. It probably could be automated.

Another possibility would be to allow the import to strip the extra
spaces and reapply them when you do the exporting. If you use a query you
can do something like this, which will always result in 25 characters for
TextField:

SELECT [TextField] & Space(25-Len(Nz([TextField],""))) AS TextFieldPadded
FROM tblSomeTable;

Clifford Bass

 

[Douglas J. Steele]

Or

SELECT Left([TextField] & Space(25), 25) AS TextFieldPadded
FROM tblSomeTable;


Space(25 - Len(Nz([TextField], ""))) will raise an error should TextField
contain 25 characters or more.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Mike,

That is a good alternative, if it is not too much of a pain to do all
the time. It probably could be automated.

Another possibility would be to allow the import to strip the extra
spaces and reapply them when you do the exporting. If you use a query you
can do something like this, which will always result in 25 characters for
TextField:

SELECT [TextField] & Space(25-Len(Nz([TextField],""))) AS TextFieldPadded
FROM tblSomeTable;

Clifford Bass

When building the DB tables, this field is concatenated from several
files
in another propriatary program. We have to QA the Data to insure the
length
of the concatenated fields are <>33 for this info to be used properly
withing
another different propriatry program.
So far, while playing with this, I have found that if i import that
particular file into excel and then into Access, the spaces are present
in
the table.

 

[Mike D]

I actually almost have it working. If i can change the delimiter from PIPE
(|) to Comma (,), and import it as a .csv file, it will bring the trailing
spaces into the table with it. I just have to figure out how to change the
delimiter in the .txt file now.

Also, Cliff and Doug, I really do appreciate the help.


Mike

Or

SELECT Left([TextField] & Space(25), 25) AS TextFieldPadded
FROM tblSomeTable;


Space(25 - Len(Nz([TextField], ""))) will raise an error should TextField
contain 25 characters or more.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Mike,

That is a good alternative, if it is not too much of a pain to do all
the time. It probably could be automated.

Another possibility would be to allow the import to strip the extra
spaces and reapply them when you do the exporting. If you use a query you
can do something like this, which will always result in 25 characters for
TextField:

SELECT [TextField] & Space(25-Len(Nz([TextField],""))) AS TextFieldPadded
FROM tblSomeTable;

Clifford Bass

When building the DB tables, this field is concatenated from several
files
in another propriatary program. We have to QA the Data to insure the
length
of the concatenated fields are <>33 for this info to be used properly
withing
another different propriatry program.
So far, while playing with this, I have found that if i import that
particular file into excel and then into Access, the spaces are present
in
the table.

:

Hi Mike,

Since it is important, although quite why I would be curious to
know,
you can if you create your own import routine in code. Simplified and
in
English: Open file, read header, check header to see if expected, open
recordset for adding new data, loop through rest of records, parsing
data
into recordset columns as appropriate, and including trailing spaces
where
desired, close recordset, close file.

Clifford Bass

:

I am using Access 2003 and have an issue. I am importing a delimited
text
file with a pipe delimiter. I have one field named or_name. I need to
have
the trailing spaces imported with the field name. Access truncates
the spaces
on import, and it does import the leading spaces. This is the only
field I
need to have the trailing spaces imported.

I am using the code:
DoCmd.TransferText acImportDelim, "Org_ip Import Specification",
"Org_ip",
CurDir$ & "\Org_ip.txt", False, ""

Is there anyplace or any way to import the trailing spaces.

 

[Clifford Bass]

Hi Doug,

Good alternate as long as he does not mind the truncation of the field.
And yeah, I thought of that issue, but decided to let him deal with it if he
encountered the issue. However, if truncation of the field is not
desireable, he could create a little function:

Public Function PositiveOrZero(ByVal intValue As Integer) As Integer

If intValue < 0 Then
PositiveOrZero = 0
Else
PositiveOrZero = intValue
End If

End Function

And use this instead:

SELECT [TextField] & Space(PositiveOrZero(25-Len(Nz([TextField],"")))) AS
TextFieldPaddedNotTruncated
FROM tblSomeTable;

I am sure there are other possibilities.

Clifford Bass

 

[Clifford Bass]

Hi Mike,

You are welcome. You might try the Bulk File Find/Replace Tool found
at http://www.bestcode.com/html/findreplace.html though a search on the
internet for "find and replace in file". I do not vouch for it. I am sure
there are tons of other possibilites.

Clifford Bass

I actually almost have it working. If i can change the delimiter from PIPE
(|) to Comma (,), and import it as a .csv file, it will bring the trailing
spaces into the table with it. I just have to figure out how to change the
delimiter in the .txt file now.

Also, Cliff and Doug, I really do appreciate the help.


Mike

Or

SELECT Left([TextField] & Space(25), 25) AS TextFieldPadded
FROM tblSomeTable;


Space(25 - Len(Nz([TextField], ""))) will raise an error should TextField
contain 25 characters or more.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Mike,

That is a good alternative, if it is not too much of a pain to do all
the time. It probably could be automated.

Another possibility would be to allow the import to strip the extra
spaces and reapply them when you do the exporting. If you use a query you
can do something like this, which will always result in 25 characters for
TextField:

SELECT [TextField] & Space(25-Len(Nz([TextField],""))) AS TextFieldPadded
FROM tblSomeTable;

Clifford Bass

:

When building the DB tables, this field is concatenated from several
files
in another propriatary program. We have to QA the Data to insure the
length
of the concatenated fields are <>33 for this info to be used properly
withing
another different propriatry program.
So far, while playing with this, I have found that if i import that
particular file into excel and then into Access, the spaces are present
in
the table.

:

Hi Mike,

Since it is important, although quite why I would be curious to
know,
you can if you create your own import routine in code. Simplified and
in
English: Open file, read header, check header to see if expected, open
recordset for adding new data, loop through rest of records, parsing
data
into recordset columns as appropriate, and including trailing spaces
where
desired, close recordset, close file.

Clifford Bass

:

I am using Access 2003 and have an issue. I am importing a delimited
text
file with a pipe delimiter. I have one field named or_name. I need to
have
the trailing spaces imported with the field name. Access truncates
the spaces
on import, and it does import the leading spaces. This is the only
field I
need to have the trailing spaces imported.

I am using the code:
DoCmd.TransferText acImportDelim, "Org_ip Import Specification",
"Org_ip",
CurDir$ & "\Org_ip.txt", False, ""

Is there anyplace or any way to import the trailing spaces.