• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

data type in excel file needs to be Number for Pivot table


Hi,
I have an excel file that has pivot tables in it that depend on a columns data type to be numbers in order to update automatically.

The data is dumped into the file automatically from sql server 2005 via a linked server to oracle...(the source data is on an oracle database) but it is interpreted as text (see picture)...I have formatted the cells as number but I suppose the data type that is being dumped to the file is text....anyway how can I ensure the data is of Number type so that the table can update?

thanks pivotProb
0
blossompark
Asked:
blossompark
  • 3
  • 3
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

several ways:

- copy an empty cell, click the colulmn and do a Paste Special - Add

- Select the column and do a Data > Text to Columns > Finish

Either could be automated with a a macro. If the data is always in the same column, just fire up the macro recorder while you perform one of the above and then run the macro on new data.

cheers, teylyn
0
 
blossomparkAuthor Commented:
Hi teylyn,
thanks for your comments.
tried
copy an empty cell, click the colulmn and do a Paste Special - Add
which ended with the same reults

Select the column and do a Data > Text to Columns > Finish
this wouldn't let me proceed because the fields are initially empty,
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
If the cells are empty, you cannot perform an operation on them. The text values need to be in the cells for any of the above to work.

As a workaround, you could add another column with a formula. If AGEDAYS is in column B, then enter in column C

=B1+0

copy down as far as required, then let the Pivot Table refer to column C instead. This way, a dynamic data link will not require any manual intervention, as long as you copy the formula in column C down as many rows as you anticipate you will need.

cheers, teylyn
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
AngelgeoCommented:
select recods. click on warning "!" and convert to number
excel.png
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
@Angelgeo, this also requires the values to be actually present in the cells. As with the methods in the first post, this needs to be repeated manually and does not apply dynamically to linked content.
0
 
blossomparkAuthor Commented:
Hi teylyn,
                   going to try your suggestion, thanks and will update you...
thanks angelgeo...tried that!!
0
 
blossomparkAuthor Commented:
Hi teylyn, worked perfect!! thank you :-)
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now