Difference between revisions of "Open CSV in Excel"

From IMSMA Wiki
Jump to: navigation, search
(Created page with "{{Warning | When you open the CSV file in Excel, Excel will automatically interpret data like '''1-10''' as '''10-Jan'''. Therefore check carefully the result in Excel before...")
 
 
(3 intermediate revisions by one other user not shown)
Line 1: Line 1:
−
{{Warning | When you open the CSV file in Excel, Excel will automatically interpret data like '''1-10''' as '''10-Jan'''.
+
{{Warning | When you open the CSV file in Excel, Excel will automatically interpret data like '''1-10''' as '''10-Jan'''. Therefore check carefully the result in Excel before sharing the export result with others.}}
−
Therefore check carefully the result in Excel before sharing the export result with others. This page describes a word around for this Excel behaviour.}}
 
  
−
Export
+
{|
−
Open a new Excel file
+
|- valign="top"
−
Open the .CSV file with a text editor e.g. Notepad++
+
|
−
Copy
+
{| class="wikitable"
−
Select cell A1
+
|-
 +
| [[Image:Csv Excel correct data type.png|250px|center]]
 +
| [[Image:Csv Excel changed to date.png|250px|center]]
 +
| [[Image:Csv Excel changed to date2.png|250px|center]]
 +
|- align="center"
 +
| ''How it should be''
 +
| ''Excel transforms to Date''
 +
| ''Result if you change the column to Text''
 +
|}
  
−
Method 1
+
Below we describe a workaround for this '''Excel''' behaviour.
−
Click on Paste button
+
<ol>
−
Select ''Use Text Import Wizard"
+
<li>[[Export Search Result]].</li>
 +
<li>Open a new Excel file.</li>
 +
<li>Open the CSV file with a text editor e.g. Notepad++.</li>
 +
[[Image:Csv copy text.png|500px|center]]
 +
<div align="center">
 +
'' Copy '''all''' text''
 +
</div>
 +
<li>Copy all rows with Ctrl+A.</li>
 +
<li>Switch back to Excel and click in cell A1.</li>
  
−
Method 2
+
<li>Open the Text import wizard by one of following methods:
−
Paste into Excel
+
[[Image:Csv paste method.png|175px|center]]
−
Select column A
+
<div align="center">
−
Use function ''Text to columns'' on Data ribbon.
+
'' Paste method''
 +
</div>
 +
: '''Paste method'''
 +
<ol type="1">
 +
<li>Click on lower part of the Paste button.</li>
 +
<li>Select ''Use Text Import Wizard'' in the drop-down menu.</li>
 +
</ol>
 +
: '''Text to Columns method'''
 +
<ol type="1">
 +
<li>Paste into Excel with Ctrl+V.</li>
 +
<li>Select column A.</li>
 +
[[Image:Csv text to columns method.png|500px|center]]
 +
<div align="center">
 +
'' Text to Columns method''
 +
</div>
 +
<li>Use function ''Text to columns'' on Data ribbon.</li>
 +
</ol>
 +
<li>The wizard starts.</li>
 +
[[Image:Csv wizard1.png|500px|center]]
 +
<div align="center">
 +
'' The first panel''
 +
</div>
 +
<li>Click '''Next'''.</li>
 +
[[Image:Csv wizard2.png|500px|center]]
 +
<div align="center">
 +
'' The second panel''
 +
</div>
 +
<li>Change ''Delimiters'' to '''Comma'''.</li>
 +
<li>Click '''Next'''.</li>
 +
[[Image:Csv wizard3.png|500px|center]]
 +
<div align="center">
 +
'' The third panel''
 +
</div>
 +
<li>Change ''Column data format'' to '''Text''' and verify that the format is set to '''Text''' for the columns that Excel otherwise will change to the wrong data type or data format.</li>
 +
<li>Click '''Finish'''.</li>
 +
[[Image:Csv Excel correct data type.png|250px|center]]
 +
<li>Save the file in xlsx.</li>
 +
<li>Close the Excel file and open the file again before you share it in order to verify that the data still has the correct format.</li>
 +
</ol>
  
−
Same steps
 
−
 
−
The wizard starts
 
−
Click '''Next'''
 
−
Change ''Delimiters'' to '''Comma'''
 
−
Click '''Next'''
 
−
Change ''Column data format'' to ''Text'' and verify that the format is set
 
−
Click '''Finish'''
 
−
Save as xlsx
 
−
Close and open the file in order to verify that the data still has the correct format
 
 
{{NavBox Technical Notes}}
 
{{NavBox Technical Notes}}
 
[[Category:NAA]]
 
[[Category:NAA]]

Latest revision as of 12:02, 7 April 2015

Warning.jpg When you open the CSV file in Excel, Excel will automatically interpret data like 1-10 as 10-Jan. Therefore check carefully the result in Excel before sharing the export result with others.
Csv Excel correct data type.png
Csv Excel changed to date.png
Csv Excel changed to date2.png
How it should be Excel transforms to Date Result if you change the column to Text

Below we describe a workaround for this Excel behaviour.

  1. Export Search Result.
  2. Open a new Excel file.
  3. Open the CSV file with a text editor e.g. Notepad++.
    4. Csv copy text.png

    Copy all text

  4. Copy all rows with Ctrl+A.
  5. Switch back to Excel and click in cell A1.
  6. Open the Text import wizard by one of following methods:
    Csv paste method.png

    Paste method

    Paste method
    1. Click on lower part of the Paste button.
    2. Select Use Text Import Wizard in the drop-down menu.
    Text to Columns method
    1. Paste into Excel with Ctrl+V.
    2. Select column A.
      3. Csv text to columns method.png

      Text to Columns method

    3. Use function Text to columns on Data ribbon.
  7. The wizard starts.
    8. Csv wizard1.png

    The first panel

  8. Click Next.
    9. Csv wizard2.png

    The second panel

  9. Change Delimiters to Comma.
  10. Click Next.
    11. Csv wizard3.png

    The third panel

  11. Change Column data format to Text and verify that the format is set to Text for the columns that Excel otherwise will change to the wrong data type or data format.
  12. Click Finish.
    13. Csv Excel correct data type.png
  13. Save the file in xlsx.
  14. Close the Excel file and open the file again before you share it in order to verify that the data still has the correct format.
Technical Notes
FAQ
A - E
F - P
Q - Z
Known issues
Workarounds
Misc
Languages
Countries
Retrieved from "http://mwiki.gichd.org:8090/mediawiki/index.php?title=Open_CSV_in_Excel&oldid=19986"