Values change from 99.32 to 0.99 when transfer to text file

[gillonia]

Apologies in advance if this is a simple query but I'm very new to Access.

I have created a Macro within Access 2003 that has two Actions; TransferSpreadsheet and TransferText.

The first Action takes two fields from a spreadsheet (ie FEB 99.32) and creates a table. The values within this are displaying correctly.

The second Action should output this to a Text File but is giving me the values FEB 0.99. I wouldn't mind if it gave me the values FEB 0.9932 as I'm using the text file to import into an Oracle Database and can get round this.

I've attached the parameters I'm using.
Thnx in advance for any suggestions/advice.
IanG

TransferSpreadsheet -
Transfer Type: Link
Spreadsheet Type: MS Excel 8-10
Table Name: Availability
File Name: <File Name>.xls
Has Field Names: No
Range: Avail!A24:B24

TransferText -
Transfer Type: Export Delimited
Specification Name: <BLANK>
Table Name: Availability
File Name: <File Name>.txt
Has Field Names: No
HTML Table Name:
Code Page

 

[smedvid]

I think you need to create a query and use the Format() to specify the exact number of decimals to dsiplay. I had a similar situation, and that was my solution... hthw,

Steve Medvid
&quot;IT Consultant & Web Master&quot;

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[gillonia]

Ok, thanks.
This gives me the value that I want within the select Query but how do I get this out to a txt file now?

Thnx again for any advice.
IanG

 

[SkipVought]

don't you need a file spec?
[tt]
TransferText -
Transfer Type: Export Delimited
Specification Name: <BLANK>
Table Name: Availability
File Name: <File Name>.txt
Has Field Names: No
HTML Table Name:
Code Page
[/tt]


Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[gillonia]

That seems prefectly reasonable SkipVought, how do I go about setting this up?

The help talks about a schema.ini file but I don't know how to do this.

Any suggestions?
Thnx again, IanG

 

[SkipVought]

I think that you can go about MANUALLY performing the text export and store the file schema in a file.

Then use that filename in your code.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[gillonia]

Excellent, thnx very much for that - that appears to almost do the trick now.

I've just found out that the data is actually in the format: FEB 99.32% IE Within Excel it's a percentage field.

Is there a way to strip out the percentage symbol using the schema.ini file as I think this is what's causing the formating problem.

If I manually remove the % sign as a test then put 'Col2=F2 Double' into the schema.ini it works a treat. So close.....

Thnx, IanG

 

[SkipVought]

It's not a symbol, its a FORMAT

You first stated...

"The first Action takes two fields from a spreadsheet (ie FEB 99.32) and creates a table. The values within this are displaying correctly." my emphasis

Is this true? If so, the problem is in the export schema.



Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[gillonia]

Thnx again Skip,

Ok, the values in the table are displaying as: FEB 99.32%

So although the % symbol is showing as a format within the spreadsheet it's showing as a symbol within the Availability table.


Thnx, IanG

 

[SkipVought]

So what's the value in Availability table in Access?

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[gillonia]

In Access the values are:

FEB 99.32%

IE the % is physically present not a format.

Thnx again, IanG

 

[SkipVought]

is this ONE field?

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[gillonia]

Sorry, not very clear... this is two fields.

 

[SkipVought]

well 99% is .99, no?

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[gillonia]

Hi Skip,

Yeah but it's cutting off the last two digits...

In Excel, the 2nd field is 99.32% (% symbol a format)
In Access Table, 2nd field is 99.32% (% symbol physically present)
In .txt file, 2nd field is 0.99 (losing the 32)

I think I now have a resolution for this - in my schema.ini file I have the 2nd field set as a longchar and it exports as 0.9932 (ie not cutting off after 2 digits):

[availability.txt]
ColNameHeader=False
Format=TabDelimited
MaxScanRows=25
CharacterSet=OEM
Col1=F1 Char Width 255
Col2=F2 LongChar

Just need to do a few more checks that it works elsewhere. Thnx very much for all your assistance. IanG

 

[SkipVought]

"In .txt file, 2nd field is 0.99 (losing the 32)"

That's ALSO a formatting issue. The ENTIRE value is still there!

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[gillonia]

Hi Skip,

Sorry, but gonna have to disagree with you on that one. In the .txt file the values are not there. I use the values from the text file to import into an Oracle database and the values are definitely not there.

I can't see how you'd format 0.99 in say notepad to show as 0.9932??

Anyway, thnx again for everything.
Cheers, IanG

 

[SkipVought]

I meant that in EXCEL, the values are there.

if you extract 4 places you ought to get 4 places

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}