Microsoft Excel - Importing and exporting data

1. Importing Data into Microsoft Excel

Excel allows you to import data from various sources such as text files, databases, and the web.
Steps:

1. Open Excel and go to the Data tab.
2. Click Get Data → choose the source (e.g., From File, From Database, From Web).
3. Browse and select your file or connection.
4. Preview the data and click Load to import it into Excel.

---

2. Importing Data from Text Files

You can import .txt or .csv files containing delimited data (commas, tabs, etc.).
Steps:

1. Go to Data → Get Data → From File → From Text/CSV.
2. Choose the file and click Import.
3. In the preview window, choose the correct delimiter (comma, tab, etc.).
4. Click Load or Transform Data to edit before loading.

---

3. Importing Data from Microsoft Access

Use this option to connect Excel with an Access database and pull tables or queries.
Steps:

1. Go to Data → Get Data → From Database → From Microsoft Access Database.
2. Select the Access file (.accdb or .mdb).
3. Pick the table or query you want to import.
4. Click Load to bring the data into Excel.

---

4. (New Version) Import Data from Text Files into Excel

The latest Excel versions (Office 365, Excel 2021+) use Power Query for importing.
Steps:

1. Go to Data → Get Data → From File → From Text/CSV.
2. Power Query opens automatically — preview and format data.
3. Choose Load to sheet or Load to Data Model for advanced analysis.

---

5. (New Version) Import Data from a Database into Excel

You can import directly from SQL Server, Oracle, or other supported databases.
Steps:

1. Go to Data → Get Data → From Database.
2. Select the database type (e.g., SQL Server).
3. Enter the server name, database, and credentials.
4. Select the tables or views to import, then click Load.

---

6. Microsoft Excel Legacy Import Options for New Excel Versions

Newer Excel versions still support legacy wizards for backward compatibility.
Steps:

1. Go to Data → Get Data → Legacy Wizards → From Text (Legacy).
2. Choose your file and follow the step-by-step import wizard.
3. Use this when older formatting or delimiters don’t work properly in Power Query.

---

7. Exporting Data to a Text File

You can export your Excel worksheet data as a .txt or .csv file for use in other applications.
Steps:

1. Go to File → Save As.
2. Choose a location and select Save as type → CSV (Comma delimited) or Text (Tab delimited).
3. Click Save.
4. Excel will warn that only the active sheet is saved — click OK to confirm.

Microsoft Excel - Excel Data Validation

1. Understanding the Need for Excel Data Validation

Data Validation in Excel helps control the type of data entered into a cell.
It ensures data accuracy, consistency, and prevents invalid or incorrect entries.
For example, you can restrict users to enter only numbers, dates, or values from a predefined list.

Use Cases:

• Allow only whole numbers between 1 and 100.
• Accept only dates within a project timeline.
• Limit entries to department names like HR, Finance, Sales.

---

2. Creating an Excel Data Validation List

A drop-down list allows users to pick from set options rather than typing manually.

Steps:

1. Select the cell or range.
2. Go to Data → Data Validation → Settings tab.
3. In Allow, choose List.
4. In Source, type options separated by commas (e.g., Yes,No,Maybe) or refer to a cell range (=$A$1:$A$3).
5. Click OK.

Result:
A drop-down list appears, ensuring only predefined values can be selected.

---

3. Excel Decimal Data Validation

You can restrict entries to specific decimal ranges.

Steps:

1. Select cells → Data → Data Validation.
2. Choose Decimal under Allow.
3. In Data, choose a condition like between.
4. Enter the minimum and maximum values (e.g., 0 and 100).

Example:
Only values between 0.5 and 9.99 are accepted.

---

4. Adding a Custom Excel Data Validation Error

You can show a custom message when users enter invalid data.

Steps:

1. After setting validation rules, open the Error Alert tab.
2. Check Show error alert after invalid data is entered.
3. Choose a Style:
   • Stop (prevents invalid entry)
   • Warning (allows entry after warning)
   • Information (just informs user)
4. Add a Title and Message (e.g., “Invalid Entry: Please enter numbers only.”)

---

5. Dynamic Formulas by Using Excel Data Validation Techniques

You can make dynamic drop-downs that change based on another cell’s selection.

Example:

• Cell A1: Country (India, USA)
• Cell B1: City (changes based on country)

Steps:

1. Create lists for each country (e.g., IndiaList, USAList) and define named ranges.
2. In Cell B1 → Data Validation → Allow: List.
3. In Source, enter formula:

   =INDIRECT(A1)
   

4. Now, when “India” is selected in A1, only Indian cities appear in B1.

Microsoft Excel - List Functions

Introduction to Excel’s Database Functions

Excel's Database Functions work with data ranges treated like a database table.
They use field names (headers) and criteria ranges to calculate results based on conditions.

Common structure:

Function(Database, Field, Criteria)

• Database → Entire data range including headers
• Field → Column to calculate on (name or index)
• Criteria → Range showing condition(s)

---

✅ DSUM() — Sum Based on Criteria

Purpose: Sum values in a column that match specific condition(s).

Syntax

=DSUM(database, field, criteria)

Example

Sum Sales where Region = "East"

Criteria setup:

| Region | | East |

Formula:

=DSUM(A1:C100, "Sales", E1:E2)

---

Excel DSUM Function — Single Criteria (Continued)

You can use any column as a filter — e.g., Product = "Laptop".

| Product | | Laptop |

=DSUM(A1:C100, "Sales", E1:E2)

---

DSUM with OR Criteria

Place criteria in separate rows.

| Region |
| East |
| West |

Result = Sum where Region is East OR West

=DSUM(A1:C100, "Sales", E1:E3)

---

DSUM with AND Criteria

Place criteria in the same row.

| Region | Product | | East | Laptop |

Result = Sum where Region = East AND Product = Laptop

=DSUM(A1:C100, "Sales", E1:F2)

---

✅ DAVERAGE() — Average Based on Criteria

Purpose: Returns average values filtered by criteria.

Syntax

=DAVERAGE(database, field, criteria)

Example: Average Salary where Department = HR.

---

✅ DCOUNT() — Count Numeric Records Based on Criteria

Purpose: Count numeric cells matching criteria.

Syntax

=DCOUNT(database, field, criteria)

Use when counting numbers only.

---

✅ SUBTOTAL() — Dynamic Summary Function

Purpose: Performs operations like sum, average, count — but ignores hidden or filtered rows.

Syntax

=SUBTOTAL(function_num, range)

Common function numbers

Function	No.
AVERAGE	1
COUNT	2
SUM	9
MAX	4
MIN	5

Example

=SUBTOTAL(9, B2:B100)

(9 = SUM)

---

Key Difference: DSUM vs SUBTOTAL

Feature	DSUM	SUBTOTAL
Criteria based?	✅ Yes	❌ No (uses filters)
Works like database query	✅	❌
Good for filtered totals	✅	✅

OIC - Direct File Polling using File Adapter in Oracle Integration Cloud (OIC)

✅ Overview

OIC supports direct file polling using the File / FTP / SFTP Adapter to automatically trigger an integration when a file arrives in a folder.

This pattern is used for real-time file integration without scheduling.

---

🎯 Use Case

A customer receives files (CSV/XML/TXT) in an SFTP or file server folder and needs OIC to:

• Automatically trigger integration when file arrives
• Filter files by name pattern
• Read and process each file
• Archive or delete after processing
• Avoid scheduled polling

Important Limitation:

Dynamic directory path is NOT supported in direct polling mode.
The polling folder must be a fixed path in the adapter trigger.

---

🧠 Key Architecture

File Source (SFTP/Folder)
      ⬇
OIC File Adapter (Polling Trigger)
      ⬇
Integration Flow
      ⬇
Target System (ERP/HCM/DB/API/Storage)

---

⚙️ Adapter Properties

📍 Directory & File Filters

Property	Purpose
Input Directory	Folder to poll (must be fixed)
File Name Pattern	Wildcard match (*.csv, *.xml, Invoice_*.txt)
Read Files Recursively	Include subfolders
Rejection Directory	For non-matching files

---

⏱️ Polling Behavior

Property	Purpose
Polling Frequency	How often OIC checks for files
Processing Delay	Delay before reading (file write completion)
Maximum Files	Limit files per poll cycle

---

🗃️ File Handling

Property	Purpose
Delete Files After Successful Read	Remove file post-process
Ignore File-Not-Found on Delete	Avoid errors during concurrent moves
Archive / Error Directory	Folder to move processed/failed files

---

🧩 Dynamic Directory — Clarification

Capability	Status
Dynamic folder for polling	❌ Not supported
Fixed directory required in trigger	✅ Yes
Dynamic directory possible in scheduled list-read flow	✅ Yes

Conclusion:

Use Direct Polling only when folder path is static.
Use Scheduled Integration + List Files when folder needs to be dynamic (lookup-based).

---

🚀 Solution Steps

1️⃣ Create File / SFTP Adapter with Polling

• Enter Base Directory (mandatory)
• Enable Read, List, Move/Delete
• Save credentials & security config

---

2️⃣ Create App-Driven Orchestration

• Trigger → File Adapter
• Operation → Read File in Trigger/Polling Mode
• Configure:
  • Input Directory
  • File Pattern (Example: *.csv)
  • Archive & Error folders

---

3️⃣ Build Flow Logic

• Read File → Map → Process to target
• Add logging & validation
• Move / Delete file after success

---

4️⃣ Error Handling

• Fault-handler block
• Move file to error folder
• Send notification (Email/Teams/SMS)
• Audit integration logs

---

✅ Outcome

Benefit	Details
Real-time automation	No scheduler needed
Reliable file execution	Archive/error segregation
Controlled polling	Based on adapter properties
Platform aligned	Follows OIC capability & constraints

---

📌 When to Use Which Model

Requirement	Recommended
Real-time fixed folder	Direct File Polling Trigger
Different folders per env	Multiple connections OR deploy-wise config
Dynamic folder path needed	Scheduled + List Files Action

---

📝 Key Takeaway

OIC File Adapter Direct Polling = Static Folder + Event-Style Trigger
Dynamic Directory = Only via Scheduled integration

Microsoft Excel - Working with an excel list

Understanding Excel List Structures

An Excel list (table) is a structured set of rows and columns used to store related data (like employee info, sales records).

• Each column has a heading
• Each row is a record
• Helps in sorting, filtering, and analyzing data easily

---

Sorting a List Using Single-Level Sort

Sort based on one column only.
Steps:

1. Click any cell in the column you want to sort
2. Go to Data tab
3. Choose Sort A→Z (ascending) or Z→A (descending)

Example: Sort employees by Name alphabetically.

---

Sorting a List Using Multi-Level Sorts

Sort based on more than one column.
Steps:

1. Select the list
2. Data → Sort
3. Add first sort level (e.g., Department)
4. Click Add Level and add second sort level (e.g., Salary)

Example: First sort by Department, then sort each department by Salary.

---

Using Custom Sorts in an Excel List

Use custom order instead of A-Z or numbers.
Steps:

1. Data → Sort
2. Choose column
3. Under Order, select Custom List
4. Pick preset lists (Mon, Tue, Wed…) or create your own

Example: Sort months as Jan, Feb, Mar… instead of alphabetical order.

---

Filter an Excel List Using the AutoFilter Tool

AutoFilter helps you quickly show only the rows that match specific criteria while hiding the rest.

---

Steps

1. Select your data range (or click any cell inside your list)
2. Go to the Data tab
3. Click Filter (funnel icon)
   → Small drop-down arrows appear on each column header
4. Click the drop-down arrow on the column you want to filter
5. Choose your filter option:
   • Select / unselect values
   • Text Filters (Starts With, Contains…)
   • Number Filters (Greater Than, Between…)
   • Date Filters (This Month, Last Week…)
6. Click OK → Only matching rows are shown

---

Example

Filter a sales list to show only “East Region” records.

Steps:
Data → Filter → Region column drop-down → tick East → OK

---

Tip

To clear filters:
Data → Clear (or remove filter icon again)

---

Creating Subtotals in a List in Excel

The Subtotal tool automatically inserts group totals and summaries into a sorted list.

---

When to Use

Use Subtotal when you need totals for each group/category in a column (e.g., total sales by region, department, or product).

---

Steps

1. Sort your data by the column you want to group
   (Example: sort by Region or Department)

2. Go to Data tab

3. Click Subtotal

4. Set the options:

   • At each change in: choose the column to group by
     (e.g., Region)
   • Use function: select summary function
     (Sum, Count, Average, etc.)
   • Add subtotal to: choose the column to total
     (e.g., Sales Amount)

5. Click OK

Excel inserts subtotal rows for each group and a grand total at the bottom.

---

Example

To get total sales by Region:

• Sort by Region
• Subtotal → At each change in: Region
• Function: Sum
• Add subtotal to: Sales

---

Useful Tips

• Expand/Collapse groups using the outline buttons (1, 2, 3) on the left
• To remove subtotals:
  Data → Subtotal → Remove All

---

Format a List as a Table in Excel

Purpose:
Convert your data range into a structured Excel Table for easier sorting, filtering, and formatting.

---

Steps

1. Select the data range
   Highlight the list you want to convert.

2. Go to Home tab → Styles group
   Click Format as Table.

3. Choose a table style
   Select a design you like.

4. Confirm the range
   Excel shows the selected range — verify it.

5. Check “My table has headers”
   Tick this box if your list already has headings.

6. Click OK.

---

Result

✅ Data becomes a Table with filters
✅ Easy sorting & formatting
✅ Table styles applied automatically
✅ Auto-expands when new data is added

Using Conditional Formatting to Find Duplicates in Excel

Purpose:
Highlight duplicate values in a range to easily identify repeated data.

---

Steps

1. Select your data range
   (Example: A1:A50)

2. Go to Home tab → Conditional Formatting

3. Choose Highlight Cells Rules → Duplicate Values

4. In the popup:

   • Select Duplicate
   • Choose a formatting style (e.g., Light Red Fill)

5. Click OK

---

Result

✅ All duplicate values in the selected range are highlighted
✅ Very useful for data cleaning and checking repeated entries

---

Tip

To highlight unique values instead, choose Unique in step 4.

Removing Duplicates in Excel

Purpose:
Delete duplicate rows from data and keep only unique records.

---

Steps

1. Select your data range
   (Example: A1:C100)

2. Go to Data tab

3. Click Remove Duplicates

4. In the dialog box:

   • Choose the columns to check for duplicates
     (Example: Select all columns to remove fully duplicate rows)

5. Click OK

6. Excel shows how many duplicates were removed and how many unique values remain

---

Result

✅ Duplicate records removed
✅ Only unique rows stay in the sheet

---

Tip

If your data has headers, ensure "My data has headers" is checked in the dialog box.

Microsoft Excel - Working with excel templates

Working with Microsoft Excel Templates

What is a Template?
A template is a pre-designed Excel file that contains ready-made formatting, formulas, and layouts for common tasks like invoices, budgets, or reports.

---

How to Open an Existing Template:

1. Open Excel → click File > New.
2. Choose from available templates or search online.
3. Select a template and click Create to open it.

---

How to Create a Custom Template:

1. Create or format a workbook as needed.
2. Go to File > Save As.
3. Choose Excel Template (*.xltx) from the file type list.
4. Save it in the Templates folder for future use.

---

Tip:
Next time, open Excel → File > New > Personal to reuse your custom templates.

Microsoft Excel - Excel Formula to Check if Data Exists Across Sheets (Missing or Present)

✅ Excel Formula to Check if Data Exists Across Sheets (Missing or Present)

💡 Use Case

In integration or data-tracking scenarios, you often maintain two different sheets — for example,

• one with a master list of interfaces, and
• another with a subset list you want to verify.

You need to automatically check whether each interface in your current sheet exists in the master sheet, and mark it as “Present” or “Missing.”

---

⚙️ Formula Used

=IF(COUNTIF('OIC Interfaces'!F:F, H2) = 0, "Missing", "Present")

---

🧠 Formula Breakdown

Part	Meaning
COUNTIF('OIC Interfaces'!F:F, H2)	Counts how many times the value in cell H2 appears in column F of the sheet named ‘OIC Interfaces’.
= 0	Checks if the count is zero — meaning the value doesn’t exist in that column.
"Missing"	Returned when the value is not found in the target sheet.
"Present"	Returned when the value is found in the target sheet.
IF(...)	Combines the logic to display either “Missing” or “Present.”

---

🪜 Step-by-Step Solution

1. Identify your reference sheet – for example, 'OIC Interfaces' is your master list of integrations.
2. Choose the lookup column – here, column F in 'OIC Interfaces' contains the unique identifiers or interface names.
3. Write the formula in your working sheet – in cell N2, enter the formula to check if H2 exists in the master list.
4. Copy down the formula – Excel will automatically check all rows and mark each as “Present” or “Missing.”

---

🧩 Example

Sheet 1: Working Sheet

H (Interface Name)	Result
ERP_GL_Extract	(Formula result)
EPM_SFTP_Load	(Formula result)

Sheet 2: OIC Interfaces

F (Interface Name)
EPM_SFTP_Load
HCM_Payroll_Extract

Now when you use the formula:

=IF(COUNTIF('OIC Interfaces'!F:F, H2)=0,"Missing","Present")

✅ For ERP_GL_Extract, Excel shows “Missing”
✅ For EPM_SFTP_Load, Excel shows “Present”

---

🏁 Final Takeaway

This simple IF + COUNTIF combination is one of the most practical Excel tricks for:

• Validating data consistency across multiple sheets
• Checking missing records between reports
• Comparing lists during integrations or reconciliations

It’s lightweight, dynamic, and works perfectly for ERP, OIC, or data validation reports.