Cara menggunakan extract data in excel

Table of Contents Show

Use the IMPORTRANGE function
Langkah mengimpor data File CSV di excel?
Bagaimana cara input data?
Bagaimana cara membuka File CSV?
Bagaimana cara mengubah excel ke CSV?

If you keep data in separate Google Sheets, copy a range of data from one spreadsheet to another with the IMPORTRANGE function.

For example, you may track quarterly sales data for a product in a different spreadsheet for each region. To combine all that quarterly sales data, copy the data from each region's spreadsheet into a single spreadsheet using IMPORTRANGE.

Use the IMPORTRANGE function

In Sheets, open a spreadsheet.
In an empty cell, enter =IMPORTRANGE.
In parenthesis, add the following specifications in quotation marks and separated by a comma*:
The URL of the spreadsheet in Sheets.
The sheet name (optional) and the range of cells to import.
*Note: If you are in a Spanish-speaking country, use the semicolon as a separator instead of the comma.

Press Enter.
Click Allow access to connect the 2 spreadsheets.

For example:

To import cells A1 through C10 from sheet 1 of the abcd123abcd123 spreadsheet, you enter: =IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "sheet1!A1:C10")

Was this helpful?

How can we improve it?

In this tutorial, we will create an automation that extracts a table with today's exchange rates for the Euro against other currencies from the European Central Bank website. We keep historical exchange rate data in an Excel spreadsheet and we want to add this data to it daily. Because the format of the data extracted from the website is different from the one in our Excel spreadsheet, we must also edit the extracted data in Excel to apply the right format to it.

We will create a project and start by adding a Use Excel File activity to indicate the Excel file to use. We will then click Data Extraction in the StudioX ribbon to start a wizard that will help us extract the data from the website. When we complete the wizard, two already configured activities will be added automatically to our project: a Use Application/Browser activity that indicates the web page and an Extract Table Data activity that extracts the data. We will then add a series of Excel activities to transpose the data, format the data as dates or numbers, and append the data to the worksheet with historical exchange rates.

Create a new blank project using the default settings.
Download and extract the archive with the automation project in this tutorial using the button at the bottom of this page and copy the file currency data.xlsx to your project folder.
This example uses Google Chrome, which requires the UiPath extension for Chrome to be installed. To install it, go to Home (StudioX Backstage View) > Tools, and then, under UiPath Extensions, click Chrome.

Click Add activity
in the Designer panel, and then find the Use Excel File activity in the search box at the top of the screen and select it. A Use Excel File activity is added to the Designer panel.
In the activity:
- Click Browse
  next to the Excel file field, and then browse to and select the file currency data.xlsx
- In the field Reference as, enter currency.
  You have indicated that you will work with the file currency data.xlsx that is known in your automation as currency.

Open Google Chrome and navigate to the following web page: https://www.ecb.europa.eu/stats/policy_and_exchange_rates/euro_reference_exchange_rates/html/index.en.html.
In StudioX, click Table Extraction in the ribbon to open the Extract Wizard.

In the Select Element wizard step, click Next to indicate the table from which to extract the data.
Start moving your mouse and notice how StudioX highlights user interface elements it detects. Move the mouse somewhere in the table and click, for example you can click the Currency header.

StudioX detects that you have clicked inside a table and asks you if you want to extract data from the entire table. Click Yes.
A preview of the extracted table data is displayed. Click Finish.

StudioX asks if the data spans multiple pages. Since that is not the case, click No.
The wizard is completed. Your project is displayed in StudioX, where a Use Application/Browser and an Extract Table Data activity have been added.
In the Extract Table Data activity, click Plus
on the right side of the Extract to field, and then select currency > Sheet1 [Sheet] to indicate that you want to copy the extracted data to Sheet1 of the currency Excel file.

Click Add activity
below the Use Application/Browser activity, then find the Copy Range activity in the search box at the top of the screen and click it. The activity is added to the project.
In the Copy Range activity:
Click Add activity
below the Copy Range activity, then find the Format Cells activity in the search box at the top of the screen and click it. The activity is added to the project.
In the Format Cells activity:
- Click Plus
  on the right side of the Source range field, and then select currency > Indicate in Excel.
- In the Excel file, select Sheet2, then select the entire third row containing the exchange rates, and then click Confirm in the UiPath tab in the Excel ribbon.
- Click Set Format. Select the Data Type tab and from the Category drop-down menu select Number. In the Decimals box enter 2, and select Use 1000 separator.
  You have indicated that you want to format the data in Sheet2, row 3 as number with two decimals and use the 1000 separator.
Add a second Format Cells activity below the previous one.
In the second Format Cells activity:
- Click Plus
  on the right side of the Source range field, and then select currency > Indicate in Excel.
- In the Excel file, select Sheet2, then select cell A3, and then click Confirm in the UiPath tab in the Excel ribbon.
- Click Set Format. Select the Data Type tab and from the Category drop-down menu select Date and from the Date format drop-down menu select 3/14/2012.
  You have indicated that you want to format the data in Sheet2, cell A3 as date with the format d/mm/yyyy. This is the cell where we will add today's date.
Click Add activity
below the second Format Cells activity, then find the Write Cell activity in the search box at the top of the screen and click it. The activity is added to the project.
In the Write Cell activity:
Click Add activity
below the Write Cell activity, then find the Append Range activity in the search box at the top of the screen and click it. The activity is added to the project.
In the Append Range activity:

Click Save in the StudioX ribbon to save the automation, then click Run to execute the automation.

The web page is opened, the exchange rates are copied to Excel where they are formatted and added to the Historical Data sheet.

Download example

Updated about a year ago

Langkah mengimpor data File CSV di excel?

Pada menu File, klik Impor. Dalam kotak dialog Impor, klik opsi untuk tipe file yang ingin Anda impor, lalu klik Impor. Dalam kotak dialog Pilih File, temukan dan klik file CSV, HTML, atau teks yang ingin Anda gunakan sebagai rentang data eksternal, lalu klik Dapatkan Data.

Bagaimana cara input data?

Jika menghendaki melakukan input data dari formula bar langkah yang diperlukan adalah sebagai berikut:.

Pilih/aktifkan sel mana data akan di masukkan..

Letakkan kursor pada formula bar..

Ketik/tulis data yang akan diinputkan ke dalam sel pada formula bar..

Tekan Enter untuk melakukan konfirmasi input data tersebut..

Bagaimana cara membuka File CSV?

Cara Membuka File CSV di Microsoft Excel.

Buka aplikasi Microsoft Excel..

Setelah itu, klik 'Data', lanjutkan dengan memilih opsi 'From Text/CSV'..

Kemudian, pilih file CSV yang terdapat di laptop atau perangkat..

Selanjutnya ubah Delimiternya menjadi 'Comma', lalu klik 'Load'..

Selesai, berkas CSV dapat dibuka dengan rapi..

Bagaimana cara mengubah excel ke CSV?

Anda dapat mengonversi lembar kerja Excel menjadi file teks menggunakan perintah Simpan Sebagai. Buka File > Simpan Sebagai. Klik Telusuri. Dalam kotak dialog Simpan Sebagai, di bawah kotak Simpan sebagai tipe, pilih format file teks untuk lembar kerja; misalnya, klik Teks (Dibatasi tab) atau CSV (Dibatasi koma).