BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

Hi
I have a 3 million records dataset that i want to export to excel csv. I was told csv can have way more than a 1M records but after exporting it i only see 1,048k
You guys know a way of doing this?
I need the 3M records in one tab so business partners would be able to do pivot tables out of that file. Any suggestions pls?

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

CSV is a comma separated values file, and Excel can read a file in that format.

 

CSV can handle unlimited records but Excel can only read 1 million records. You need a different workaround. Consider exporting to an Access DB and then connecting your Excel workbook to the Access DB. If you need to email the file that's not going to be workable. 

Otherwise Excel will not be a good solution, especially if you can't split your file into multiple sheets. 

View solution in original post

7 REPLIES 7
Reeza
Super User

CSV is a comma separated values file, and Excel can read a file in that format.

 

CSV can handle unlimited records but Excel can only read 1 million records. You need a different workaround. Consider exporting to an Access DB and then connecting your Excel workbook to the Access DB. If you need to email the file that's not going to be workable. 

Otherwise Excel will not be a good solution, especially if you can't split your file into multiple sheets. 

Reeza
Super User

If you can get the PowerPivot add in, that does seem to provide a workaround. You'll need to look into that further yourself. 

PGStats
Opal | Level 21

You or your business partners could do the pivot tables directly in MS-Access. 

PG
LinusH
Tourmaline | Level 20
Or keep the data in SAS and use the SAS OLEDB provider/SAS Addin 4 MS Office.
Data never sleeps
Norman21
Lapis Lazuli | Level 10

As already stated, the contraint is built into Excel, not the CSV file.

 

If you want to view the large CSV file, try something other than Excel. Here is a link to a freeware program that might be useful:

 

http://csved.sjfrancke.nl/

 

 

Norman.
SAS 9.4 (TS1M6) X64_10PRO WIN 10.0.17763 Workstation

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9
Thx Reeza thx guys for your inputs but we dont have MS Access at work. Can that be possible? Have everything under MS Office but Access
Reeza
Super User

That's pretty standard, Access is available under a different license and has an additional cost.  

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1926 views
  • 0 likes
  • 5 in conversation