What does CSV stand for?

Sometimes the word ‘CSV’ might come up when discussing about Excel data.

‘CSV’ is an abbreviation for Comma Separated Value and are values separated by commas. In other words, it is a file created with each chunk of information separated with ‘, (commas)’.

This CSV file serves as an intermediary between applications not only for Excel but for others.

 

How to create a CSV file

Before anything, let’s try to create a CSV file.

There are a few ways in creating a CSV file, but here we will explain with ‘Text Document’ (an application in Windows).

 

Example of use) Create a CSV file with ‘Text Document’

(1) Right click on desktop, select [New]→[Text Document].

 

(2) Open the ‘New Text Document’ created on desktop and input the following:

Name,Address,Tel,Other information
James Taylor,Stratford Rd. Brooklyn New York,fixed(718)777-1123
Tom Bowden,Annette Ave. Lake Forest California,fixed(847)123-4567,Indirect line
Suzy Lawson,N. Heights St. Winters Texas, fixed(325)987-6543

Make sure each category is divided with a ‘, (comma)’.

 

(3) On menu, click [File]→[Save As].

 

(4) When the dialog box appears, input file name “Directory.csv“. Make sure the file ends with ‘.csv’.

 

(5) An Excel-like ‘CSV file’ will be created on desktop.

 

What happens when we open this file?

When we double click the file made, Excel opens and the information appears like below

If we compare this file with the one we input originally, the ‘, (comma)’ is serving as a column.

Like this, data that is divided by a ‘, (comma)’ serving for each column is called a ‘CSV file’.

 

How to save as CSV

We will now see how to save an Excel file as a ‘CSV file’.

 

Example of use) Save as a CSV file

The original table below is in Excel.
We will save this table in CSV form.

 

(1) Click [Save As] and click on [Save as type] below [File name].

 

(2) When a list appears, select ‘CSV (Comma delimited)’ and Save.

 

How to open a CSV file with Excel

Normally, a CSV file appears on desktop like the screenshot below and we can open the file with Excel just by double clicking it.

 

However, the icon display might differ based on the version of the computer, and in some cases it will not automatically open with Excel.

 

In this case, we open a blank sheet of Excel and “import” the CSV data.

 

Example 1) Open a CSV file with Excel

To open a file saved in a different form in Excel is called ‘importing‘.

Now we will import the ‘Directory.CSV’ data to Excel.

 

(1) Follow the steps to import ‘foreign data’ to Excel.

① Click on [Data] tab.
② Click on [From Text/CSV] from the category ‘Get & Transform Data’ on ribbon.

 

(2) When the dialog box appears, select file to import.

① Select ‘Directory.csv’.
② Click ‘Import’ on the bottom right.

 

(3) When the ‘Text Import Wizard’ appears, select the following:

① Add check on ‘Delimited – Characters such…’ from the [Original data type] section.
② Click [Next].

 

(4) Continue by clicking on the following:

① This time it is data separated with a comma, so check ‘Comma’ from the [Delimiters] section.
② Click [Finish].

 

The data in the CSV file should have been imported in Excel.

 

Example of use 2) Use Excel as the standard application to open CSV files

If we want to open all CSV files with Excel, there is a way to set that up.
Follow the steps below:

 

(1) Right click on the CSV file, and click on the following:

① Click [Open with].
② On new tab that appears, click [Choose another app].

 

(2) When the dialog box appears, follow the steps below:

① Select ‘Excel’ from ‘Other options’.
② Check ‘Always use this app…’.
③ Click [OK] to complete.

Now that the set up is complete, whenever we open any CSV files it will open with Excel.

 

Open CSV files with WordPad

If we want to open CSV files with WordPad instead of Excel, we can change the options in the same way as the steps we carried out for setting up Excel as the standard application. We can freely change the standard application of opening CSV files anytime.

 

What does TSV stand for?

Like CSV files, there is another form of saving data called ‘TSV’.
While CSV separates information with ‘, (commas)’, TSV separates information with ‘tabs’. TSV stands for Tab Separated Values.

 

Example of a TSV file) 

 

Advantages of TSV)

It seems similar to CSV, but as explained earlier, tab spaces separate information.
This could be useful when there is information that include ‘, (commas)’ and ‘ ” (double quotations)’, like ‘$1,000’.
Therefore, it is best to use them depending on what kind/type of information we have.

 

How to create a TSV file

Example of use) Create a TSV file with ‘Text Document’

The steps are similar to that of creating CSV files.
Use the ‘Tab’ key to separate information, and save the finished product with a file name ending with ‘.tsv’.

 

POINT!

It could be difficult to distinguish a TSV file with a text document file.
In this case, we can change the Windows setting so that it displays the file type extension (text that appears in the end of a file name, like “csv” and “tsv”).

When the file type extension can not be seen) → TSV file cannot be distinguished

When the file type extension can be seen) → TSV file can be distinguished

 

⇒How to set up file type extension)

(1) Select ‘This PC’ from the Windows button. (Select ‘Computer’ from ‘Start’ for previous Windows versions.)

 

(2) When the dialog box appears, follow these steps:

① Click on [File] on upper left.
② Select ‘Change folder and search options’.

 

(3) When a new dialog box opens, change the following:

① Click on [View].
② Remove check mark for ‘Hide extensions for known file types’ from [Advance settings].
③ Click ‘OK’ to complete.

 

Now the file type extensions are displayed and we can distinguish TSV files.

How to save as TSV

Next, we will explain how to save files as TSV. The steps are mostly the same with CSV, but there are some points we must be careful of.

 

Example of use) Save as a TSV file

(1) Select ‘Text (Tab delimited)’ from [Save as type].

 

(2) After file is created, change file type extension from ‘.txt’ to ‘.tsv’.

 

How to open a TSV file with Excel

Similarly to the CSV, we use the ‘importing of foreign data’.

 

Example of use 1) Open a TSV file with Excel

(1) Click [Data]→’From Text/CSV’.

 

(2) When the dialog box appears, click ‘All Files’ from the v-shape. (※The TSV file will not appear if we do not choose ‘All Files’.)

 

(3) Select TSV file and press [Import].

 

Example of use 2) Use Excel as the standard application to open TSV files

We will change the setting in Windows so that every time we click on a TSV file, it will open with Excel.
The steps are the same with that of the CSV, so please check above.

 

Open TSV files with WordPad

Lastly, let’s look at how to open TSV files with WordPad.