Exports excel data to SharePoint custom list with desired column names!

So once again I am back with a blog post…

Today we will see that how to import a spreadsheet inside a custom list with defined columns.

We are ready for production deployment for an application. This application contains the master data for cities all over the world. Client provided us the list of 9000 cities that need to be inserted inside master custom list as we know that 9000 entries will take the hell lot of time to add in the list. So how would I do it within the sort span of time?

The only way that I put all the cities inside an excel and then create a new a custom list by importing that excel. So I start the thing. I create a list using import spreadsheet option but ooopsssss all cities updated inside that custom list but when I look at the column name this shows me like Column1, Column2, etc….

This was very disappointing as this list and columns are used in my custom code, and I don’t want to change my code due to this.

SO QUESTION WAS that I want 9000 entries inside custom list with desired column names…

What to do so below are the steps to do that.

 

  • First open the excel sheet.
  • Paste the data inside excel sheet accordingly you want.
  • Now in the first row put the column name that you want the list column name.
  • Remember that between two columns there should not be any column left blank.
  • Now select first cell of the sheet.
  • Click on Insert tab and then click on the table.
  • It will open a Create table window with the selected range of data.
  • Select my table has headers checkbox then ok.
  • Now your sheet is in a table format, and we are on the way.
  • Now open the site and select on create.
  • Create the custom list using import spreadsheet option
  • Give a name of the list and path of the excel sheet and click on import.
  • It will open the excel sheet and a window.
  • Select cell ranges in the drop down and give the range of the cells.
  • Just import and after completing the process we have a custom list with data and desired column names.

Happy SharePointing 🙂

6 Comments

  • Pharmc522 says:

    Hello! ddkdbfe interesting ddkdbfe site!

  • Excellent article. I was checking constantly this blog and I am impressed! Extremely helpful information particularly the last part. I care for such information much. I was looking for this particular information for a long time. Thank you and good luck.

  • Excellent piece. I was checking constantly this blog and I am impressed! Very useful info specially the last part. I care for such info a lot. I was looking for this particular info for a very long time. Thank you and good luck.

  • Naresh says:

    Hi Kailash,
    Brilliant article. This piece of info has saved me from manually creating the headers for 60columns. Hats off to you for sharing such a wonderful information. I guess this should be published in my company’s known workaround DB for share-point 😉

    Regards,
    Naresh

  • Chris Wallace says:

    Great. Many thanks. Lots of stuff on the web on this topic but none of it hit the mark like this.

  • RanCon says:

    Hi, I tried your instructions but I keep having column1, column2, etc instead of excel header names. Thanks,