Powershell Import Data from csv with special characters

Often companies using Excel to work on different data and information. Sometimes these data is exported from a external system like SAP or other line of business applications. A way to work together on those data is using office webapps or a normal SharePoint list. A SharePoint list has the advantage that you can add some more columns for some notes or status notes. This was one of the request at a customer who wants to import data from a Excel file in a monthly intervall.

For a small demo ho it works we used a Powershell Script for that, which imports the data. But first we save the Excel as csv.

The first problem was, that the special characters in german (ä,ö,ü) were not transformed correctly. So we did a small trick in Powershell. First we get the content and saved this content in another csv file with encoding to utf8.

After that we get this new csv file and read it into a Dataset with Encoding to unicode. Now we have the dataset and can iterate through the data, add or update the data or whatever you like to do with the data.



Add-PSSnapin Microsoft.SharePoint.Powershell -ErrorAction SilentlyContinue

############## PARAMETERS ####################
$WebUrl = "sitecollectionurl"
$Listname = "Listname"

#Convert CSV to UTF-8 with special characters
Get-Content D:\Folder\myCSV.csv | Out-File D:\Folder\myCSV_utf8.csv -Encoding utf8

#Get the CSV file and connect to the SharePoint list
$DataSet = import-csv -Delimiter ";" -Path "D:\Folder\myCSV_utf8.csv" -Encoding Unicode

######### Add to SPList ########

$spWeb = Get-SPWeb -identity $WebUrl  #  Get SPWeb
$list = $spWeb.Lists[$Listname] # Get SPList

for($i = 0; $i -le $DataSet.Count; $i++)
{
#Read current row
$row = $DataSet[$i]

$item = $list.Items.Add();

$item["SPColumn1"] = $row.'Column1'
$item["SPColumn2"] = $row.'Column2'
$item["SPColumn3"] = $row.'Column3'
$item["SPColumn_n"] = $row.'Column_n'

$item.Update()
}


Hope this helps you.

 

The article or information provided here represents completely my own personal view & thought. It is recommended to test the content or scripts of the site in the lab, before making use in the production environment & use it completely at your own risk. The articles, scripts, suggestions or tricks published on the site are provided AS-IS with no warranties or guarantees and confers no rights.

Karsten Pohnke About Karsten Pohnke
He is Consultant for SharePoint Solutions for collaboration, communication and business processes. He provides his customers applications based on standard features as well as development or combining the power of several microsoft tools like Dynamics CRM. In his free time he tries to collect tipps and worthy experience in this blog.

2 comments on “Powershell Import Data from csv with special characters

  1. Sorry but this doesn’t work.
    Only a bunch of red for everything that is in the CSV file

Submit comment

Allowed HTML tags: <a href="http://google.com">google</a> <strong>bold</strong> <em>emphasized</em> <code>code</code> <blockquote>
quote
</blockquote>

Please fill in the captcha: * Time limit is exhausted. Please reload CAPTCHA.