Forum Discussion
Solved
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.
What is the exact error are you getting?
PowerShell has no issues with paths containing spaces, so with no error to refer to, I can't seen any issue.
I'd also point out that your final example isn't going to work. You can use double quotes or single quotes, but not both together as you have done in that example.
Double quotes are useful where you intend to use variables (and other special characters) inside of a string - as you have done with "$size MB", while single quotes are used where all PowerShell-specific special characters must be ignored.
What your second example is telling PowerShell to do is look for a single file in the current working directory that has an actual filename (not a path) of 'E:\MyDocuments\!Onedrive\OneDrive - Specified Nonprofit Organization Sample Japan\Sample.pdf', which isn't a valid filename.
Here's some examples of appropriate (first two commands) and inappropriate (final command) quote usage as well as a path containing spaces.
Cheers,
Lain
12 Replies
- 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.
