Assignments Of Class 14: Data Import and Export
Assignment 1: Importing Data from a CSV File
Objective: To practice importing data into Excel from a CSV file.
Instructions:
- Download a Sample CSV File:
- Use the following sample data to create a CSV file named SalesData.csv:
Date,Product,Quantity,Price
2024-01-01,Widget A,10,5.00
2024-01-02,Widget B,20,10.00
2024-01-03,Widget C,15,7.50
2024-01-04,Widget A,5,5.00
2024-01-05,Widget B,30,10.00
- Import the CSV File into Excel:
- Open Excel and go to the Data tab.
- Select Get Data > From File > From Text/CSV.
- Browse to the location of your SalesData.csv file and click Import.
- Follow the prompts to load the data into a new worksheet.
- Verify the Data:
- Check that all columns are correctly imported and aligned.
Expected Outcome:
- A new worksheet in Excel displaying the imported sales data in a tabular format.
Assignment 2: Exporting Data to PDF Format
Objective: To practice exporting an Excel worksheet to PDF format.
Instructions:
- Create a Summary Report:
- Using the data imported from the CSV file, create a summary report by calculating the total sales for each product. You can do this by adding a new column for "Total Sales" calculated as Quantity * Price.
- Format the Worksheet:
- Ensure your summary report is well-formatted (e.g., use bold headers, adjust column widths).
- Export the Worksheet to PDF:
- Go to the File tab and select Save As.
- Choose the location to save your file.
- In the "Save as type" dropdown, select PDF.
- Click Save and confirm that the worksheet is saved as a PDF file.
Expected Outcome:
- A PDF file containing the formatted summary report of total sales for each product.
Assignment 3: Importing Data from a Text File
Objective: To practice importing data from a text file into Excel.
Instructions:
- Create a Sample Text File:
- Use the following sample data to create a text file named Inventory.txt:
Product1 50 10.00
Product2 30 15.00
Product3 20 7.50
- Note: Use tab spaces to separate each value.
- Import the Text File into Excel:
- Open Excel and go to the Data tab.
- Select Get Data > From File > From Text/CSV.
- Browse to the location of your Inventory.txt file and click Import.
- Choose Tab as the delimiter and follow the prompts to load the data into a new worksheet.
- Verify the Data:
- Ensure that the imported data appears correctly in columns.
Expected Outcome:
- A new worksheet in Excel displaying the imported inventory data in a tabular format.
Assignment 4: Exporting Data to CSV Format
Objective: To practice exporting data from Excel to CSV format.
Instructions:
- Use the Imported Sales Data:
- Use the sales data you imported earlier from SalesData.csv.
- Add a New Column:
- Create a new column named "Total Revenue" and calculate it by multiplying "Quantity" by "Price".
- Export the Worksheet to CSV:
- Go to the File tab and select Save As.
- Choose the location to save your file.
- In the "Save as type" dropdown, select CSV (Comma delimited) (*.csv).
- Click Save and confirm that only the active worksheet will be exported.
Expected Outcome:
- A CSV file containing the data from your Excel worksheet, including the new "Total Revenue" column.
Summary of Assignments:
- Assignment 1: Import data from a CSV file into Excel.
- Assignment 2: Create a summary report and export it to PDF format.
- Assignment 3: Import data from a text file into Excel.
- Assignment 4: Export Excel data to CSV format.
