Desired Behaviour
Export a SharePoint list to a CSV or Excel file in a format that can be imported back into SharePoint as a list with all Column types and values correctly formatted.
The reason this functionality is required is to ensure that any backed up SharePoint lists can be restored quickly and easily.
Current Behaviour
Importing a file that was created by clicking Export to Excel from a SharePoint list (which prompts you to download an .iqy file, which when opened can be saved as an .xlsx file) results in a list where Column types are incorrect and values are not formatted correctly.
What I've Tried
Following instructions in the following article, it should be possible to create a SharePoint list from the exported Excel document:
Create a list based on a spreadsheet
The article has this caveat however:
Note: Typically, the columns are set up on the SharePoint site based on the type of data that they contain. After you import a list, however, you should inspect the columns and data to make sure that everything was imported as you expected. For example, you may want to specify that a column contains currency rather than just a number. To view or change the list settings, open the list, and on the Settings menu, select List Settings.
Specifically, these are the results that I get (the exclamation mark ! indicates wrong Column type):
+--------------------------------------+------------------------+------------------------+
| Column | Original Type | Imported Type |
+--------------------------------------+------------------------+------------------------+
| Title | Single line of text | Single line of text |
| MySingleLineOfTextColumn | Single line of text | Single line of text |
| MyMultipleLinesOfTextColumn | Multiple lines of text | Multiple lines of text |
| MyLocationColumn | Location | Single line of text ! |
| MyNumberColumn | Number | Number |
| MyYesNoColumn | Yes/No | Yes/No |
| MyPersonColumn | Person or Group | Single line of text ! |
| MyDateColumn | Date and Time | Date and Time |
| MyChoiceColumnMultiWithSelections | Choice | Single line of text ! |
| MyChoiceColumnMultiWithoutSelections | Choice | Single line of text ! |
| MyChoiceColumnSingleWithSelection | Choice | Single line of text ! |
| MyChoiceColumnSingleWithoutSelection | Choice | Single line of text ! |
| MyHyperlinkColumn | Hyperlink or Picture | Hyperlink or Picture |
| MyPictureColumn | Hyperlink or Picture | Hyperlink or Picture |
| MyCurrencyColumn | Currency | Currency |
| Modified | Date and Time | Date and Time |
| Created | Date and Time | Date and Time |
| Created By | Person or Group | Person or Group |
| Modified By | Person or Group | Person or Group |
+--------------------------------------+------------------------+------------------------+
So the following Columns have the incorrect type:
+--------------------------------------+------------------------+------------------------+
| Column | Original Type | Imported Type |
+--------------------------------------+------------------------+------------------------+
| MyLocationColumn | Location | Single line of text ! |
| MyPersonColumn | Person or Group | Single line of text ! |
| MyChoiceColumnMultiWithSelections | Choice | Single line of text ! |
| MyChoiceColumnMultiWithoutSelections | Choice | Single line of text ! |
| MyChoiceColumnSingleWithSelection | Choice | Single line of text ! |
| MyChoiceColumnSingleWithoutSelection | Choice | Single line of text ! |
+--------------------------------------+------------------------+------------------------+
Strangely, Person or Group works when they are values automatically added by SharePoint, ie the Created By and Modified By columns, but not when I have searched for and selected a User in a Person or Group column.
In the List Settings area, if I change the Column type of all the columns that should be Choice columns to Choice (with relevant settings of 'Checkboxes ( allow multiple selections)', and I need to manually define all available options), then the values in the list frontend appear correctly.
So what was displayed at Choice1;#Choice2;#Choice3 is now displayed as Choice1, Choice2, Choice3 and the relevant selections are 'checked' when editing the items.
It is not possible to change the Column type of the columns that should have types of Location and Person or Group, however, because they are both now Single line of text type.
Question
What do all column type values need to be in order for them to import into SharePoint correctly?
Or, perhaps more specifically, how should the values be exported in order for them to be imported back into SharePoint correctly?
PS...
I noticed when exporting the list, and opening the resulting .iqy file, I get the message:
The table contains a string that is too long for excel to display. If you edit the long value some data might be lost.
So therefore the Location column value is truncated, how does one get around that issue when wanting to back up content that includes a Location?
