Forum Discussion
Importing a List from CSV with Schema
I am trying to use the out-of-the-box functionality in Lists to export a list as a CSV with Schema, then reimporting the list to another site, but when I try to import the list, it does not recognize the schema. It thinks it is the column header (see screenshot). I only changed the CSV enough to update some data in the list, but did not touch the schema row. Has anyone used this successfully? I even tried it on a brand new site, in case the old site was the issue.
- 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.
- 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- Hey Sam, that's a little different than the problem I was having so I'm not sure.
Do you have a line break after the end of the schema?
- That doesn't maintain the data, it only copies the schema. Microsoft has ostensibly provided a way to do both, just wish it would work.
- Try creating your new list "from an existing list" and selecting your original site/list instead of exporting to CSV first.
