Open CSV in Excel - Revision history

Dionysia at 11:02, 7 April 2015

2015-04-07T11:02:00Z

← Older revision		Revision as of 13:02, 7 April 2015
Line 1:		Line 1:
			{{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.}}

	{\|		{\|
	\|- valign="top"		\|- valign="top"
Line 13:		Line 15:
	\|}		\|}

	~~{{Warning \| When you open the CSV file in Excel, Excel will automatically interpret data like '''1-10''' as '''10-Jan'''.~~		Below we describe a workaround for this '''Excel''' behaviour.
	~~Therefore check carefully the result in Excel before sharing the export result with others. This page describes~~ a workaround for this '''Excel''' behaviour.}}

	<ol>		<ol>
	<li>[[Export Search Result]].</li>		<li>[[Export Search Result]].</li>
Line 69:		Line 69:
	<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>		<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>		</ol>

	{{NavBox Technical Notes}}		{{NavBox Technical Notes}}
	[[Category:NAA]]		[[Category:NAA]]

Alnaucler at 19:50, 5 April 2015

2015-04-05T19:50:36Z

← Older revision		Revision as of 21:50, 5 April 2015
Line 1:		Line 1:
			{\|
			\|- valign="top"
			\|
			{\| class="wikitable"
			\|-
			\| [[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''
			\|}

	{{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. 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 workaround for this '''Excel''' behaviour.}}

	<ol>		<ol>
Line 51:		Line 65:
	<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>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>		<li>Click '''Finish'''.</li>
	<li>Save as xlsx.</li>		[[Image:Csv Excel correct data type.png\|250px\|center]]
	<li>Close and open the file in order to verify that the data still has the correct format.</li>		<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>		</ol>
	{{NavBox Technical Notes}}		{{NavBox Technical Notes}}
	[[Category:NAA]]		[[Category:NAA]]

Alnaucler at 12:39, 5 April 2015

2015-04-05T12:39:47Z

← Older revision		Revision as of 14:39, 5 April 2015
Line 33:		Line 33:
	<li>Use function ''Text to columns'' on Data ribbon.</li>		<li>Use function ''Text to columns'' on Data ribbon.</li>
	</ol>		</ol>
	~~Same steps~~		<li>The wizard starts.</li>
			[[Image:Csv wizard1.png\|500px\|center]]
	<li>The wizard starts
	[[Image:.png\|500px\|center]]
	<div align="center">		<div align="center">
	'' ~~Country~~ ''		'' The first panel''
	</div>		</div>
	<li>Click '''Next'''		<li>Click '''Next'''.</li>
	<li>~~Change ''Delimiters'' to '''Comma'''~~		[[Image:Csv wizard2.png\|500px\|center]]
	[[Image:.png\|500px\|center]]
	<div align="center">		<div align="center">
	'' ~~Country~~ ''		'' The second panel''
	</div>		</div>
	<li>~~Click~~ '''~~Next~~'''		<li>Change ''Delimiters'' to '''Comma'''.</li>
	<li>~~Change~~ ''~~Column data format~~'~~' to~~ ''~~Text~~'~~' and verify that the format is set~~		<li>Click '''Next'''.</li>
	[[Image:.png\|500px\|center]]		[[Image:Csv wizard3.png\|500px\|center]]
	<div align="center">		<div align="center">
	'' ~~Country~~ ''		'' The third panel''
	</div>		</div>
	<li>Click '''Finish'''		<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>Save as xlsx		<li>Click '''Finish'''.</li>
	<li>Close and open the file in order to verify that the data still has the correct format		<li>Save as xlsx.</li>
			<li>Close and open the file in order to verify that the data still has the correct format.</li>
	</ol>		</ol>
	{{NavBox Technical Notes}}		{{NavBox Technical Notes}}
	[[Category:NAA]]		[[Category:NAA]]

Alnaucler at 12:31, 5 April 2015

2015-04-05T12:31:35Z

← Older revision		Revision as of 14:31, 5 April 2015
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]]

Alnaucler: 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..."

2015-04-05T12:06:19Z

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..."

New page

{{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. This page describes a word around for this Excel behaviour.}}

Export
Open a new Excel file
Open the .CSV file with a text editor e.g. Notepad++
Copy
Select cell A1

Method 1
Click on Paste button
Select ''Use Text Import Wizard"

Method 2
Paste into Excel
Select column A
Use function ''Text to columns'' on Data ribbon.

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}}
[[Category:NAA]]