Export specification Data Types

[ragtopcaddy via AccessMonster.com]

I have seen posted (by Ken Snell) in several places in this newsgroup,
instructions to open an export specification in the wizard and edit the data
types for each individual field. I have found that to be impossible. From
similar posts, I have surmised that there are supposed to be 2 columns under
Field Information. In the wizard that I'm looking at, there is only 1 column,
"Field Name". I can select individual field names, but I can't do a damn
thing with them. Any suggestions would be appreciated.

Bill R

 

[Ken Snell \(MVP\)]

The ability to edit the type of data is available for an import
specification, but not for an export specification. A text file does not
have a format per se, but you can set the "format" of the data in the query
that you export.

Can you give us some details about what you are wanting to achieve?

 

[ragtopcaddy via AccessMonster.com]

Ken,

Thanks, as always for your response.

I am exporting a query that has several fields of differing data types. I
have tried the following:

SELECT Type, ReceiveIndexID, PayIndexID,
CLng([Tenor]) AS TimeToMaturity,
Format([VaR],"0.000000") AS VaRFactor,
Format([01],"0.000000") AS PEFactorTB01,
Format([02],"0.000000") AS PEFactorTB02, <etc.>
FROM qryInsightTable

And I keep getting:

PAY,NG_AECO_CGPR_MO_USD,NG_AECO_CGPR_MO_USD,125.00,0.131644,0.186173,<etc.>

I have tried using an export specification, but as I stated and you have
elucidated, there is no way to force a data type in a text export spec. I
also have a schema.ini file on my desktop that looks like the following:

[InsightUpload.txt]
ColNameHeader=True
CharacterSet=OEM
Format=CommaDelimited
Col1=Type Char Width 25
Col2=ReceiveIndexID Char Width 255
Col3=PayIndexID Char Width 255
Col4=TimeToMaturity Integer
Col5=VaRFactor NUMERIC
Col6=PEFactorTB01 NUMERIC
Col7=PEFactorTB02 NUMERIC
<etc.>

But, even when I rename the schema.ini to schema._ni, I get the same result.
As you can see, TimeToMaturity is a double, not a long integer.

Thanks,

Bill R

The ability to edit the type of data is available for an import
specification, but not for an export specification. A text file does not
have a format per se, but you can set the "format" of the data in the query
that you export.

Can you give us some details about what you are wanting to achieve?

I have seen posted (by Ken Snell) in several places in this newsgroup,
instructions to open an export specification in the wizard and edit the

[quoted text clipped - 8 lines]

Bill R

 

[ragtopcaddy via AccessMonster.com]

Incidentally, the field "TimeToMaturity" which is an alias for [Tenor] in a
table, is never at any time in Access a double! In the original table as well
as the query which I am exporting, it is a Long. Why it would convert to a
double upon export is as baffling as it is frustrating.

The ability to edit the type of data is available for an import
specification, but not for an export specification. A text file does not
have a format per se, but you can set the "format" of the data in the query
that you export.

Can you give us some details about what you are wanting to achieve?

I have seen posted (by Ken Snell) in several places in this newsgroup,
instructions to open an export specification in the wizard and edit the

[quoted text clipped - 8 lines]

Bill R

 

[ragtopcaddy via AccessMonster.com]

Workaround:

After the export, can I run Access/VBA "edit/replace" code in Notepad to
replace ".00," with "," ?

The ability to edit the type of data is available for an import
specification, but not for an export specification. A text file does not
have a format per se, but you can set the "format" of the data in the query
that you export.

Can you give us some details about what you are wanting to achieve?

I have seen posted (by Ken Snell) in several places in this newsgroup,
instructions to open an export specification in the wizard and edit the

[quoted text clipped - 8 lines]

Bill R

 

[Ken Snell \(MVP\)]

First, I went back and looked at some old posts of mine, and I did find one
from 2002 that said you could set data type in an export specification. That
post was from my "unlearned" days... sorry for the misleading info.

Second, when ACCESS exports numeric data, it will default to using "0.00" as
the export format, regardless of how you've "cast" the number by a CLng or
other function. You must explicitly format an integer value to be an
integer:

SELECT [Type], ReceiveIndexID, PayIndexID,
Format([Tenor], "0") AS TimeToMaturity,
Format([VaR],"0.000000") AS VaRFactor,
Format([01],"0.000000") AS PEFactorTB01,
Format([02],"0.000000") AS PEFactorTB02, <etc.>
FROM qryInsightTable;

Third, I recommend that you not use Type as the name of a field. It and many
other words are reserved words in ACCESS, and can create serious confusion
for ACCESS and Jet. See these Knowledge Base articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763

If you must use it as a field name, then always surround it with [ ]
characters so that Jet and ACCESS will know it's not the reserved property
that you mean.

--

Ken Snell
<MS ACCESS MVP>

Ken,

Thanks, as always for your response.

I am exporting a query that has several fields of differing data types. I
have tried the following:

SELECT Type, ReceiveIndexID, PayIndexID,
CLng([Tenor]) AS TimeToMaturity,
Format([VaR],"0.000000") AS VaRFactor,
Format([01],"0.000000") AS PEFactorTB01,
Format([02],"0.000000") AS PEFactorTB02, <etc.>
FROM qryInsightTable

And I keep getting:

PAY,NG_AECO_CGPR_MO_USD,NG_AECO_CGPR_MO_USD,125.00,0.131644,0.186173,<etc.>

I have tried using an export specification, but as I stated and you have
elucidated, there is no way to force a data type in a text export spec. I
also have a schema.ini file on my desktop that looks like the following:

[InsightUpload.txt]
ColNameHeader=True
CharacterSet=OEM
Format=CommaDelimited
Col1=Type Char Width 25
Col2=ReceiveIndexID Char Width 255
Col3=PayIndexID Char Width 255
Col4=TimeToMaturity Integer
Col5=VaRFactor NUMERIC
Col6=PEFactorTB01 NUMERIC
Col7=PEFactorTB02 NUMERIC
<etc.>

But, even when I rename the schema.ini to schema._ni, I get the same
result.
As you can see, TimeToMaturity is a double, not a long integer.

Thanks,

Bill R

The ability to edit the type of data is available for an import
specification, but not for an export specification. A text file does not
have a format per se, but you can set the "format" of the data in the
query
that you export.

Can you give us some details about what you are wanting to achieve?

I have seen posted (by Ken Snell) in several places in this newsgroup,
instructions to open an export specification in the wizard and edit the

[quoted text clipped - 8 lines]

Bill R

 

[ragtopcaddy via AccessMonster.com]

Ken,

Thanks for the info. I will try your suggestion. I usually put those field
names that are reserved words in brackets, but in this instance I neglected
them.

Bill

First, I went back and looked at some old posts of mine, and I did find one
from 2002 that said you could set data type in an export specification. That
post was from my "unlearned" days... sorry for the misleading info.

Second, when ACCESS exports numeric data, it will default to using "0.00" as
the export format, regardless of how you've "cast" the number by a CLng or
other function. You must explicitly format an integer value to be an
integer:

SELECT [Type], ReceiveIndexID, PayIndexID,
Format([Tenor], "0") AS TimeToMaturity,
Format([VaR],"0.000000") AS VaRFactor,
Format([01],"0.000000") AS PEFactorTB01,
Format([02],"0.000000") AS PEFactorTB02, <etc.>
FROM qryInsightTable;

Third, I recommend that you not use Type as the name of a field. It and many
other words are reserved words in ACCESS, and can create serious confusion
for ACCESS and Jet. See these Knowledge Base articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763

If you must use it as a field name, then always surround it with [ ]
characters so that Jet and ACCESS will know it's not the reserved property
that you mean.

Ken,

[quoted text clipped - 51 lines]

 

[ragtopcaddy via AccessMonster.com]

Ken,

If I use "s in the export spec it looks OK, but I want to export without the
"s.

Bill R

First, I went back and looked at some old posts of mine, and I did find one
from 2002 that said you could set data type in an export specification. That
post was from my "unlearned" days... sorry for the misleading info.

Second, when ACCESS exports numeric data, it will default to using "0.00" as
the export format, regardless of how you've "cast" the number by a CLng or
other function. You must explicitly format an integer value to be an
integer:

SELECT [Type], ReceiveIndexID, PayIndexID,
Format([Tenor], "0") AS TimeToMaturity,
Format([VaR],"0.000000") AS VaRFactor,
Format([01],"0.000000") AS PEFactorTB01,
Format([02],"0.000000") AS PEFactorTB02, <etc.>
FROM qryInsightTable;

Third, I recommend that you not use Type as the name of a field. It and many
other words are reserved words in ACCESS, and can create serious confusion
for ACCESS and Jet. See these Knowledge Base articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763

If you must use it as a field name, then always surround it with [ ]
characters so that Jet and ACCESS will know it's not the reserved property
that you mean.

Ken,

[quoted text clipped - 51 lines]

 

[ragtopcaddy via AccessMonster.com]

Apparantly there was some confusion going on between the redesigned export
specs and the query formatting. I've got it straightened out now and the
output looks just fine.

Thanks again,

Bill R

First, I went back and looked at some old posts of mine, and I did find one
from 2002 that said you could set data type in an export specification. That
post was from my "unlearned" days... sorry for the misleading info.

Second, when ACCESS exports numeric data, it will default to using "0.00" as
the export format, regardless of how you've "cast" the number by a CLng or
other function. You must explicitly format an integer value to be an
integer:

SELECT [Type], ReceiveIndexID, PayIndexID,
Format([Tenor], "0") AS TimeToMaturity,
Format([VaR],"0.000000") AS VaRFactor,
Format([01],"0.000000") AS PEFactorTB01,
Format([02],"0.000000") AS PEFactorTB02, <etc.>
FROM qryInsightTable;

Third, I recommend that you not use Type as the name of a field. It and many
other words are reserved words in ACCESS, and can create serious confusion
for ACCESS and Jet. See these Knowledge Base articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763

If you must use it as a field name, then always surround it with [ ]
characters so that Jet and ACCESS will know it's not the reserved property
that you mean.

Ken,

[quoted text clipped - 51 lines]

 

[Ken Snell \(MVP\)]

Glad it's working! You're welcome.

--

Ken Snell
<MS ACCESS MVP>

Apparantly there was some confusion going on between the redesigned export
specs and the query formatting. I've got it straightened out now and the
output looks just fine.

Thanks again,

Bill R

< snipped >