CSV Imports

When we looked at reports and saved searches we talked about exporting their results. That’s all well and good for getting data out of the system, but what about getting data into the system from an external file. For this task we have CSV Imports. Before we go into NetSuite, I want to talk about CSV files, incase you are not familiar with what they are. CSV is an acronym for Comma Separated Values. CSV files contain structured information with each piece of data separated by a comma. Rows of data are usually separated by a carriage return or line break. CSV’s are so common, that in most systems they open in a spreadsheet program such as Excel. Let’s take a look at this one I have on my desktop. Double clicking to open this, it opens in Excel. Let’s go ahead and expand the columns so we can see everything. This is a simple CSV file populated with Disney character names, and some information about those characters. Each of the cells corresponds to a discrete piece of information, such as the email address for Daisy. Each of the columns helps us organize the data. We have First Name, Last Name, Number, which in this case is a phone number, Email, and Company. We’re going to import this file into NetSuite in a few minutes. Now, to show this isn’t just an excel file, I’ll go ahead and close it. We didn’t make any changes, so we don’t need to save it. We can also open this with Notepad, by right clicking and selecting open with notepad. The file is a little more complex to read this way, but it is exactly the same. Between every piece of data, we see a comma. The comma is what tells Excel, or NetSuite, that this is the end of one piece of information, and the beginning of another. I’ll go ahead and close this, and let’s go into NetSuite, which I already have open here in my taskbar. Before we do the import of these contacts, I want to show you that I don’t already have them in the system. If we go to Lists, then Relationships, then click Contacts, we see all of our contacts. They are reverse sorted by Internal ID right now, so the newest ones are at the top. To start the import we go to Setup, then Import/Export, and click Import CSV Records. This takes us to the Import Assistant. The first thing we need to do is select the type of import we will be performing. Contacts is not actually listed here, instead we need to select Relationships. Now under Record Type, we can select what we want. Contacts is the default, but there are other options. For example, you might be importing Customers or Leads. We have an option to choose a CSV column delimiter. Technically CSV files are always separated by a comma. Since they are so prevalent though, other structured files are sometimes called CSV, or comma separated value files, even if that is not actually what they are. If you are using a file that is separated by a different character such as a pipe or tab, then you would choose that option here. By default, the system is setup to allow us to upload a single file at a time. This is what you will use most often, but there could be instances where you have more than one file, such as uploading customers and contacts together. In that case you would select Multiple files to upload and follow the prompts. The process is almost identical, so we won’t be covering it here. I’ll go ahead and change this back to one file, and then select the file to upload. This one here on my desktop is the same one we were looking at earlier. Click Next and we’re taken to the next step in the process. Here we have options to add data, update data, or add or update data. Adding is generally used to import new and unique information into the system. Updating is used when you want to update records that are already in the system. The last option, Add or Update, allows you to use one file and have it update records if certain criteria match, or add new records if the criteria does not match. Usually the criteria you use is either the Internal ID or External ID for the record. We have a whole host of advanced options down below. You can read through these if you want, but the defaults are usually fine. If you watch closely, you will notice that we skip step three as I click Next. This is because step three, File Mapping, is used when importing multiple files. In step four, Field Mapping, we tell NetSuite where we want our data to go. The left-hand column in the main window represents the file on our local computer. The right-hand column represents the record in NetSuite. In the middle we have our mapping, that is where fields in our file will be placed, in a NetSuite record. NetSuite tries to make its best guess about where data might go, based on the field titles in our file. So, we can see most of these are already mapped. In this case they are mapped correctly, but I encourage you to be extra thorough when reviewing mappings. They are easy to mess up, and hard to correct after the fact. Number didn’t get mapped, so let’s add that. If I click number it is added to the middle column where our pointer was. Now I’ll scroll down and click Main Phone. Since our pointer moved when I added Number, Main Phone went to the wrong place. This is easy enough to correct by clicking the X over here to remove this row. Now let’s select the correct row and click Main Phone again. This looks better. If there’s a value we know we need for every record, and that value is going to be the same we can provide it as a default. My instance of NetSuite requires a subsidiary be selected for all records. So, let’s click Subsidiary to add it, then click the pencil icon to provide a default value. Let’s select Honeycomb Mfg, and click done. Now that our mappings are all set, we can click next. This last screen gives us the option to Save the import. This is important for a few reasons. Right now we are only importing four contacts and it’s a really simple mapping. But, what if we had four thousand, and what if they were a lot more complex? It’s unlikely that we would want to import all of them into a live system without testing a small sample of, maybe half a dozen first. Also, what if we got something wrong during the process and had to go back and redo it. Saving the import means that you can test a small subset, delete the records if there are problems, and rework everything until you achieve success. Saving also means that imports and their mappings can be built iteratively. That is starting with a simple setup and gradually adding complexity. You are free to do an import without saving it, but I recommend against it in most cases. If we want to save this, and in this case I do, we need to provide a name for it. Let’s go ahead and type Disney Import. We can provide a description, and I recommend doing this as well, especially if this is an import you will keep around. Let’s click Save & Run, down here at the bottom. Our import is now running, and we can see this by the green status bar at the top. We can click the Import Job Status link here to see how the job has done. This screen shows us the percent complete, and if all the records were imported successfully or not, among other things. If some records were not, this screen will tell us what problems were encountered. As a couple of side notes. Importing four records happens in just a matter of seconds, but if you are importing large batches this can take longer, and you will see the progress on this screen. If you leave this screen for some reason you can get back to it by going to Setup, then Import/Export, and clicking View CSV Status. Our import is here at the bottom. We can see that only three of the four records were created successfully. Let’s click the CSV Response and see why. This downloads a CSV file, which we can open. Let’s expand the columns to get a clear look at what is going on. Ahh, here we can see that Fake Company apparently doesn’t exist. You might remember that Minnie was the only contact that had a company filled in, none of the other contacts did. I actually did this on purpose, so we could see this error come back. What we would probably want to do, is correct this, and import just this contact again. We’re not going to do that here, so I will go ahead and close this window, and there is no need to save any changes to this file. Now that we know three of four contacts has been imported successfully, let’s go ahead and take a look at our contact records. This is the same screen we saw earlier in this video, except now we have our new contacts. We did save this import, and if we wanted to access it, perhaps to reimport the failed contact we could do so. Let’s go to Setup, then Import/Export, and click Saved CSV Imports. Here we see all of our saved imports, including the Disney one. If we clicked on the name, we could go through the steps again with most of the data filled in. During the import process there were a lot of defaults that were filled in for us. Those defaults are found under Setup, Import/Export, and Import Preferences. Here we could set our default delimiter for multi-select values. We could also change the column delimiter or the decimal delimiter. If you don’t know what this is, don’t worry about it. Usually this will not need to be changed as the defaults generally make the most sense. I just wanted you to know where you could change the defaults if you needed to. The last thing I want to talk about has nothing to do with imports, but it is an option you may have noticed here at the bottom of the menus we were working with. It’s this Full CSV Export. Lot’s of folks see this and think: Hey wait, is this really what I think it is? Can I use this to perform a full export or backup of all the records in my NetSuite instance? The answer is a kind of yes and no. This full export is based on saved searches. If you setup the saved searches then you can, in fact, export all of your structured data with this option, but this does require a lot of work. Out of the box this is usually not setup. The reason I bring this up is because I don’t want you to think you are exporting everything, only to find out later, at a critical moment, that you really weren’t. If you are interested in exports or backups, there is a video that covers just that in the Ask the Professor series. CSV Imports are something you probably won’t do that often. However, when the need arises, you now have the knowledge to tackle it.