CSV is only capable of storing a single sheet in a file. It doesn't keep any formatting and formulas.
While Excel (XLS and XLSX) file formats are better for storing more complex data, you can import CSV files on nearly all data upload interfaces. If you plan to move your data between platforms, export them, and import them from one interface to another, we recommend using the CSV file format.
You can import data from a text file into an existing worksheet. If you play a role in your organization that works with data (accounting, marketing, etc.), this help guide will help you.
Import CSV data to Microsoft Excel
On the Data tab, in the Get & Transform Data group, click From Text/CSV.
In the Import Data dialog box, locate and double-click the text file that you want to import. Then, click Import.
When the Text Import Wizard is open, please choose Delimited and select File Origin. For languages with Latin or Cyrillic characters, please choose Unicode (UTF-8). If your language has a different alphabet (such as Arabic and Hebrew), you can select it here.
In the next step, please select Comma as your Delimiter (After all, CSV means Comma Separated Values). When you try to merge data between Uscreen and an external source (CRM, Marketing Software, etc.), sometimes, you may be required to select a Text qualifier. For instance, Stripe CSV reports require this.
Note the difference after you select the Comma option. It should delimit your data and look like this:
Then, you can format your data. Please consider this as an optional step that you can skip.
Then, it will allow you to select where you want to import your data. We recommend creating a new sheet for every import - for this example, we have used the Existing sheet because it's a new file.
If we repeat the process to import additional data, we will use New sheet instead.
Then your data is imported.
Combine data from several tables onto one worksheet by using VLOOKUP
You can use VLOOKUP to combine multiple tables into one, as long as one of the tables has fields in common with all the others.
There are four pieces of information that you will need to build the VLOOKUP syntax:
The value you want to look up - also called the lookup value.
The range of the lookup value - the lookup value should always be in the first column in the range for VLOOKUP to work correctly. For example, if your lookup value is in cell "C2", your range should start with "C."
The column number in the range that contains the return value. For example, if you specify B2:D11 as the range, you should count B as the first column, C as the second, etc.
Optionally, you can specify "TRUE" if you want an approximate match or "FALSE" if you want an exact match of the return value. If you don't choose anything, the default value will always be "TRUE" or an approximate match.
Now put all of the above together as follows:
=VLOOKUP(lookup value, the range containing the lookup value, the column number in the range containing the return value, Approximate match (TRUE), or Exact match (FALSE)).
Use Case
You want to export the invoices and include the country and state to pay your taxes.
Note: you can use this in any situation that requires merging data from two different sources, but this is a typical example.
Pre-requisites
You requested the sales by location report to the Uscreen support team (via support@uscreen.tv) - Information for businesses located in the United States and Canada.
You downloaded the paid invoices report from the Admin Area - We recommend filtering your report by "Status: Paid" before you export it.
Instructions
Follow the instructions to Import CSV data to Microsoft Excel - listed above - and import the two files on different sheets.
Sales by location on Sheet1
Paid Invoices on Sheet2
You can now follow the instructions to Combine data from several tables onto one worksheet by using VLOOKUP - listed above.
Please see the formula we used below.
=VLOOKUP(Sheet2!E2;Sheet1!A:F;5;FALSE)
Where:
Sheet2!E2 is the E2 cell on Sheet2 (the worksheet that contains Paid Invoices)
Sheet1!A:F is the cell range (between A and F) on the Sheet1 (the worksheet that contains Sales by Location)
Five (5) is the column index. In this example, the fifth column contains the country_name. If we wanted to get the state, we would use Six (6) instead.
FALSE refers to the Range Lookup option - when Range Lookup is set to TRUE, it will return the closest match, whereas FALSE will only return an exact match.
The result looks like this:
