Email:      Pass:    Pass?


Advertisements


Free Excel Forum

Creating Pivot Table From .csv, But Date Formatted As Text

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I have a .csv file with about 400,000 lines. I'm using Excel 2003, so can't open the file in Excel. I need to create a pivot table from the file.

Currently we create an Access database, and then run a query that pulls a summarised version into Excel. This data is a count of policies by date, summarised by scheme ID. We then have to summarise further, so that we have a count of policies by month by scheme ID.

I'd like to simplify the process, and just create a pivot table straight from the .csv file. This works, except that it imports the dates as text, so I can't group them. (If I just open the .csv file in Excel, then it gives me the dates as numbers - but obviously I can't see the whole file.)

Is there a way to pull the dates into the pivot table as dates?


Similar Excel Video Tutorials

Helpful Excel Macros

Create a 12 Month Calendar With The Current Day Highlighted in Excel
- Create a 12 month formatted calendar on a new tab in Excel with the current day highlighted. This macro creates a nicel
Open any Program from Excel
- This free excel macro allows you to open any program on your computer from excel. You can open a media player, file vie
Basic Web Query in Excel - Import Data from the Web into Excel
- Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
Open a PowerPoint Presentation from Excel
- This free macro for Microsoft Excel allows you to open any PowerPoint presentation from excel. You can change the file
Prevent Saving a Workbook under a Different File Name
- This Excel macro prevents the user from saving an excel file or workbook under a different file name. The file can only

Similar Topics







I've tried (to a maddening end) to figure out how to do this. According to
the Excel help, once I've created a pivot table, all I need to do is select
the column of dates, select group, and then a sub window will open up to
allow me to subdivide by week, month, etc. However, despite every and any
known attempt to alter the structure of the dates, I can never get the option
to appear and all that Excel (using 2003) will do is put it in a generic
Group1.

The file I'm working with is a CSV output from an SQL table. Excel opens it
without any problem, and easily recognizes the dates and offers all the
formatting options, save the pivot table recognizing this as a date for
grouping. I've even tried exporting to Access and reimporting as text, as
excel.

Does anyone know format the pivot table is looking for. Is there some sort
of conversion I need to do? None of the excel date conversion functions work
because the dates are already (it seems) in excel format.



I am using the following code in a SQL query in MS Query to feed an Excel pivot table:

isnull(convert(varchar,mydate,101),'12/31/2099'))

The result works and does return data in the form MM/DD/YYYY, but the date type is 'general' and not 'date', so I can't do any grouping on it or anything else that a date field would allow me to do.

I can use the query to import the data to a table in aworksheet. Then, I have an opportunity to convert the data from text to dates (by doing a find-replace all of '/' to '/'). I can then create a Pivot Table from that table and the columns then act as dates.

However, if I go to a create pivot table directly (and skip the loading of all the data in a table on a worksheet), I don't have the opportunity to do the conversion and I can't do the grouping I want to do. Pivot tables don't let you change the underlying data, so I can't do the conversion.

I'd like to avoid having to do the intermediate step of loading the worksheet table - becuase other users will be using this pivot table process and it's obviously easier if we keep it as simple as possible.

Does anyone have any thoughts on what I can do to make sure the data in the pivot table is of type 'date'?

The underlying database is based on SQL Server 2005, and can use either Excel 2003 or 2007.

Thanks.


I create pivot tables in Office 2003 all the time. Typically, I create an Excel file with two sheets:

1 contains a database
1 contains a pivot table based on the database data

Several colleagues of mine recently switched to Office 2010. They cannot open ANY files that I create that contains a pivot table -- they get a message indicating that the file is corrupt. These files open fine on any other machine using Office 2003. If I copy|paste values so that the pivot table is replaced with just numbers and characters, then the file can be opened in Office 2010.

Any help appreciated.


I have a file that has a large pivot table in it. The data source for the pivot table was in another excel file on our network drive. The file with the pivot table was huge ~ 120 Mbs. Upon opening the file today, the pivot table is gone and the data is just there like someone did a copy/paste values over it. There is no way anyone could access the file. In addition, the file is still the same large size without the pivot table. There are now no links in the file either. We did recently upgrade from 2003 to 2007. The original file was created in 2003. I know this is a strange case, but has anyone come across anything like this? Thanks in advance.


Hi,

Is there a way to have VBA create a new date field in a pivot table that uses the eomonth function?

I created a pivot table using vba based on a dynamic database where most of the dates are the first of the month...but not all. If they were all the first, then the pivot table would generate results grouped by month which is what I want. But there are always a few records that are not the first of the month that messes everything up.

The twist is that the pivot table results have to have the date as a value because formulas are using it (using the pivot table's group date feature converts the date to text).

Thanks,

Lawrence


I am using the following snipet in a SQL query in MS Query to feed an Excel pivot table:

isnull(convert(varchar,mydate,101),'12/31/2099'))

The result works to an extent, because basically the column values returned are text and not dates.

If I use the query to import the data to a worksheet, I have an opportunity to convert the data from text to dates by doing a find-replace all of '/' to '/'. I can then use those columns in a resulting pivot table as dates and can group into such items as quarters, months, etc.

However, if I go to a pivot table directly, I don't have the opportunity to do the conversion and I can't do the grouping I want to do. Pivot tables don't let you change the underlying data, so you can do the conversion.

Does anyone have any thoughts on what I might do if I choose to go directly to a pivot table?

The underlying database is based on SQL Server 2005, and I'm using Excel 2003. If Office 2007 is a solution, I can go to that if I must.

Thanks.


Good Afternoon All,

This is a two part question, hopefully its an easy one, but for the life of me I cant figure out why Excel does this.

Part 1:
Sometimes when I create a pivot table and use dates as the column or row labels, Excel does not recognize the field as dates and will not allow to filter by date; Month, date range, ect... but sometimes it will, anyone know why?

Part 2:
On a pivot table, if u do use dates in the Column or Row labels, is there any way to automatically display the dates month by month? Only way I have been able to do this is to manually group the dates and name each month, and that sucks. And it does not translate well if you have to change anything in the pivot table.

Thanks


I have created a report which shows data in a list format. The data comes from our ERP system and is taken into Excel via an add-in program called Jet Reports.
For each date and person in the table there is a site visit.
I would like to create a table which shows this data summarised by person, and by date. So e.g. a list of people down the left hand column and a row of the relevant dates along the top.
I am aware of pivot tables and use them to summarise numerical data which they do very well. However, this is different, as this is text data.

The dates will change as we go through the year, so I need a dynamic table which uses the dates given, or an entered date range, and which shows the people in the report, who can also change.
Is there a way in which my data can be summarised - the data itself is all there, it's just a matter of arranging it so that it shows in a sensible format - by date and person.


Hi,
I have a table of data with one column representaing dates and another amounts. I then want to create a pivot table using these data which is no problem. The tricky part is however that my dates are not continous.. ie: the table might look like:

11/05/07 $10
11/05/07 $15
12/05/07 $10
14/05/07 $5
14/05/07 $10

The pivot table will then give me:
11/05/07 $25
12/05/07 $10
14/05/07 $15

But I want it to give me:
11/05/07 $25
12/05/07 $10
13/05/07 $0
14/05/07 $15

ie: I want the pivot tables to display dates that does not exist in the list and give these dates a value of 0. Is this possible? either by settings or VBA?

Thank you for taking your time to read this and I hope I've given an understandable explanation.

btw, im using excel 2003


Office Version:Office 2007Operating System:Windows Vista



I have a pivot table based off an msaccess table. I want to be able to send this workbook to other who do not have access to the original msaccess database. I THOUGHT that Excel stored the data internally once you refresh the pivot table. However when I save the file to a flash drive and try to open it on another computer, the pivot table is gone and I just get text and numbers where it used to be. I can't paste all of the data into the Excel file as it would become too large to send. How can I make this workbook portable and retain the pivot table?

I just learned that you can take data with specific dates and create a pivot table that consolidates the dates by month (select 'Group' from the right-click pivot table menu). However, many times when I try to do this, I get a message that says 'Cannot group that selection.' Other times it works just fine. Any ideas on how to get this to work all the time?


Is it possible to create a pivot table directly from a csv file that is longer than Excel's row limit without Access. My csv contains almost 200,000 records so importing over several worksheets would defeat the purpose of avoiding Access. The pivot table feature obviously has the ability to handle records that exceeds Excel's ability to import thanks to MS Query. One would think that querying a csv file would be a piece of cake.

I can go to the pivot table wizard and choose external data and click Get Data. Why not have the ability to pull in a csv there along with dbf, Access and the rest? It has the option of "New Data Source" but I'm at a loss on how to proceed from there.

Any ideas?

Thanks!


Hi all,
I am trying to construct a pivot table from an excel database ive made. Ive added my fields the way i want it setup and by default my data is summarised by count. This shows up as 1 in the data field. When I try and change the 'summarise data by' to sum it comes up as 0, and when i try to summarise by average i get #DIV/0! and i dont know how to get my data (most of which i formatted h:mm:ss) to show up. Am I doing something wrong with my pivot table or is anyone able to help me get my data to show up? Im very new with pivot tables and have been thrown in the deep end at work, so any help is greatly appreciated.
Ill add a link to a screen shot of my pivot table if that helps.

Cheers

http://i53.tinypic.com/16banpv.jpg


Issue:

I have several large CSV files I need to work with. File source is Oracle (10g) Query Exports to Comma Separated Values data file.
I need Vlookup capability and Pivot Table functionality when working with these files. I receive new data files each business day.

Rather than open each file, convert to Excel 2007 xlsx format, set up pivot tables and add VLookups each time I get a new file (daily), I want to create a pivot table that connects to the raw CSV file via Data Connections. When I try to connect via Excel 2007 Data Connections I am receiving a message that I can't create a pivot table using data in a csv format.

Is this correct? Or am I missing an Add-In? Any suggestions on how to do this?

Attached is a sample data file (limited to the first 100 records).

Thanks
joanna.


Here the Issue with no answer so far:

I have several large CSV files I need to work with. File source is Oracle (10g) Query Exports to Comma Separated Values data file.
I need Vlookup capability and Pivot Table functionality when working with these files. I receive new data files each business day.

Rather than open each file, convert to Excel 2007 xlsx format, set up pivot tables and add VLookups each time I get a new file (daily), I want to create a pivot table that connects to the raw CSV file via Data Connections. When I try to connect via Excel 2007 Data Connections I am receiving a message that I can't create a pivot table using data in a csv format.

Is this correct? Or am I missing an Add-In? Any suggestions on how to do this?

Attached is a sample data file (limited to the first 100 records) - however, the data is in xls format which makes the operation possible.
It doesn't work with the csv file.
the exact message I get reads:
The type of connection selected cannot be used to create a Pivot Table.

Thanks for your insight.
joanna.


Hi, I have a problem that I see others have, but I couldn't find the exact answer in the forums I am looking for.

I have a simple report (table) from an ecommerce database that shows order values by date. Order dates are shown, in chronological order, from '20th Jun 2008' etc. Column of dates is headed 'Orders'

I wanted to create a pivot table but found that the dates wouldn't sort as dates, only as alpha. The cell formatting on the original table says they are dates but when I check the values by numbers, they don't change to numbers.

I tried some tips, eg running the dates thro Text to Columns, but this hasn't worked.

Does anyone have any smart ideas for me?

Thanks


I have an excel pivot table with a connection to an access database that tracks my employees vacation dates. I am trying to set up the pivot table so it will show me the begining and end dates of the vacation attached to each employee in a chart. I have been able to get the pivot chart to have the correct fields in the axis', but whenever I drag the begin or end dates to the pivot table, it automatically counts the records instead of looking at the date itself. Does anyone know how I could get it to stop counting the records and pull the dates themselves into the chart?


Hello I have a excel spreadsheet that I need to change into a pivot table.
The dates are listed in the date column daily. (1/1/01= $10 , 1/2/01= $
10,1/3/01= 10) I want the pivot table to group the dates and give me the sums
of a specific month. Jan 01 = $30. The reason I need to do this is because
the pivot table is also stating that I have too many columns and it won’t
show all of my data at one time.



Hey - I'm doing some Save As stuff with a file that has a pivot table (using 2003). The pivot table, which is on one worksheet, is using data from a query that is on another worksheet in the file. I take the original file and "save as" the new file. Then, the next time I bring up the "saved as" file, I get a can't open error, the file then opens and I get a message that say's the pivot table has been eliminated, and as it says, thought the formatting is as if it was still there, I cannot refresh the pivot table, because it really is "gone."

Any ideas?


I have an excel list with dates from 7/7/09-12/31/10. I would like to create a second column to identify the date by its quarter (i.e. 2009Q4, 2010Q1, 2010Q2, 2010Q3 and 2010Q4) and create a pivot table off of this. (See attached file).
What formula can I use to accomplish this?

Thanks,
Nathan

PS creating a pivot table with whjhat I have I am able to group them by year, quarter and month but they are all in 3 seaparate columns, when I need it in one column so I can create a chart


Hi All

I have a big problem and i hope that you could give me a solution.

I have a large data from Oracle Database, I want to make a pivot table for this data. I did create the pivot table based on OLAP cube.

OLAP Cube Pivot tables create needs two files to run, the query from which you retrieved the recordset, and the (.cub) file that contains the offline data.

When i wanted to burn this Pivot table on a CD, I found that you should determine where is the related (.cub) file.

I thought that I can determine the relative batch of the cub file, but that didn't work, you have to determine the absolute path , and that means you can't use this pivot table on a CD .

Any Help will be appreciated
Thank you All
Jotnarta


I use Excel 2010. Everything that I do involves a pivot table. I've been using them for years.

I've never had so many crashes as I have since using Excel 2010. I was modifying my raw data table today which is within the excel file as a table. I added 2 columns to the far right. I then went to my first pivot table in the workbook to change the range and my spreadsheet immediately crashed,,,,,white screen with the message Excel is trying to recover your file. This "recover" my file can take up to an hour and the end result is that my pivot table is no longer a pivot table.

I don't know where to look to figure out why my file has become so unstable that just changing the range makes it crash.

Any ideas of where to look? I don't use any macros or anything beyond having a table of data that I create a few different pivot tables for reports.


QUESTION: Is there a way to get rid of the extra dates in a pivot table table field (the ones I put a check mark next to to populate the pivot table), especially when the data no longer exists?

INFO TO HELP FIGURE OUT WHAT I AM WRITING ABOUT: I have a large workbook containing multiple pivot tables. This file was created years ago and has been maintained by different users over the years (now my turn).

The file looks at data that is entered for the current month and the past two months. (Old raw data is deleted as new is entered).

When I click on a pivot table "Month" field to "check" the months currently use (as of today: Aug-10, Jul-10, and Jun-10), there are about one hundred entries I have to scroll through before I find the three months to check off, even though the data no longer exists. To make matters worse, another user had entered dates in the month field and there as many dates mixed in.

For instance, the month field in one of my pivots has:

1/10/2009
2/10/2009
3/10/2009
4/10/2009
5/10/2009
6/10/2009
7/8/2009 (a date value not even supposed to be in field)
7/8/2009 (a date value not even supposed to be in field)
7/8/2009 (a date value not even supposed to be in field)
7/8/2009 (a date value not even supposed to be in field)
7/8/2009 (a date value not even supposed to be in field)
7/9/2009 (a date value not even supposed to be in field)
7/9/2009 (a date value not even supposed to be in field)
7/10/2009
8/10/2009
8/3/2009 (a date value not even supposed to be in field)
8/3/2009 (a date value not even supposed to be in field)
8/3/2009 (a date value not even supposed to be in field)
8/3/2009 (a date value not even supposed to be in field)
9/10/2009... all the way to the current 6/10/2010, 7/10/2010, and 8/10/2010 entries which I want to check. All the other dates are not needed and create extra work for me.

NOTE: The m/d/yyyy value (8/10/2010) is formatted to the mmm-yy value in the pivot table and corresponding chart.

I've tried creating a macro to clear the pivotcache, but nothing was deleted.

Any help on this is appreciated.

Thank you,

Lidsavr


I'm using an Access table with around 220K lines to create a pivot table that should end up with around 15K rows and 16 columns total.

I have created pivot tables like this before, in fact I create one every month and haven't had any problems that I can't solve, but this one has me stumped.

It will retrieve the data, update the fields, and get about halfway through creating the pivot table. Then it just stops. All dialogue boxes go away, I don't get any warnings or errors, and no pivot table appears. I'm left with a worksheet that is as blank as a new one.

I'm at a bit of a loss. I recreated the source data file and put in only half the data and I get the same result. I can create small pivot tables from a 3x3 set of random data, but I'm lost without so much as an error message.

Could this be a memory problem? I'm trying to convince IT to get me set up with 2007 instead of Excel 2003.


I'm trying to use the "external data source" option in creating a pivot table in order to combine related table information from different tables into a pivot. However, when I choose the .xlsx file I want to use I get a message that says "OBDC Excel Driver Login Failed ... External table is not in the expected format." I can choose an older excel file (.xls), but it isn't letting me choose a new version (.xlsx); and I need to because the data source has too many rows for the older version.

I tried also accessing the file directly in the Microsoft Query and again received the same message as above. The file has five tabs/tables that I would like to create a pivot from using defined relationships (I'd prefer to stay out of Access as I'm not as versed there).

How can I use .xlsx files in this way or is there another option for combining excel tables in a pivot where I use table relationships (i.e. Query).

I appreciate your help on this.