As a SharePoint administrator, I have encountered numerous situations where I needed to create a list from an Excel file with custom column types not supported by the built-in “Create List from XLSX” feature. In this blog post, I will discuss two alternative solutions to overcome this limitation: using an .stp template and modifying the XML of a list.

Solution 1: Using an .stp Template

One way to create a list with custom column types is by using an .stp template. An .stp template is a file that defines the structure and layout of a SharePoint list. You can acquire an .stp template from somewhere, such as a third-party website or a SharePoint community forum, and modify it to add the columns you need.

To use an .stp template, follow these steps:

1. Obtain an .stp template: You can download an .stp template from a reliable source, such as a SharePoint community forum or a third-party website. Make sure to check the template’s compatibility with your SharePoint version and ensure that it does not contain any malware or viruses.

2. Modify the template: Open the .stp template in a text editor, such as Notepad or Visual Studio Code, and modify the JSON definition to add the columns you need. You can use a JSON editor or a tool like JSON Editor Online to help you modify the JSON data.

3. Upload the template: Once you have modified the template, upload it to your SharePoint site. To do this, go to your SharePoint site’s “Lists” page, click on “Create List,” and select “From .stp Template.” Then, select the .stp template file you modified earlier and click “OK.”

4. Create the list: After uploading the template, you will be prompted to create a new list based on the template. Fill in the required information, such as the list name and description, and click “Create.”

Solution 2: Modifying the XML of a List

Another way to add custom columns to a SharePoint list is by modifying the XML of an existing list. This method requires some technical knowledge of XML and SharePoint development, but it can be a powerful tool for creating lists with custom column types.

To modify the XML of a list, follow these steps:

1. Export the list’s XML: Go to your SharePoint site’s “Lists” page, select the list you want to modify, and click on “Export List.” Choose “All Items” and select “XML” as the export format. Save the XML file to your local machine.

2. Modify the XML: Open the XML file in a text editor, such as Notepad or Visual Studio Code, and modify it to add the columns you need. You can use a tool like XML Editor Online to help you modify the XML data.

3. Import the modified XML: Once you have modified the XML file, import it back into your SharePoint site by going to your “Lists” page, selecting the list you want to modify, and clicking on “Import List.” Select the modified XML file and click “OK.”

4. Create the list: After importing the modified XML, you will be prompted to create a new list based on the modified template. Fill in the required information, such as the list name and description, and click “Create.”

Conclusion

In conclusion, there are two alternative solutions to creating a SharePoint list from an Excel file with custom column types not supported by the built-in “Create List from XLSX” feature: using an .stp template and modifying the XML of a list. Both methods require some technical knowledge, but they can be powerful tools for creating lists with custom column types. Remember to always use reliable sources when downloading templates and to test your modifications thoroughly before implementing them in your production environment.