[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 210
  • Last Modified:

CSV files - Import in to Excel

Guys, having a couple of problems opening a CSV file in Excel for the following value;

1-1

the value actually means "one to one"  and not "1 minus 1" and when opening, Excel translates the value to 01-Jan

I actually want the vale 1-1 to appear in the cell.

I thought that using text qualifiers would help, e.g. in the csv file, the value is "1-1" but Excel still treats this as a date value and converts to 01-Jan.

Any thoughts on how I could change way Excel acts?
0
Runrigger
Asked:
Runrigger
  • 3
  • 3
  • 2
  • +2
4 Solutions
 
BadotzCommented:
Set the cell type to text when you import, then make sure the column type in the sheet is also text.
0
 
akajohnCommented:
In the Text Import Wizard, step 3, Chose Text as the type, not "General".
Just tried it and it works.

A>
0
 
Chris MillardCommented:
Change the file extension from csv to txt. Then open Excel and select File->Open. Select the txt file.

Here's my test.txt file
01-Jan,1-1,02-Feb,2-2

Open in new window


When it opens (this is from Excel 2010), the text import wizard opens. In option 1, I select "Delimited". In option 2, I select the comma as the delimeter. In option 3, I highlight the column with 1-1 in it and change the column data format to Text, and I do the same for the column with 2-2 in it.

The file then opens with the correct values in the cells.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
philip m o'brienCommented:
It sounds like you are opening the file using Excel, rather than importing the data to Excel.

Because the CSV file contains no formatting Excel makes it's own assumptions about the fields as it imports them. As Badotz states, you need to to change the column type.

Open Excel, CLick on Data>Import External Data>Import Data
Select Text files as the format, then browse to and open your file
Now select Delimited as type, press Next
Check "comma" as the delimiter and press Next
Now change the column format as above.
Regards
0
 
Chris MillardCommented:
If it's Excel 2010 you don't have an import function - you have to choose Open.
0
 
BadotzCommented:
0
 
Chris MillardCommented:
Sorry what I was trying to say is that there isn't a File->Import function as in earlier version of Excel. I should have been a bit clearer.
0
 
akajohnCommented:
For clarification on the Data Ribbon, the "From Text" button is what you need t oimport csv using the wizard.

A>
0
 
RunriggerAuthor Commented:
Guys, thank you all. Import was the way to go.

Majority of points to SubversiveArmadillo as that was the most comprehensive solution
0
 
BadotzCommented:
No worries - glad to help.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now