Difference between revisions of "Open CSV in Excel"
From IMSMA Wiki
(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 | + | <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> | |
| − | |||
| − | |||
| − | |||
| − | |||
| + | <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
 |
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. |
- Export Search Result.
- Open a new Excel file.
- Open the CSV file with a text editor e.g. Notepad++.
- Copy all rows with Ctrl+A.
- Switch back to Excel and click in cell A1.
- Open the Text import wizard by one of following methods:
Paste method
- Paste method
- Click on lower part of the Paste button.
- Select Use Text Import Wizard in the drop-down menu.
- Text to Columns method
- Paste into Excel with Ctrl+V.
- Select column A.
- Use function Text to columns on Data ribbon.
Text to Columns method
Same steps
- The wizard starts
Country
- Click Next
- Change Delimiters to Comma
Country
- Click Next
- Change Column data format to Text and verify that the format is set
Country
- Click Finish
- Save as xlsx
- Close and open the file in order to verify that the data still has the correct format
Copy all text
