Automatically Update List with .csv or XML

Question

I would like to pull data from a .csv or XML file that is exported daily from an ARP system to a SharePoint 2013 document library. I would like to automatically update a SharePoint list with the data from this file on a daily basis. And I would like to do this using Designer if possible. Would Data Sources in Designer work?

Answer 1

Your question divided to two section

1. Run something at daily basis.
2. Update list item from an XML file.

To run something on daily basis you have two option

1. Create a custom SharePoint timer job.
2. Create Power shell script that will run via Task Schedule.

Note : you can also use the loop in SharePoint designer to run daily basis but I think you need to add XML file as a data source as mentioned here. (I have never tried this).

To insert items into specific list from an XML file you can run this script (Ref)

Add-PsSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue -WarningAction SilentlyContinue | Out-Null 
[System.Xml.XmlDocument] $XmlDoc = new-object System.Xml.XmlDocument
$path = "<path to xml>"
$XmlDoc = [xml](Get-Content $path)

$url = "<url web>"
$listTitle = "<list title>" 
$web = Get-SPWeb $url
$list = $web.Lists.TryGetList($listTitle)
if ( $list -ne $null)
    {

    $XmlDoc.gesmes.Cube.Cube | % {
        $cube = $_
        $cubeTime = $cube.time
        $cube.Cube | % {
            $cubeEntry = $_
            $currency = $cubeEntry.currency
            $rate =  $cubeEntry.rate

            $item  =$list.AddItem();
            $item["<CurrencyField>"] = $currency
            $item["<RateField>"] = $rate
            $item.Update()

        }
    }
}

You can also use Server-side object model via C# to read XML file and update your list as the following

1. Read XMl file and save it at data table.

DataSet ds = new DataSet();
ds.ReadXml("XmlFilePath");
DataTable dt = ds.Tables[0];

2. loop using for each to each item within your data table then add or update it to your list.

    using (SPSite oSite=new SPSite("http://mysharepoint"))
    {
        using (SPWeb oWeb=oSite.RootWeb)
        {
            SPList oList = oWeb.Lists["Test"];
            SPListItem oSPListItem = oList.Items.Add();
            oSPListItem["Title"] = "Hello SharePoint";
            oSPListItem.Update();
        }  
}