CODEWITHSUNDEEP
excelcsvformatting
scrollout
scrollout

Reputation: 577

How to display raw data from CSV in Excel

When I open a CSV file in Excel, data is formatted into values that aren't useful.

For example, I have a CSV with raw data

id,        studyid, studystartdatetime,      19960333152518,     19960444074329
10219922,  330732,  2020-10-26 07:22:03.000, 13.728111605495413, 0.8199649107182145
10222451,  331814,  2020-11-03 10:28:10.000, 43.369795528728545, 0.8648709765481933

and when I open this in Excel, it shows me

id,        studyid, studystartdatetime,   2E+13,     2E+13
10219922,  330732,  22:03.0,              13.72811,  0.81996
10222451,  331814,  28:10.0,              43.36979,  0.86487

How can I disable any type of formatting that occurs and view the raw data? There is going to be hundreds of columns in the CSVs I am using, so reformatting each column is not an option.

Upvotes: 3

Views: 7407

Answers (2)

Code Novice
Code Novice

Reputation: 2398

I was also needing to be able to manage and work with CSV Data without destroying the format as Excel is so famously known for. I find the the NEW Import Wizards are not conducive to a basic CSV Import - at least I didn't find it useful due to my not having the ability of importing the RAW Data.

Basically I'm using Excel Office 365 version 16 at the time of writing. I had to turn on the LEGACY CSV/Text Import Wizard... I hope Microsoft NEVER gets rid of this wizard without first providing an easier method to import raw data.

Using the below method I have no issues removing rows or columns after first making sure I have imported ALL columns as TEXT... when you save your changes be sure to save it as a CSV file.

Turn On Legacy Wizard:

In Excel navigate to File --> Options --> Data --> Check the box in the 'Show legacy data import wizards' section 'From Text (Legacy)'

Now to import your CSV or RAW Text File

Navigate to the Data Tab and within the 'Get & Transform Data' section choose Get Data --> Legacy Wizards --> From Text (Legacy).

Gif Showing how to turn on Legacy Import: Turn on CSV Legacy Import

Gif Showing how to import CSV Data: Import CSV Data

Hopefully this will help somebody else.

Upvotes: 1

scrollout
scrollout

Reputation: 577

  1. Go to Data -> From Text/CSV
  2. Choose your CSV file from File Explorer
  3. Select Transform Data
  4. Select all cells with Ctrl-A
  5. Click Use First Row as Headers
  6. Click Datatype: Any and select Text and click Replace current, if prompted.
  7. Click Close and Load

Upvotes: 3

Related Questions