Cara menggunakan google sheets csv delimiter

wikiHow adalah suatu "wiki", yang berarti ada banyak artikel kami yang disusun oleh lebih dari satu orang. Untuk membuat artikel ini, penyusun sukarela menyunting dan memperbaiki dari waktu ke waktu.

Table of Contents Show

IMPORTDATA for a CSV or TSV File
IMPORTFEED for an RSS or ATOM Feed
IMPORTHTML for a Table or List on a Web Page
IMPORTRANGE for a Cell Range in a Spreadsheet

Artikel ini telah dilihat 26.019 kali.

Berkas CSV atau “comma separated values”memungkinkan Anda untuk menyimpan data dalam format tabel terstruktur yang berguna ketika Anda perlu mengelola basis data yang besar. Berkas CSV dapat dibuat menggunakan Microsoft Excel, OpenOffice Calc, Google Spreadsheets, dan Notepad.

Buka lembar lajur baru pada Microsoft Excel, OpenOffice Calc, atau Google Spreadsheets.
- Jika ingin mengubah lembar lajur yang sudah Anda ke dalam format CSV, beralihlah ke langkah keempat.
Tikkan setiap judul atau nama kolom pada kotak-kotak di baris 1 yang ada di bagian atas lembar lajur. Sebagai contoh, jika Anda ingin memasukkan data barang yang dijual, tikkan “Nama Barang” pada kotak A1, “Harga Barang” pada kotak B1, “Deskripsi Barang” pada kotak C1, dan seterusnya.
Masukkan data pada lembar lajur di bawah setiap kolom sesuai kebutuhan. Dengan contoh yang dijelaskan di langkah kedua, tuliskan nama barang pada kolom kotak A2, harga barang pada kotak B2, dan deskripsi barang pada kotak C2.
Klik menu “File” dan pilih “Save As” setelah selesai memasukkan semua data pada lembar lajur. Jika Anda menggunakan Google Spreadsheets, opsi yang perlu dipilih adalah “File > Download as”.
Pilih “CSV” dari menu drop-down “Save as type”.
Tikkan nama berkas CSV, kemudian pilih “Save”. Sekarang Anda sudah membuat berkas CSV, dan koma secara otomatis akan ditambahkan ke berkas untuk memisahkan setiap kolom.

Jalankan Notepad dan tikkan nama-nama kolom yang dipisahkan oleh koma pada baris pertama. Sebagai contoh, jika Anda ingin memasukkan data barang yang dijual, tikkan entri berikut pada baris pertama dokumen: “nama,harga,deskripsi.” Perlu diingat bahwa tidak boleh ada spasi di antara setiap nama kolom.
Tikkan data pada baris kedua menggunakan format yang sama dengan format nama kolom pada baris pertama. Dengan contoh yang dijelaskan di langkah pertama, tulis nama barang yang dijual, diikuti dengan harga dan deskripsi barang. Sebagai contoh, jika Anda menjual biskuit, tikkan “biskuit,10.000,camilan.”
Tetap masukkan data untuk setiap produk pada baris-baris berikutnya. Jika ada kolom yang ingin dibiarkan kosong, pastikan Anda tetap memasukkan koma. Jika tidak, kolom-kolom yang lain tidak akan ditampilkan karena satu kolom kosong tersebut.

You may want to work with data in your spreadsheet that resides elsewhere. Using a set of Google Sheets functions, you can import data from a CSV file, RSS feed, web page, or another spreadsheet.
With the functions we’ll describe here, you can pull data into your sheet from external sources. Then, analyze, manipulate, format, and do what you please with your new data.
Table of Contents

IMPORTDATA for a CSV or TSV File
If you see a CSV or TSV file on a website you’d like to import, you can use the IMPORTDATA function.
RELATED: What Is a CSV File, and How Do I Open It?

The syntax for the function is
```
=IMPORTDATA("https://www.bls.gov/cew/classifications/aggregation/agg-level-titles-csv.csv",".")
```
0 where only the first argument is required as the URL or a cell reference. If you want to use a different delimiter than the default file type, use the
```
=IMPORTDATA("https://www.bls.gov/cew/classifications/aggregation/agg-level-titles-csv.csv",".")
```
1 argument. And if you need to change the language, use the
```
=IMPORTDATA("https://www.bls.gov/cew/classifications/aggregation/agg-level-titles-csv.csv",".")
```
2 argument with the region’s code.
Here, we’ll import a CSV file using the URL with this formula:
```
=IMPORTDATA("https://www.bls.gov/cew/classifications/aggregation/agg-level-titles-csv.csv")
```
In this example, we add the
```
=IMPORTDATA("https://www.bls.gov/cew/classifications/aggregation/agg-level-titles-csv.csv",".")
```
1 argument rather than using the default (comma) for the CSV file:
```
=IMPORTDATA("https://www.bls.gov/cew/classifications/aggregation/agg-level-titles-csv.csv",".")
```
IMPORTFEED for an RSS or ATOM Feed
Maybe there’s an RSS or ATOM feed that you want to pull data from to manipulate it in your sheet. You’ll use the IMPORTFEED function.
RELATED: What Is RSS, and How Can I Benefit From Using It?
The syntax for the function is
```
=IMPORTDATA("https://www.bls.gov/cew/classifications/aggregation/agg-level-titles-csv.csv",".")
```
4 where only the first argument is required, and you can use the URL or a cell reference.
- Query: Enter the default “items” or use “feed” for a single row of data, “feed [type]” for a certain feed element, or “items [type]” for a certain item element.
- Headers: The default is FALSE, but you can use TRUE to include a header row.
- Number_items: The default is all items in the feed, but you can enter a specific number of items.
To import our How-To Geek feed with five items, you can use this formula:
```
=IMPORTFEED("https://www.howtogeek.com/feed","items",,5)
```
Using this next formula, you can import five items from the same feed and include the header row:
```
=IMPORTFEED("https://www.howtogeek.com/feed","items",TRUE,5)
```
For one more example, using the same feed, we’ll import just the titles for five items using this formula:
```
=IMPORTFEED("https://www.howtogeek.com/feed","items title",,5)
```
IMPORTHTML for a Table or List on a Web Page
Tables and lists from a web page (HTML) are easy to import into Google Sheets with the IMPORTHTML function.
RELATED: What Is HTML?
The syntax for the function is
```
=IMPORTDATA("https://www.bls.gov/cew/classifications/aggregation/agg-level-titles-csv.csv",".")
```
5 where you may want to use all three arguments depending on the page. Enter a URL or cell for the
```
=IMPORTDATA("https://www.bls.gov/cew/classifications/aggregation/agg-level-titles-csv.csv",".")
```
6, “table” or “list” for the
```
=IMPORTDATA("https://www.bls.gov/cew/classifications/aggregation/agg-level-titles-csv.csv",".")
```
7, and a number for the
```
=IMPORTDATA("https://www.bls.gov/cew/classifications/aggregation/agg-level-titles-csv.csv",".")
```
8. The index is the identifier in the page’s HTML for the table or list if there’s more than one.
As an example, we’ll import the first table on a Wikipedia page for Star Wars films using this formula:
```
=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_Star_Wars_films","table",1)
```
When you view the web page, you can see this first table is the one on the top right.
Because it’s the next table on that page we really want, we’ll include the next index number instead with this formula:
```
=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_Star_Wars_films","table",2)
```
Now we have the table shown below in our Google Sheet instead.

For one more example, we’ll import a list from that same page. This is the third list identified on the page which is the contents of the article. Here’s the formula:
```
=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_Star_Wars_films","list",3)
```
IMPORTRANGE for a Cell Range in a Spreadsheet
One more handy import function is for bringing in data from another spreadsheet. Although it’s easy enough to pull data from a sheet in the same workbook, you might want data from a different workbook. For this, you can use the IMPORTRANGE function.
RELATED: How to Import Data from Another Google Sheet
The syntax for the function is
```
=IMPORTDATA("https://www.bls.gov/cew/classifications/aggregation/agg-level-titles-csv.csv",".")
```
9 where you’ll need both arguments. Enter the URL for the sheet in quotes or use a cell reference. Then, include the sheet name and cell range as a string or a cell reference, both should be in quotation marks.
When you first enter a formula for the IMPORTRANGE function, you’ll likely see an error like the one below. This is simply to alert you that you need to allow access to the sheet you want to import. Select “Allow Access” to continue.
In this example, we’ll import the range A1 through E7 from another workbook. This workbook only has one sheet, so the import is successful without the sheet name. Here’s the formula:
```
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/mysheet/edit","A1:E7")
```
For the next example, we’re importing from another workbook that has multiple sheets. So, you’d include the sheet name and cell range as a single string:
```
=IMPORTFEED("https://www.howtogeek.com/feed","items",,5)
```
0
```
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/mysheet/edit#gid=111525310","Sales!D1:F13")
```
These Google Sheets import functions can come in super handy when you need external data like the types mentioned here. Keep in mind that if you want to import a specific type of file from your computer, such as a Microsoft Excel workbook, you can do that with the Google Sheets menu.