Txt file to Pivot Table

[SPhill]

Has anyone got a solution for this, my pivot Table is rounding numbers from a txt file:

Should look like this (from Access table into Piv Table)
PROJCLAS NCNTTWO
UBONSU 1
UBONSU 0.442
UBONSU 1
UBONSU 0.392
UBONSU 1

But looks like this in Piv table (from txt file into PT)
PROJCLAS NCNTTWO
UBONSU 1
UBONSU 0
UBONSU 1
UBONSU 0

This isn't fixed by changing formats 'decimal places' as the Pivot table seems to be dropping the decimals from the .txt file in the import procedure, as in the above queries. I'd like to run it straight from the .txt file so I can remove the Access stage.

Anybody come across this before?

SP

 

[KenWright]

Why do you even have a text file involved? Just use the import external data option in Excel and specify the Access database as the source, opting for a Pivot table analysis.

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[SPhill]

Thanks Ken, currently I'm creating (from SAS) the initial .txt file which is 1GB in size & having to import it into an Access DB and run the Piv Table off a table in the Access DB. I want to get rid of the Access stage and just drive the Piv table straight from the initial raw .txt file, but it's dropping the decimals as above?

SP

 

[SkipVought]

SP,

Are you sure it is truncating? Did you look at the Pivottable Field properties?

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[xlbo]

What function do you have in your VALUEs field ?? SUM / COUNT etc ??



Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[KenWright]

Have you thought about querying the database directly from the Import External data option?, skipping both the Access and text file steps.


Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[SPhill]

Thanks guys, but this (losing decimals) is happening during the initial import phase, the above data is generated from a query in stage 2 of 4 of the external import process. What I need is a way of setting the Piv table to 'keep' the decimals during the import procedure from a txt file.

It doesn't have to be a .txt file it can be a .csv file etc etc. I'm using windows NT, MS Office97 prods.

SP

 

[SPhill]

Update....I've tried this with a smaller .txt file and it works fine, so the conclusion is you can only drive a pivot table from an external .txt file as long as the text file is below a certain size. The next question is does anyone know what the limits are as the original .txt file which didn't work (dropped decimals) is 0.8gb whereas it works with a 0.06gb .txt file of the same data just restricted records.

SP

 

[SPhill]

Solved, in case anyone else has this problem:

Cause was first few thousand lines of my .txt file did not have decimal places in the particular field I was looking at (showing just 1 instead of 1.00). Even though lines from a few thousand onwards had decimals the Pivot Table must be just looking at the first line and then using it to allocate attributes for the whole file. Cured by making sure my text file showed decimals from first line onwards (after titles). It seems to work fine now.

SP

 

[xlbo]

Excellent news and with the further info, it does make sense - most applications look at the 1st 10 to 100 lines for each field to try and determine the field type so having integers in the 1st few rows would certainly cause what you have seen

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question