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...")
 
Line 2: Line 2:
 
Therefore check carefully the result in Excel before sharing the export result with others. This page describes a word around for this Excel behaviour.}}
 
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
+
<ol>
−
Open a new Excel file
+
<li>[[Export Search Result]].</li>
−
Open the .CSV file with a text editor e.g. Notepad++
+
<li>Open a new Excel file.</li>
−
Copy
+
<li>Open the CSV file with a text editor e.g. Notepad++.</li>
−
Select cell A1
+
[[Image:Csv copy text.png|500px|center]]
−
 
+
<div align="center">
−
Method 1
+
'' Copy '''all''' text''
−
Click on Paste button
+
</div>
−
Select ''Use Text Import Wizard"
+
<li>Copy all rows with Ctrl+A.</li>
−
 
+
<li>Switch back to Excel and click in cell A1.</li>
−
Method 2
 
−
Paste into Excel
 
−
Select column A
 
−
Use function ''Text to columns'' on Data ribbon.
 
  
 +
<li>Open the Text import wizard by one of following methods:
 +
[[Image:Csv paste method.png|175px|center]]
 +
<div align="center">
 +
'' 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>
 
Same steps
 
Same steps
  
−
The wizard starts
+
<li>The wizard starts
−
Click '''Next'''
+
[[Image:.png|500px|center]]
−
Change ''Delimiters'' to '''Comma'''
+
<div align="center">
−
Click '''Next'''
+
'' Country ''
−
Change ''Column data format'' to ''Text'' and verify that the format is set
+
</div>
−
Click '''Finish'''
+
<li>Click '''Next'''
−
Save as xlsx
+
<li>Change ''Delimiters'' to '''Comma'''
−
Close and open the file in order to verify that the data still has the correct format
+
[[Image:.png|500px|center]]
 +
<div align="center">
 +
'' Country ''
 +
</div>
 +
<li>Click '''Next'''
 +
<li>Change ''Column data format'' to ''Text'' and verify that the format is set
 +
[[Image:.png|500px|center]]
 +
<div align="center">
 +
'' Country ''
 +
</div>
 +
<li>Click '''Finish'''
 +
<li>Save as xlsx
 +
<li>Close and open the file in order to verify that the data still has the correct format
 +
</ol>
 
{{NavBox Technical Notes}}
 
{{NavBox Technical Notes}}
 
[[Category:NAA]]
 
[[Category:NAA]]

Revision as of 13:31, 5 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. This page describes a word around 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.

    Same steps

  7. The wizard starts
    500px

    Country

  8. Click Next
  9. Change Delimiters to Comma
    500px

    Country

  10. Click Next
  11. Change Column data format to Text and verify that the format is set
    500px

    Country

  12. Click Finish
  13. Save as xlsx
  14. Close and open the file 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=19964"