Decimal Point Appearing in Fixed Width Export File with Access 2K

[Mark H.]

I am having a problem with properly exporting a field from Access 2000. The data starts its life in an Excel spreadsheet. The column that is causing problems is a field that contains either 1 digit or two digits (so a value between 0 and 99). I import this data into a table, and then eventually export it to a fixed width text file, which will be used as input to a mainframe job.

When I export the table containing the imported Excel data (although I don't think its origin is relevant, since it happens with data that I input into the table by hand) I end up getting a decimal point in the second byte for this column if the value in the column is a single digit. In other words, if the data is a "6", it ends up being a "6." in the fixed width file that is created. Also, if the data is padded with a leading zero, as in "06", the field ends up looking like "6." in the exported file. What I want it to do is be padded with a leading zero if there is a single digit in the field, or if it has a leading zero already in it, but Access seems to always want to remove the zero and put in a trailing decimal point.

I have tried defining the field in the Access table as various different types of numeric fields, as well as a text field, and it does not change the way the data ultimately gets exported. It always has a decimal point in the second byte when the data contains a single digit (or a value with a leading zero) in that field. In playing with the export specification (over and over again) it appears to me that the field "Decimal Symbol" that appears on the Export Specification screen is what is getting put after the single digit, since when I change that to, say, a semi-colon, the data in the exported file looks like "6;" instead of '6.'. There is no way that I can find to stop Access from placing the decimal separator after the data no matter what I have tried. I really thought changing the data type to text would prevent Access from trying to treat it as a number upon export but no such luck. All the other fields in the table get exported properly into fixed width columns in the file that is created, it is just this one field that is causing problems.

Is there a way that I can get this two byte field (whether it contains a single digit or is preceded by a leading zero) to contain a leading zero and then the digit when it is exported to a fixed width text file with capabilities built into Access? I am not much of a VB or other PC language programmer, and I want this to be a simple solution if possible, since I will be automating the import/export process and the simpler the better when it comes to automation. So, I'd like to avoid a complicated programming solution to this problem. I can write a pre-processor program on the mainframe to "fix" the data before it is used up there, but it seems like there should be a way to create the file properly in the first place.

Any ideas???

Thanks,

Mark

 

[Ronald W. Roberts]

I am having a problem with properly exporting a field from Access 2000.The data starts its life in an Excel spreadsheet. The column that is causing problems is a field that contains either 1 digit or two digits (soa value between 0 and 99). I import this data into a table, and then eventually export it to a fixed width text file, which will be used as input to a mainframe job.

When I export the table containing the imported Excel data (although I don't think its origin is relevant, since it happens with data that I input into the table by hand) I end up getting a decimal point in the secondbyte for this column if the value in the column is a single digit. In other words, if the data is a "6", it ends up being a "6." in the fixed width file that is created. Also, if the data is padded with a leading zero, as in "06", the field ends up looking like "6." in the exported file. What I want it to do is be padded with a leading zero if there is a single digit in the field, or if it has a leading zero already in it, but Access seems to always want to remove the zero and put in a trailing decimal point.

I have tried defining the field in the Access table as various different types of numeric fields, as well as a text field, and it does not change the way the data ultimately gets exported. It always has a decimal point in the second byte when the data contains a single digit (or a value with a leading zero) in that field. In playing with the export specification (over and over again) it appears to me that the field "Decimal Symbol" that appears on the Export Specification screen is what is getting put after the single digit, since when I change that to, say, a semi-colon, the data in the exported file looks like "6;" instead of '6.'. There is no way that I can find to stop Access from placing the decimal separator after the data no matter what I have tried. I really thought changing thedata type to text would prevent Access from trying to treat it as a number upon export but no such luck. All the other fields in the table get exported properly into fixed width columns in the
file that is created, it is just this one field that is causing problems.

Is there a way that I can get this two byte field (whether it contains a single digit or is preceded by a leading zero) to contain a leading zero and then the digit when it is exported to a fixed width text file with capabilities built into Access? I am not much of a VB or other PC language programmer, and I want this to be a simple solution if possible, sinceI will be automating the import/export process and the simpler the better when it comes to automation. So, I'd like to avoid a complicated programming solution to this problem. I can write a pre-processor program on the mainframe to "fix" the data before it is used up there, but it seems like there should be a way to create the file properly in the first place.

Any ideas???

Thanks,

Mark

Use a query and format the data in the query.
Then export the query using it as the record source or file name for theexport.

NewNumber: Format([rs_MyNumber],"000")

Ron

 

[Mark H]

Thanks Ron, that should probably work - I'll give it a try.

A follow-up question, however:

I noticed in looking around my database that there is a hidden (system) table called MSysIMEXColumns that appears to contain the column definitions for the specifications I have created. I noticed for the troublesome column that the "data type" column in the MSysIMEXColumns table for this fieldname is listed as a type 7, where all the other fields (which are also text fields) are listed as data type 10. I suspect that somehow the 7 means that it should treat the data for this field as a numeric field of some sort, and that is why the data ends up getting the decimal point for single digit entries.

There does not appear to be a way to change this MSysIMEXColumns table, but I suspect if I could make the 7 a 10 in this table I would have corrected my problem. Does anyone know of a way to change this system table? It doesn't seem like it could do serious harm since it is only pertaining to export specifications, and I could always delete the spec if I needed to.

Secondly, does anyone know how the export spec might have decided that this field was numeric, and thus it assigned it a type of 7? There doesn't really seem to be anywhere that one specifies a type for the columns, just an offset and a length, but clearly there is something going on in this spec that was causing my problem.

Finally, is there documentation anywhere that I could reference to see if my assumption is correct? In other words, does Microsoft make available what the values placed in the data type field mean, so I can figure out what a 7 versus a 10 is. I did some searching but came up with nothing useful.

TIA,

Mark Heider

 

[Bart]

Hey Mark,

I'm having the exact same issue. Have you found a fix?
The query idea didn't work for me. Did it work for you?

Thanks,

Bart

-----Original Message-----
I am having a problem with properly exporting a field

from Access 2000. The data starts its life in an Excel
spreadsheet. The column that is causing problems is a
field that contains either 1 digit or two digits (so a
value between 0 and 99). I import this data into a table,
and then eventually export it to a fixed width text file,
which will be used as input to a mainframe job.

When I export the table containing the imported Excel

data (although I don't think its origin is relevant, since
it happens with data that I input into the table by hand)
I end up getting a decimal point in the second byte for
this column if the value in the column is a single digit.
In other words, if the data is a "6", it ends up being
a "6." in the fixed width file that is created. Also, if
the data is padded with a leading zero, as in "06", the
field ends up looking like "6." in the exported file. What
I want it to do is be padded with a leading zero if there
is a single digit in the field, or if it has a leading
zero already in it, but Access seems to always want to
remove the zero and put in a trailing decimal point.

I have tried defining the field in the Access table as

various different types of numeric fields, as well as a
text field, and it does not change the way the data
ultimately gets exported. It always has a decimal point
in the second byte when the data contains a single digit
(or a value with a leading zero) in that field. In
playing with the export specification (over and over
again) it appears to me that the field "Decimal Symbol"
that appears on the Export Specification screen is what is
getting put after the single digit, since when I change
that to, say, a semi-colon, the data in the exported file
looks like "6;" instead of '6.'. There is no way that I
can find to stop Access from placing the decimal separator
after the data no matter what I have tried. I really
thought changing the data type to text would prevent
Access from trying to treat it as a number upon export but
no such luck. All the other fields in the table get
exported properly into fixed width columns in the file
that is created, it is just this one field that is causing
problems.

Is there a way that I can get this two byte field

(whether it contains a single digit or is preceded by a
leading zero) to contain a leading zero and then the digit
when it is exported to a fixed width text file with
capabilities built into Access? I am not much of a VB or
other PC language programmer, and I want this to be a
simple solution if possible, since I will be automating
the import/export process and the simpler the better when
it comes to automation. So, I'd like to avoid a
complicated programming solution to this problem. I can
write a pre-processor program on the mainframe to "fix"
the data before it is used up there, but it seems like
there should be a way to create the file properly in the
first place.

 

[Ronald W. Roberts]

Thanks Ron, that should probably work - I'll give it a try.

A follow-up question, however:

I noticed in looking around my database that there is a hidden (system) table called MSysIMEXColumns that appears to contain the column definitions for the specifications I have created. I noticed for the troublesome column that the "data type" column in the MSysIMEXColumns table for this fieldname is listed as a type 7, where all the other fields (which are also text fields) are listed as data type 10. I suspect that somehow the 7 means that it should treat the data for this field as a numeric field of some sort, and that is why the data ends up getting the decimal point for single digit entries.

There does not appear to be a way to change this MSysIMEXColumns table, but I suspect if I could make the 7 a 10 in this table I would have corrected my problem. Does anyone know of a way to change this system table? It doesn't seem like it could do serious harm since it is only pertaining to export specifications, and I could always delete the spec if I needed to.

Secondly, does anyone know how the export spec might have decided that this field was numeric, and thus it assigned it a type of 7? There doesn't really seem to be anywhere that one specifies a type for the columns, just an offset and a length, but clearly there is something going on in this spec that was causing my problem.

Finally, is there documentation anywhere that I could reference to see if my assumption is correct? In other words, does Microsoft make available what the values placed in the data type field mean, so I can figure out what a 7 versus a 10 is. I did some searching but came up with nothing useful.

TIA,

Mark Heider

Sorry it has taken so long to reply.
If you import or export a file as txt, an "Advanced" button in the lower left
is unhidden allowing you to change the specification of the operation and or
save the specification with a new name. See if you can start an import or
export of a test or dummy file selecting TXT as the file type. This will allow
you to see the advanced button. At that point you can try to edit the specification
and save it with a new name and use the new specification name in your export
operation.

I'm not sure how the export decides what to use for the data type, you would think
Access would know from the table definition. On the import, Access looks at several
rows of data and determines from that what a column should be, numeric or text.

For the numeric codes Access uses, look under Microsoft Access Constants - Overview.
At the bottom there are links to different types of constants showing the codes used internally
in Access.

If you use a format statement in a query, you have to be sure the output will be seen as text
and not as numeric. Just because you format a number as 001, Access still doesn't know
if it is numeric or text. Try putting a single quote in front of the numeric data. Excel should
see this as a text field and not numeric.

MyNum: "'" & Format(Mydata,"000")

Ron