Pivot Table W/E Date Grouping using external .CSV
 

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

Pivot Table W/E Date Grouping using external .CSV
 

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.
--
Regards
Roger Govier

"mike" wrote in message
...
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

Pivot Table W/E Date Grouping using external .CSV
 

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" wrote:

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.
--
Regards
Roger Govier

"mike" wrote in message
...
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

Pivot Table W/E Date Grouping using external .CSV
 

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.

--
Regards
Roger Govier

"mike" wrote in message
...
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" wrote:

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.
--
Regards
Roger Govier

"mike" wrote in message
...
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

Pivot Table W/E Date Grouping using external .CSV
 

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 csvSelected the Date columnDataText to
columnsnextNextchose column formatD/M/YFinish

Save the csv

Close the csv



Drag the Date field off the PT

Refresh

Drag field back to Column Area

Right clickGroupGroupMonth



All worked OK.



"Roger Govier" wrote:

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.

--
Regards
Roger Govier

"mike" wrote in message
...
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" wrote:

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.
--
Regards
Roger Govier

"mike" wrote in message
...
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

Pivot Table W/E Date Grouping using external .CSV
 

Hi Mike

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

--
Regards
Roger Govier

"mike" wrote in message
...
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 csvSelected the Date columnDataText to
columnsnextNextchose column formatD/M/YFinish

Save the csv

Close the csv



Drag the Date field off the PT

Refresh

Drag field back to Column Area

Right clickGroupGroupMonth



All worked OK.



"Roger Govier" wrote:

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.

--
Regards
Roger Govier

"mike" wrote in message
...
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" wrote:

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.
--
Regards
Roger Govier

"mike" wrote in message
...
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