Pivot Table W/E Date Grouping using external .CSV

[mike]

Hi All,

I have a .csv file produced by our accounting software Exchequer containing
my data which contains the following:

Department
Invoice Value
Invoice Date

I have linked this data as an external datasource to a pivot table but am
unable to group the "Invoice Date" field. I am assuming this is becuase MS
Query is taking the invoice date field as text and not as an actual date OR
that the header is MS Query is making it not possible for me to groupt the
Invoice Date Data. Before I have only been able to get pivot tables to group
by date if only the Data is selected and not the header.

Please can anyone help?

Many thanks in advance

Mike

 

[Roger Govier]

Hi Mike

There is no inherent reason why the date field cannot be grouped having come
through MSquery.
If the dates in the csv file are genuine dates, AND if there are no blank
date fields, then the PT will group them.

I would try just opening the csv file in Excel, by double clicking on it.
Examine the data and see if there are problems in the date field. If so,
amend them and save back as csv, then your link to the PT should work fine.

 

[mike]

Hi Roger,

Whats the best way to track down where the problem. I have opened the .csv
file in excel and excel can recognise the dates as dates and i can produce a
pivot table grouping on the dates so would you assume that ms query is
looking at some blank data??

 

[Roger Govier]

Hi Mike

Works fine for me on a sample csv file I created.
If the data is not sensitive, mail me your workbook and the csv file and I
will see if I can see what the problem is.

To send direct mail to
roger at technology4u dot co dot uk
Do the obvious with at and dot.

 

[mike]

SOLUTION:

Although the dates in the csv all look OK, they are being stored as a Text
column, hence the PT is “not happyâ€

I opened the csv>Selected the Date column>Data>Text to
columns>next>Next>chose column format>D/M/Y>Finish

Save the csv

Close the csv



Drag the Date field off the PT

Refresh

Drag field back to Column Area

Right click>Group>Group>Month



All worked OK.

 

[Roger Govier]

Hi Mike

glad the solution I posted back to you privately worked out OK.