Create Pivot Table Directly from CSV File?

F

financeguy123

New Member
Joined
Feb 28, 2007
Messages
2
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!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
Welcome to the Board!

If you want to use a datasource, you must first create the datasource for the csv.
Go to the Windows Start Menu and Select Run.
Type "odbcad32" and press enter.
On the "User DSN" tab select "Add"
Then select "Microsoft Text Driver"
Give it a name and Description (Does not matter what)
Uncheck "Use Current Directory"
Click "Select Directory"
Change the directory to the one that has your CSV.
Click OK
Click OK again.
Then run the wizard and just select the datasource you just made.
 
Upvote 0
Works but with some qualifications

Thanks for the tip. It works as you say but the only problem that remains is that the data types are based upon whatever Microsoft decides. For example, I have one field that contains numbers at the top of the dataset but has letters later, the import assumes that everything is a number based upon the first few observations and makes the letters unavailable in the pivot.

I think it's kind of like importing data into Access and letting Access decide the data types. As long as everything is straight forward, it works fine. In my case, I'm still having problems. If you have a work around that you know of, let me know.

I definitely appreciate your help! Thanks a lot!
 
Upvote 0
Re: Works but with some qualifications

Here is how I did to add a pivot table from an external csv file without importing the entire csv file:
- Open the csv file with excel
- Create the Pivot table
- Select, copy and paste the pivot table from the csv file in your actual excel file
- Close without saving the csv file.

That works fine enough for me.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Getting Power Query data to an Excel worksheet
Replies
4
Views
189
MichaelMorris
M
A
  • Solved
Pivot Table Slicers mystery!!!
Replies
9
Views
840
adambc
A
K
  • Solved
VBA to copy column/data from two .csv files to one combined file.
Replies
6
Views
505
kbilly70
K
J
  • Question
Filter Pivot Table Generated From PowerQuery Data Model
Replies
1
Views
91
jdorby
J
H
  • Question
Macro to move csv files within-subfolder to folder "Old Templates"
Replies
2
Views
374
howard
H

Forum statistics

Threads
1,214,920
Messages
6,122,279
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top