How to Map and Transform a CSV File for Import

Hi and welcome to DataFlowMapper. In this video, I'm going to show you how to take a raw CSV file and transform it into a clean, ready-to-use format for importing. By the end of this short tutorial, you'll have completed your first transformation and be ready to tackle your own data projects. Here's our source file. It contains transaction data for purchases that we want to load into our destination system. The file has common fields like first name, last name, address, and phone number. Let's start on the main transformation screen. Our goal is to import our transactions, but our source file isn't in the right format for our destination system. First, let's upload our source data. We can select the file type that our source data is in and drag and drop the file or click to upload. At the bottom of our screen, we should see our selected source file. We can also click the X here to remove it. Next, we need to tell DataFlowMapper what our target format looks like. We'll do this by creating a new mapping and uploading a template file that just contains the headers for our destination system. Let's start by clicking 'Create Mapping'. Here we're prompted to either start from scratch and manually enter headers, or upload a file to pull the headers from. We're going to be choosing the upload option. Now we take our file with the destination template headers and drag and drop it. As simple as that, our headers have been imported and we can begin mapping. Each card represents a destination field where we can add mappings, custom logic, and validation. For the purpose of this video, we'll stick with simple mapping and transformations. Now we'll map our source fields to our destination fields. In the expandable sidebar, we can see all the source fields available for mapping. For simple one-to-one matches, you can just drag and drop the source field onto the corresponding destination field. Here I'll drag 'CustID' to 'CustomerID'. We can also click the input itself and choose from the searchable dropdown instead of dragging and dropping, which we'll do for the 'ContactEmail' field. But what about the 'FullName' field? Our source data has 'FirstName' and 'LastName' separately. We need to combine them. For this, we'll use the Logic Builder. We can define custom logic for any of our fields by clicking 'Add Custom Logic' on that field's card. The Logic Builder lets you create any kind of custom transformation. We'll use a simple 'Concatenate' function to join the first and last names in the functions tab, but the logic builder allows for complex logic with variables, conditional if/thens, and function chaining, which we'll touch on in-depth in another video. On the functions tab, I'll click 'Add function'. Let's name the function 'createFullName', and I'll search for the function 'concatenate'. Here I have my input parameters for the concatenate function, and I'll select 'FirstName' as the first value, add a space, and then select 'LastName' as the second value. We can see on the right-hand side, the function showed up in our Progress Checklist, and the logic builder generated the corresponding python code to run this transformation. Let's navigate to the 'Return Result' tab, apply the result of the function we just created, and click save. Now for each row, that logic will be applied for our 'FullName' field. And just like that, we've created our custom logic. Now, we could continue mapping these fields manually, but let's see how AI can speed this up. I'll click 'Suggest Mappings' and let the AI copilot analyze our fields and data. Additionally, we could have used 'AI Complete Mapping' and had our intelligent copilot create all mappings and custom logic for you. The AI attempted to map each field and gave us its rationale and a confidence score, so we can review and accept or decline the mappings. Perfect. The AI handled the rest of the fields that should be mapped one-to-one. Now we'll save our mapping so we can reuse it later. Let's click save, name our mapping file, and go back to the transformation engine. We're back on the main screen. Our source file is loaded, and our new mapping file is selected. All that's left to do is click 'Transform'. And just like that, 5,000 records have been transformed and the output file has automatically downloaded. Here we can see some statistics on our transformation, and download a log file which would show any warnings, failures, or errors that occurred during transformation. Now we can click 'view transformed data' to see the output. The data we mapped is now in the correct format. You can see our 'FullName' column has been perfectly created by combining the first and last names. Now any time we have a similar source file, all we have to do is upload it along with the mapping file, and we can consistently transform our data into our destination format. In just a few minutes, we went from a raw source file to a clean, validated output, ready for import. We mapped fields directly, used the Logic Builder for a custom requirement, and even used AI to accelerate the process. To try this for yourself, sign up for a free trial at our website.