Filters:

  • Technologies

  • Instructors

  • Part 3 How to Import a CSV Text File into SharePoint 2013 – Creating the list in SharePoint

    SharePoint Video Training Series – Importing a CSV file into SharePoint 2013.

    1. Introduction
    2. Prepare the Excel File
    3. Create the List in SharePoint 2013
    4. Create the Views in SharePoint 2013
    5. Create the SharePoint Home Page Dashboard

    For instructor-led SharePoint training, see our class schedule.

    This is part three of a five part SharePoint 2013 training videos series on moving a Comma Separated Values (CSV) text file list into Excel, then getting that data into SharePoint and manipulating the data. We will do this with out-of-the-box SharePoint techniques with no special programming required.

    Download the Products.csv file.

    This one segment Creating the Lists in SharePoint. Now, I’m going to go ahead and start with a brand new fresh site.

    001-importing-csv-file-into-sharepoin-2013-create-the-list

    I don’t have to start with a fresh site, I could certainly do it here, but I want to keep my navigation nice and clean, to focus on the stuff we’re going to do in this training video.

    I’m going to go to site contents.

    002-importing-csv-file-into-sharepoin-2013-create-the-list

    Just for reference, I’m on the top level site, a Site Collection, where the top level side was based on the Team Site Template with the Publishing feature not on. If you don’t know what that is, don’t worry about it, but it you do, you’ll understand what I mean. It’s just to let you know, I’m in a collaboration scenario.

    I’m going to create a new subsite, and I’m actually going to call this subsite, “Products”. It’s going to be a products site. I will put it at the URL products.

    003-importing-csv-file-into-sharepoin-2013-create-the-list

    It’s going to be whatever the URL is to the top level site /products, and I’m going to say, “Site for our company Products.” Of course, it’s a fictitious company, it’s not really what it is. I’ll base the subsite on the teams site template.

    004-importing-csv-file-into-sharepoin-2013-create-the-list

    That is appropriate choice and I’ll leave all the settings default, except I’m going to select yes for the option of “Use the top link bar from the parent site?”.

    005-importing-csv-file-into-sharepoin-2013-create-the-list

    I’m asking it to display this site on the top link bar of the Parent Site, and then I’m saying, use the top link bar on the parent site, and that gives a nice effect, because what it does is it unifies the navigation experience on the top link bar between the site above it and this site here. Its Parent Site and its site will have the same navigation, so it’ll be a nice, fluid navigation.

    If I quickly want to get from one to the other, I can do that simply by clicking the top link bar, If I make changes to the top link bar of the top link site, it’ll push down to this side as well.

    You can see I’m on the Home page of my Products sub site, but I still have that top link bar. There’s the Class Demo’s site, and there is the link to my Product’s site.

    006-importing-csv-file-into-sharepoin-2013-create-the-list

    If I go back to the class demo’s site, because I ask this to appear, I can still see a link quickly to get back to my product subsite, where I’m going to do all my work for this particular video.

    007-importing-csv-file-into-sharepoin-2013-create-the-list

    The focus of this video is Creating a List in SharePoint. To do this, by importing a spreadsheet, I’m going to click Site Actions, the Site Actions gear in the upper right hand side.

    008-importing-csv-file-into-sharepoin-2013-create-the-list

    There’s other ways to do it too, but what I’m looking for is something that says Add an App so I’ll choose Add an App.

    Then there are several different templates that appear.

    009-importing-csv-file-into-sharepoin-2013-create-the-list

    I’m looking for one that says Import Spreadsheets. Since I don’t see it right away, I’m just going to type the word “Import” and use that awesome search functionality on the app’s page of SharePoint 2013.

    You may also like:  Part 1 - Importing a CSV file into SharePoint 2013 - Introduction

    010-importing-csv-file-into-sharepoin-2013-create-the-list

    I’m going to fill out some information. I’m going to call this Products. That means that this is going to be called the Products List. I’ll add a description, “Imported from products.xlsx,” on whatever the date is.

    011-importing-csv-file-into-sharepoin-2013-create-the-list

    You don’t have to put that there, but I find it handy, especially for migrating data from one location into SharePoint, and it used to be somewhere else. I find that to be useful information.

    Then I’m going to browse. In this case it’s on my desktop, and it is called products.xlsx.

    012-importing-csv-file-into-sharepoin-2013-create-the-list

    This is the Excel file we prepared last time, on the last video [url] , which you can see in this series. Then I’ll click Import.

    013-importing-csv-file-into-sharepoin-2013-create-the-list

    It’s really nice to have some sample documents that you can have to really work with views, and understand some of the out of the box functionality of SharePoint. Again, that plain CSV file, which we show you how to get into Excel and prepare, is available for downloading on any of these five video blog posts.

    This is a Table Range, because we format it as a table. On the top drop down, then I’m going to click here and find my table range, which is my Sheet1Product

    014-importing-csv-file-into-sharepoin-2013-create-the-list

    Then find named Table Range, so I’m going to click Import.

    SharePoint’s will analyze the file. It’s going to go ahead and process the data, and then it’s going to create a new list in SharePoint, then import all that date into the list.

    015-importing-csv-file-into-sharepoin-2013-create-the-list

    It’s showing me 30 items at a time.

    What I’m going to be checking here is these column names. I’ve got Product ID. One came in as Name One. One came in as Product Model. Unit Cost and Prod‑Dept.

    016-importing-csv-file-into-sharepoin-2013-create-the-list

    I will change the column to Name One to Product or Prod Description, just to make it easier, so to do that. I’m going to go to the List tab, and I’m going to go to List Settings.

    017-importing-csv-file-into-sharepoin-2013-create-the-list

    I’m going to click on Name One, which is a single line of text.

    018-importing-csv-file-into-sharepoin-2013-create-the-list

    And I’m going to change that to Prod‑Desc, for Product Description.

    019-importing-csv-file-into-sharepoin-2013-create-the-list

    I’m not going to change the data type, it’s fine. I just want to have it a little bit more friendly with what I’m working with.

    Now I’ve got my column names as I want them, Product ID, Prod‑Desc, Prod‑Model, Unit Cost, etc., things like that, and then Department.

    020-importing-csv-file-into-sharepoin-2013-create-the-list

    Now, looks like these are coming sorted by price, but just to make sure, I’m going to sort the default view. I’m going to say Modify View.

    021-importing-csv-file-into-sharepoin-2013-create-the-list

    I’m just going to make sure that that default view is sorted by price.

    022-importing-csv-file-into-sharepoin-2013-create-the-list

    Well as it turns out, they happened to be sorted in Excel by price originally, but just in case, I’m going to go ahead and change the sorting from the ID column to the Unit Cost.

    023-importing-csv-file-into-sharepoin-2013-create-the-list

    I’m going to create a column called, Retail Price, but right now Unit Cost will be fine.

    I want the least expensive products in the default view to be at the top, and the most expensive to be at the bottom. If I take a glance I can see that these seem to be obeying that just fine.

    024-importing-csv-file-into-sharepoin-2013-create-the-list

    I’ve got that ready to go. The data’s inside of SharePoint. Everything is good. I’ve even modified the column name, and I’ve modified the default view to be sorted by price. The next thing I want to do is add a column and I want it to be the Retail Price, and that’s going to be a Calculated Column. It’s actually going to use a calculation.

    You may also like:  How to Configure Navigation in SharePoint Publishing Sites

    025-importing-csv-file-into-sharepoin-2013-create-the-list

    It’s going to take the unit cost, whatever that value is, and it’s going to add that unit’s cost times 0.20, so the unit cost plus 20 percent.

     

    Just as a safety precaution, I’m going to pass that to a round function {  ,2  } into SharePoint, just to make sure that I don’t have any unexpected rounding issues.

    To do that, I’m going to click on the List tab, and I’m going to click Create Column.

    026-importing-csv-file-into-sharepoin-2013-create-the-list

    This will be a list column. I’m going to call this Retail Price. The data type for this is going to be calculated column, so I’m going to go ahead and click Calculated Column.

    027-importing-csv-file-into-sharepoin-2013-create-the-list

    Then I’m going to add my formula in there.

    It’s really a good idea to put your formulas, at least what you type in, into Notepad or some text file. When you’re done, you’ll see what I mean here, because it’s easy to make a mistake here.

    I’m going to say equals, and I’m going to say round. Let me round. It’s easy to make a mistake with your parens, so I’m going to put two parentheses here. Then I’m going to double click Unit Cost.

    028-importing-csv-file-into-sharepoin-2013-create-the-list

    Then I’m going to put a plus sign. Then I’m going to open another parentheses, and I’m going to double click Unit Cost, and I’m going to put a plus sign, and I’m going to put times 0.20.

    029-importing-csv-file-into-sharepoin-2013-create-the-list

    I’m going to close the parens for Unit Cost times 0.20. Close the parens for Unit Cost plus that. Put a comma, put a two, to round it to two decimal places, and then close that parens. Again, the formula is here.

    030-importing-csv-file-into-sharepoin-2013-create-the-list

    Now, what I would normally do is I’d copy that, paste it into Notepad or a text editor, just in case what I typed in is incorrect, which it does happen more often than not, for me, because I’m not perfect. The problem is it doesn’t let you start from where you left off, and you can have some fairly sophisticated formulas, so that’s something that I tend to do.

    I want to return this as currency, so I’m going to select that.

    031-importing-csv-file-into-sharepoin-2013-create-the-list

    Even though I’ve passed it to the round function of two decimal places, I’m also going to choose Return the Number of Decimal Places to two, and I’m going to click OK.

    032-importing-csv-file-into-sharepoin-2013-create-the-list

    If I typed my formula in correctly, or at least syntactically correctly, I’ll get some results, and then usually in production I like to do some cursory calculator checks, just to make sure that I’m getting what I’m expecting to get. This looks like it’s coming out right. $20 + 20 percent should be $24, so that looks good.

    033-importing-csv-file-into-sharepoin-2013-create-the-list

    Now, it’s ready to go. The next video we’re going to go ahead and create the views and then in the last video, we’ll create the dashboards.

    Spike Xavier
    SharePoint Instructor – Interface Technical Training
    Phoenix, AZ

    5 Part SharePoint Video Training Series – Importing a CSV file into SharePoint 2013.

    1. Introduction
    2. Prepare the Excel File
    3. Create the List in SharePoint 2013
    4. Create the Views in SharePoint 2013
    5. Create the SharePoint Home Page Dashboard

    See what people are saying...

    1. Pingback: Importing a CSV file into SharePoint 2013. Part 1: Introduction

    2. Pingback: Video - How to Import a CSV Text File into SharePoint 2013 - Part 2 Preparing the Excel File

    Share your thoughts...

    Please fill out the comment form below to post a reply.