Pivot Table W/E Date Grouping using external .CSV

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mike - 04 Mar 2008 09:22 GMT

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

Reply to this Message

Roger Govier - 04 Mar 2008 10:34 GMT

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.

Signature

Regards
Roger Govier

> Hi All,
>
[quoted text clipped - 21 lines]
>
> Mike

Reply to this Message

mike - 04 Mar 2008 12:04 GMT

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??

> Hi Mike
>
[quoted text clipped - 31 lines]
> >
> > Mike

Reply to this Message

Roger Govier - 04 Mar 2008 15:45 GMT

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.

Signature

Regards
Roger Govier

> Hi Roger,
>
[quoted text clipped - 45 lines]
>> >
>> > Mike

Reply to this Message

mike - 05 Mar 2008 10:49 GMT

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.

> Hi Mike
>
[quoted text clipped - 55 lines]
> >> >
> >> > Mike

Reply to this Message

Roger Govier - 05 Mar 2008 16:22 GMT

Hi Mike

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

Signature

Regards
Roger Govier

> SOLUTION:
>
[quoted text clipped - 87 lines]
>> >> >
>> >> > Mike

Reply to this Message