Forum Discussion
Importing a List from CSV with Schema
- Actually I figured out the problem. If you open the exported csv in Excel, even if you do not touch the first row (schema), and even if you save it again as a csv, Excel messes up the schema by encapsulating it in quotes and escaping all the internal quotes. I understand why, but it is annoying.
I solved the problem by opening up the exported csv in notepad, removing the schema line and saving it. Then I can make all the adjustments needed in to the data rows in Excel and save the csv, then re-add the schema line to the beginning in notepad. Then, when I import it, everything works.
Easy mistake when I look at it now. But maybe it will help someone else.
Even if I DON'T touch the exported CSV (w/schema) file after I download it, I still get the "Invalid schema provided" error. I grabbed the JSON (schema) from the CSV, put it in a JSON validator, and it does come back as valid JSON.
Even if I create a new List (from Blank), add 1 column, no formatting, no data, no nothing; and then export it as CSV w/schema, I also get the same error when trying when trying to create a new list from it.
I was so excited to see MS finally offered a method to move a List to another Group, but of course it's broken
My Test CSV:
ListSchema={"schemaXmlList":["<Field ID=\"{fa564e0f-0c70-4ab9-b863-0177e6ddd247}\" Type=\"Text\" Name=\"Title\" DisplayName=\"Title\" Required=\"FALSE\" SourceID=\"http://schemas.microsoft.com/sharepoint/v3\" StaticName=\"Title\" FromBaseType=\"TRUE\" MaxLength=\"255\" />","<Field DisplayName=\"TestCol\" Format=\"Dropdown\" IsModern=\"TRUE\" MaxLength=\"255\" Name=\"TestCol\" Title=\"TestCol\" Type=\"Text\" ID=\"{e1cdcf34-c477-4f26-bf3b-b0faa775427d}\" StaticName=\"TestCol\" />","<Field ID=\"{82642ec8-ef9b-478f-acf9-31f7d45fbc31}\" DisplayName=\"Title\" Description=\"\" Name=\"LinkTitle\" SourceID=\"http://schemas.microsoft.com/sharepoint/v3\" StaticName=\"LinkTitle\" Type=\"Computed\" ReadOnly=\"TRUE\" FromBaseType=\"TRUE\" Width=\"150\" DisplayNameSrcField=\"Title\" Sealed=\"FALSE\"><FieldRefs><FieldRef Name=\"Title\" /><FieldRef Name=\"LinkTitleNoMenu\" /><FieldRef Name=\"_EditMenuTableStart2\" /><FieldRef Name=\"_EditMenuTableEnd\" /></FieldRefs><DisplayPattern><FieldSwitch><Expr><GetVar Name=\"FreeForm\" /></Expr><Case Value=\"TRUE\"><Field Name=\"LinkTitleNoMenu\" /></Case><Default><HTML><![CDATA[<div class=\"ms-vb itx\" onmouseover=\"OnItem(this)\" CTXName=\"ctx]]></HTML><Field Name=\"_EditMenuTableStart2\" /><HTML><![CDATA[\">]]></HTML><Field Name=\"LinkTitleNoMenu\" /><HTML><![CDATA[</div>]]></HTML><HTML><![CDATA[<div class=\"s4-ctx\" onmouseover=\"OnChildItem(this.parentNode); return false;\">]]></HTML><HTML><![CDATA[<span> </span>]]></HTML><HTML><![CDATA[<a onfocus=\"OnChildItem(this.parentNode.parentNode); return false;\" onclick=\"PopMenuFromChevron(event); return false;\" href=\"javascript:;\" title=\"Open Menu\"></a>]]></HTML><HTML><![CDATA[<span> </span>]]></HTML><HTML><![CDATA[</div>]]></HTML></Default></FieldSwitch></DisplayPattern></Field>"]}Title,TestCol
Do you have a line break after the end of the schema?
OMG, it was a simple as that.
No, I didn't have a line break after the schema (I checked all the CSV export files I created today, a line break wasn't included on any of them). The exports are all one line when opened as read-only in Notepad++. I figured that was by design.I added a line break, and boom, the import now works.
Great thinking, thanks!Sample new CSV for reference. Hopefully this helps someone else down the road.
ListSchema={"schemaXmlList":["<Field ID=\"{fa564e0f-0c70-4ab9-b863-0177e6ddd247}\" Type=\"Text\" Name=\"Title\" DisplayName=\"Title\" Required=\"FALSE\" SourceID=\"http://schemas.microsoft.com/sharepoint/v3\" StaticName=\"Title\" FromBaseType=\"TRUE\" MaxLength=\"255\" />","<Field DisplayName=\"TestCol\" Format=\"Dropdown\" IsModern=\"TRUE\" MaxLength=\"255\" Name=\"TestCol\" Title=\"TestCol\" Type=\"Text\" ID=\"{e1cdcf34-c477-4f26-bf3b-b0faa775427d}\" StaticName=\"TestCol\" />","<Field ID=\"{82642ec8-ef9b-478f-acf9-31f7d45fbc31}\" DisplayName=\"Title\" Description=\"\" Name=\"LinkTitle\" SourceID=\"http://schemas.microsoft.com/sharepoint/v3\" StaticName=\"LinkTitle\" Type=\"Computed\" ReadOnly=\"TRUE\" FromBaseType=\"TRUE\" Width=\"150\" DisplayNameSrcField=\"Title\" Sealed=\"FALSE\"><FieldRefs><FieldRef Name=\"Title\" /><FieldRef Name=\"LinkTitleNoMenu\" /><FieldRef Name=\"_EditMenuTableStart2\" /><FieldRef Name=\"_EditMenuTableEnd\" /></FieldRefs><DisplayPattern><FieldSwitch><Expr><GetVar Name=\"FreeForm\" /></Expr><Case Value=\"TRUE\"><Field Name=\"LinkTitleNoMenu\" /></Case><Default><HTML><![CDATA[<div class=\"ms-vb itx\" onmouseover=\"OnItem(this)\" CTXName=\"ctx]]></HTML><Field Name=\"_EditMenuTableStart2\" /><HTML><![CDATA[\">]]></HTML><Field Name=\"LinkTitleNoMenu\" /><HTML><![CDATA[</div>]]></HTML><HTML><![CDATA[<div class=\"s4-ctx\" onmouseover=\"OnChildItem(this.parentNode); return false;\">]]></HTML><HTML><![CDATA[<span> </span>]]></HTML><HTML><![CDATA[<a onfocus=\"OnChildItem(this.parentNode.parentNode); return false;\" onclick=\"PopMenuFromChevron(event); return false;\" href=\"javascript:;\" title=\"Open Menu\"></a>]]></HTML><HTML><![CDATA[<span> </span>]]></HTML><HTML><![CDATA[</div>]]></HTML></Default></FieldSwitch></DisplayPattern></Field>"]} Title,TestCol- That's great!
Though annoying how all the different editors do things just a little bit different.Yes, but it's not the editor's fault here. Regardless of how I open it (Notepad, Notepad++, VisualStudio Code), I'm not modifying the CSV. I'm even marking the files read-only before I inspect them.
The SharePoint export process is failing to add the necessary line break between the schema and the contents.