Part 2 – How to Import a CSV Text File into SharePoint 2013 – Preparing the Excel File

Home > Blogs > SharePoint > Part 2 – How to Import a CSV Text File into SharePoint 2013 – Preparing the Excel File

Part 2 – How to Import a CSV Text File into SharePoint 2013 – Preparing the Excel File

Like This Blog 1Spike Xavier
Added by October 5, 2016

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 two of a five‑part SharePoint 2013 training videos about moving data out of an Excel spreadsheet into SharePoint. Then we will leverage this using all out‑of‑the‑box functionality of SharePoint 2013. This video is Preparing the Excel file.

Download the Products.csv file.

001-how-to-import-a-csv-text-file-into-sharepoint-2013-preparing-the-excel-file

I’m going to open it up in Notepad.

002-how-to-import-a-csv-text-file-into-sharepoint-2013-preparing-the-excel-file

This is just a simple Comma‑Separated Values (CSV) file, and it has several headers.

003-how-to-import-a-csv-text-file-into-sharepoint-2013-preparing-the-excel-file

We’ve have a Product ID column, Name column, Product Model column, Unit Cost, and a Product Department column.

All of these will be the individual header columns. They will become List Columns in SharePoint as we create our list. Then the data separated by commas.

First I’ll open up Excel 2013 because I’m going to go ahead and grab this into Excel first. I’m going to start a blank notebook, and then I’m going to click on the data tab, which is up in the ribbon.

004-how-to-import-a-csv-text-file-into-sharepoint-2013-preparing-the-excel-file

I’m going to click that Data tab to activate the icons for putting data into an Excel workbook or worksheet. I’m going to highlight cell A1, just for good measure, and then I’m going to be getting the data from text.

005-how-to-import-a-csv-text-file-into-sharepoint-2013-preparing-the-excel-file

It’s a CSV file. That’s a text file, so I’m going to choose “from text” in the ribbon.

After I choose that, I’m going to have to browse for the file, which I’ve left on my desktop, and I’ve just named it products.csv.

006-how-to-import-a-csv-text-file-into-sharepoint-2013-preparing-the-excel-file

Now Excel’s going to notifies us “that it found a file with data in it. Just want to make sure that we’re on the same page with understanding what that data is, and how you want me to bring it into Excel.”

007-how-to-import-a-csv-text-file-into-sharepoint-2013-preparing-the-excel-file

The first default says, “Is this data delimited with characters such as commas, tabs separating each field?” This is separated by commas.

You may also like:  Part 3 How to Import a CSV Text File into SharePoint 2013 – Creating the list in SharePoint

008-how-to-import-a-csv-text-file-into-sharepoint-2013-preparing-the-excel-file

The next question is, “Does your data have headers?”

009-how-to-import-a-csv-text-file-into-sharepoint-2013-preparing-the-excel-file

Ours does so I’m going to go ahead and select, “Yes, this data has headers.” Then I’ll click next.

That helped Excel out a lot, and right now it’s looking for a tab delimited file.

010-how-to-import-a-csv-text-file-into-sharepoint-2013-preparing-the-excel-file

It can select every tab, every time I hit tab if I was creating the file, would see that as a new column. But actually mine is not tab delimited.

Mine is comma delimited, so I’m going to change that to comma.

011-how-to-import-a-csv-text-file-into-sharepoint-2013-preparing-the-excel-file

In this little data preview screen, you can see that Excel has already determined that our header is Product ID, Name, Product Model, Unit Cost, and Product Description.

012-how-to-import-a-csv-text-file-into-sharepoint-2013-preparing-the-excel-file

Everything looks good, so I’m going to click next.

Right now, I could go through each of these columns, and format them as I want. Everything looks good, but down here under unit cost column, I could theoretically, if I wanted to, click advanced

013-how-to-import-a-csv-text-file-into-sharepoint-2013-preparing-the-excel-file

Then change the decimal places.

014-how-to-import-a-csv-text-file-into-sharepoint-2013-preparing-the-excel-file

We won’t do that now. We will do it when it gets into SharePoint, because it’s nice to know how to do that in SharePoint.

I’m done. It wants to validate the range that I want to create, and I’m going click OK.

015-how-to-import-a-csv-text-file-into-sharepoint-2013-preparing-the-excel-file

That is the process of inserting everything into Excel. I’ve got the data in there; it’s always good to check everything.

016-how-to-import-a-csv-text-file-into-sharepoint-2013-preparing-the-excel-file

Now, what I’m going to do is format this data in Excel as a table. A1 is already selected. I’m just going to scroll down here. I’m using the mouse wheel. There’s several ways to do this, but I’m using the mouse wheel.

I’m going to find the last piece of data, which is in E87 in my case. I’m going to hold the shift key down, and click the right mouse key, and that will select that entire range.

017-how-to-import-a-csv-text-file-into-sharepoint-2013-preparing-the-excel-file

Then what I can do is click the home tab, and there’s an option up here in the ribbon to Format as Tables.

You may also like:  Part 5 - Importing a CSV file into SharePoint 2013: Create Home Page Dashboards

018-how-to-import-a-csv-text-file-into-sharepoint-2013-preparing-the-excel-file

I’m going to click that drop‑down.

You can pick any style that you like. I’m going to go with the really simple one, where the top row is dark black as a background, and white text. I’m going to go ahead and click “OK.”

019-how-to-import-a-csv-text-file-into-sharepoint-2013-preparing-the-excel-file

Now, the data is formatted as a table.

020-how-to-import-a-csv-text-file-into-sharepoint-2013-preparing-the-excel-file

This gives me functionality. Excel tables are awesome, but one thing that I want to do is I want to go ahead to the design tab and I’m going to change the table name.

021-how-to-import-a-csv-text-file-into-sharepoint-2013-preparing-the-excel-file

I’ll name is “Products” because, as I import this into SharePoint, it’s going to open the Excel file and go, “Which object in Excel do you want me to import and use as the data source for this list?” as I’m creating it. I want to go ahead and be able to say “table range,” and the table range is named table.

That’s about it, there. I’m going to now save this to my desktop. I’m going to actually call this file Products.xlsx, so it’s an XLSX workbook.

022-how-to-import-a-csv-text-file-into-sharepoint-2013-preparing-the-excel-file

I’ve now got my file ready to go. I’ve imported it into Excel. I’ve formatted it as a table, named the table as products, and save that to my desktop.

In the next video, we’ll get this data into SharePoint. That’s preparing the Excel file.

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

 

 

Videos You May Like

Agile Methodology in Project Management

0 21 0

In this video, you will gain an understanding of Agile and Scrum Master Certification terminologies and concepts to help you make better decisions in your Project Management capabilities. Whether you’re a developer looking to obtain an Agile or Scrum Master Certification, or you’re a Project Manager/Product Owner who is attempting to get your product or … Continue reading Agile Methodology in Project Management

Creating Users and Managing Passwords in Microsoft Office 365

0 30 1

In this Office 365 training video, instructor Spike Xavier demonstrates how to create users and manage passwords in Office 365.

Detailed Forensic Investigation of Malware Infections – April 21, 2015

1 79 1

In this IT Security training video, Security expert Mike Danseglio (CISSP / CEH) will perform several malware investigations including rootkits, botnets, viruses, and browser toolbars.

Write a Comment

See what people are saying...

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

Share your thoughts...

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