Eksportér en SharePoint-liste til CSV med PowerShell
Et planlagt PnP PowerShell-script, der henter alle elementer i en liste side for side, vælger felterne ud og skriver en CSV-fil til et fildrev.
Export to Excel i SharePoint stopper ved 5.000 elementer, tager alle kolonnerne med og kræver, at nogen trykker på knappen. Når et andet system skal have en CSV-fil hver dag på et bestemt klokkeslæt, er det et script, der skal gøre det. Det her henter alle elementer i en liste, side for side, lægger de felter, der skal med, i en fast rækkefølge og skriver filen til et fildrev. Det kører som planlagt opgave og logger hvert trin.
Scheduled Task → PowerShell Script → Connect to SharePoint → Get List Items (Paginated) → Parse Fields → Export to CSV → Disconnect → Log Results
Forbindelsen, app-registreringen og den planlagte opgave er de samme som i Upload fra et fildrev til SharePoint Online med PowerShell, og det samme gælder advarslen der: -ClientId med -ClientSecret er ACS-login, og det virker ikke længere. Scriptet skal forbinde med et certifikat:
1Connect-PnPOnline -Url $global:sharePointSite `
2 -ClientId $global:sharepointClient `
3 -Tenant "<tenant>.onmicrosoft.com" `
4 -Thumbprint "<certifikat-thumbprint>"
Appen skal kun læse, så Sites.Read.All rækker — eller Sites.Selected med læseadgang til det ene websted.
Scriptet#
Export-SharePointToCSV.ps1:
1<#
2.SYNOPSIS
3 Automated SharePoint list export to CSV file
4.DESCRIPTION
5 Exports all items from a SharePoint list to CSV with custom field mapping,
6 pagination for large lists, and comprehensive error handling.
7.EXAMPLE
8 .\Export-SharePointToCSV.ps1
9#>
10
11#---------------------------------------------------------------------------------------------------
12# Script Settings
13#---------------------------------------------------------------------------------------------------
14
15# Output Configuration
16$global:outputDir = "\\fileserver\reports" # Network share or local path
17$global:outputCSV = "SharePointExport_$(Get-Date -Format 'yyyyMMdd').csv"
18$global:csvPath = Join-Path $global:outputDir $global:outputCSV
19
20# SharePoint Configuration
21$global:sharePointSite = "https://tenant.sharepoint.com/sites/yoursite"
22$global:sharePointList = "<listenavn>" # List display name
23
24# Azure AD App Authentication
25$global:sharepointClient = "YOUR-CLIENT-ID"
26$global:sharepointClientSecret = "YOUR-CLIENT-SECRET"
27
28# Logging Configuration
29$global:runningFolderPath = Convert-Path "."
30$global:logPath = ""
31$global:keepLogFiles = 10
32
33#---------------------------------------------------------------------------------------------------
34# Logging Functions
35#---------------------------------------------------------------------------------------------------
36
37function Ensure-Structure($date) {
38 <#
39 .SYNOPSIS
40 Creates log directory and manages retention
41 #>
42 try {
43 $global:logPath = "$($global:runningFolderPath)\logs"
44 New-Item -ItemType Directory -Force -Path $global:logPath | Out-Null
45
46 $file = "$($date.ToString("yyyyMMdd")).log"
47 $global:logFilePath = "$($global:logPath)\$($file)"
48
49 # Log rotation
50 $logFiles = Get-ChildItem $global:logPath | Sort-Object FullName -Descending
51 if ($logFiles -ne $null -and $logFiles.Length -gt $global:keepLogFiles) {
52 $exclude = $logFiles | Select-Object -Skip $global:keepLogFiles
53 foreach ($current in $exclude) {
54 try {
55 Remove-Item -Path $current.FullName -Force
56 }
57 catch {
58 Write-Warning "Failed to delete old log: $($current.Name)"
59 }
60 }
61 }
62
63 return $true
64 }
65 catch {
66 Write-Error "Log structure creation failed: $_"
67 return $false
68 }
69}
70
71function Log-Entry([string]$message, [string]$type = "INFO") {
72 <#
73 .SYNOPSIS
74 Writes timestamped log entry to file and console
75 #>
76 try {
77 $type = $type.ToUpper()
78 $logMessage = "$((Get-Date).ToString("yyyy-MM-dd HH:mm:ss")) :: $type :: $message"
79
80 switch ($type) {
81 "ERROR" {
82 Write-Host $logMessage -ForegroundColor Red
83 Add-Content -Path $global:logFilePath -Value $logMessage
84 }
85 "WARNING" {
86 Write-Host $logMessage -ForegroundColor Yellow
87 Add-Content -Path $global:logFilePath -Value $logMessage
88 }
89 "SUCCESS" {
90 Write-Host $logMessage -ForegroundColor Green
91 Add-Content -Path $global:logFilePath -Value $logMessage
92 }
93 default {
94 Write-Host $logMessage -ForegroundColor White
95 Add-Content -Path $global:logFilePath -Value $logMessage
96 }
97 }
98 }
99 catch {
100 Write-Error "Failed to write log: $_"
101 }
102}
103
104#---------------------------------------------------------------------------------------------------
105# SharePoint Connection
106#---------------------------------------------------------------------------------------------------
107
108function Get-SharePoint-Connection {
109 <#
110 .SYNOPSIS
111 Connects to SharePoint Online using app-only authentication
112 #>
113 try {
114 Log-Entry "Connecting to SharePoint: $($global:sharePointSite)" "INFO"
115
116 Connect-PnPOnline -Url $global:sharePointSite `
117 -ClientId $global:sharepointClient `
118 -ClientSecret $global:sharepointClientSecret
119
120 $web = Get-PnPWeb
121
122 if ($web -ne $null) {
123 Log-Entry "Connected successfully to: $($web.Url)" "SUCCESS"
124 return $true
125 }
126 else {
127 Log-Entry "Connection failed - web object is null" "ERROR"
128 Disconnect-PnPOnline
129 return $false
130 }
131 }
132 catch {
133 Log-Entry "SharePoint connection error: $($_.Exception.Message)" "ERROR"
134 return $false
135 }
136}
137
138function Disconnect-SharePoint-Connection {
139 <#
140 .SYNOPSIS
141 Disconnects from SharePoint Online
142 #>
143 try {
144 Disconnect-PnPOnline -ErrorAction SilentlyContinue
145 }
146 catch {
147 # Ignore disconnect errors
148 }
149}
150
151#---------------------------------------------------------------------------------------------------
152# Data Export Functions
153#---------------------------------------------------------------------------------------------------
154
155function Get-Items-From-SharePoint {
156 <#
157 .SYNOPSIS
158 Retrieves all items from SharePoint list with pagination
159 #>
160 try {
161 Log-Entry "Retrieving items from list: $($global:sharePointList)" "INFO"
162
163 $parsedItems = @()
164
165 # Get list
166 $spList = Get-PnPList -Identity $global:sharePointList -ErrorAction SilentlyContinue
167
168 if ($spList -eq $null) {
169 Log-Entry "List not found: $($global:sharePointList)" "ERROR"
170 return @{
171 Status = 404
172 Message = "List not found"
173 Content = @()
174 }
175 }
176
177 # Get items with pagination (500 items per batch)
178 $items = Get-PnPListItem -List $spList -PageSize 500 -ScriptBlock {
179 Param($items)
180 $items.Context.ExecuteQuery()
181 }
182
183 Log-Entry "Retrieved $($items.Count) items from list" "INFO"
184
185 # Parse each item
186 foreach ($item in $items) {
187 $parsed = Parse-Item-From-SharePoint $item
188
189 if ($parsed.Status -eq 200) {
190 $parsedItems += $parsed.Content
191 }
192 else {
193 Log-Entry "Failed to parse item ID $($item['ID']): $($parsed.Message)" "WARNING"
194 }
195 }
196
197 Log-Entry "Successfully parsed $($parsedItems.Count) items" "SUCCESS"
198
199 return @{
200 Status = 200
201 Message = "Success"
202 Content = $parsedItems
203 }
204 }
205 catch {
206 Log-Entry "Error retrieving items: $($_.Exception.Message)" "ERROR"
207 return @{
208 Status = 500
209 Message = $_.Exception.Message
210 Content = @()
211 }
212 }
213}
214
215function Parse-Item-From-SharePoint($item) {
216 <#
217 .SYNOPSIS
218 Parses SharePoint list item into custom object with selected fields
219 #>
220 try {
221 # Custom field mapping - MODIFY THIS SECTION FOR YOUR LIST
222 $data = [PSCustomObject]@{
223 ID = $item['ID']
224 Title = $item['Title']
225 Customer = $item['Customer']
226 Email = $item['Email']
227 MailSubject = $item['MailSubject']
228 Created = $item['Created']
229 Modified = $item['Modified']
230 # Add more fields as needed:
231 # Status = $item['Status']?['Value'] # Choice field
232 # AssignedTo = $item['AssignedTo']?['Email'] # Person field
233 }
234
235 return @{
236 Status = 200
237 Message = "Success"
238 Content = $data
239 }
240 }
241 catch {
242 return @{
243 Status = 500
244 Message = $_.Exception.Message
245 Content = $null
246 }
247 }
248}
249
250function Export-To-CSV($items) {
251 <#
252 .SYNOPSIS
253 Exports items to CSV file with UTF-8 encoding
254 #>
255 try {
256 Log-Entry "Exporting $($items.Count) items to: $($global:csvPath)" "INFO"
257
258 # Ensure output directory exists
259 $outputDirectory = Split-Path $global:csvPath -Parent
260 if (-not (Test-Path $outputDirectory)) {
261 New-Item -ItemType Directory -Path $outputDirectory -Force | Out-Null
262 }
263
264 # Export to CSV
265 $items | Export-Csv -Path $global:csvPath -NoTypeInformation -Encoding UTF8 -Force
266
267 # Verify file created
268 if (Test-Path $global:csvPath) {
269 $fileSize = (Get-Item $global:csvPath).Length / 1KB
270 Log-Entry "Export successful: $([math]::Round($fileSize, 2)) KB" "SUCCESS"
271 return $true
272 }
273 else {
274 Log-Entry "CSV file not created" "ERROR"
275 return $false
276 }
277 }
278 catch {
279 Log-Entry "Export error: $($_.Exception.Message)" "ERROR"
280 return $false
281 }
282}
283
284#---------------------------------------------------------------------------------------------------
285# Main Process
286#---------------------------------------------------------------------------------------------------
287
288function Start-Process {
289 <#
290 .SYNOPSIS
291 Main orchestration function
292 #>
293 try {
294 Log-Entry "-" * 100 "INFO"
295 Log-Entry "Export process started" "INFO"
296 Log-Entry "Source: $($global:sharePointSite) - List: $($global:sharePointList)" "INFO"
297 Log-Entry "Destination: $($global:csvPath)" "INFO"
298
299 # Connect to SharePoint
300 $connected = Get-SharePoint-Connection
301 if ($connected -eq $false) {
302 return $false
303 }
304
305 # Get items
306 $response = Get-Items-From-SharePoint
307
308 if ($response.Status -eq 200) {
309 # Export to CSV
310 $exported = Export-To-CSV $response.Content
311 Disconnect-SharePoint-Connection
312 return $exported
313 }
314 else {
315 Log-Entry "Failed to retrieve items: $($response.Message)" "ERROR"
316 Disconnect-SharePoint-Connection
317 return $false
318 }
319 }
320 catch {
321 Log-Entry "Process error: $($_.Exception.Message)" "ERROR"
322 Disconnect-SharePoint-Connection
323 return $false
324 }
325 finally {
326 Log-Entry "Export process completed" "INFO"
327 Log-Entry "-" * 100 "INFO"
328 Disconnect-SharePoint-Connection
329 }
330}
331
332#---------------------------------------------------------------------------------------------------
333# Execution
334#---------------------------------------------------------------------------------------------------
335
336$ensured = Ensure-Structure (Get-Date)
337if ($ensured -eq $false) {
338 Write-Error "Failed to ensure log structure"
339 exit 1
340}
341
342$result = Start-Process
343if ($result -eq $false) {
344 Write-Error "Export process failed - check logs: $($global:logFilePath)"
345 exit 1
346}
347
348Write-Host "Export completed successfully" -ForegroundColor Green
349exit 0
Opsætning#
Output på et fildrev eller lokalt, med dato i filnavnet eller uden:
1$global:outputDir = "\\fileserver\reports"
2$global:outputCSV = "SharePointExport_$(Get-Date -Format 'yyyyMMdd').csv"
1$global:outputDir = "D:\Exports\SharePoint"
2$global:outputCSV = "ListExport.csv"
1# Include date and time
2$global:outputCSV = "Export_$(Get-Date -Format 'yyyyMMdd_HHmmss').csv"
3
4# Include list name
5$global:outputCSV = "$($global:sharePointList)_$(Get-Date -Format 'yyyyMMdd').csv"
Webstedet og listen. Listen angives med visningsnavnet:
1$global:sharePointSite = "https://contoso.sharepoint.com/sites/Finance"
1$global:sharePointList = "<listenavn>" # Use display name, not internal name
1$global:sharepointClient = "<klient-id>"
2$global:sharepointClientSecret = "YOUR-SECRET-HERE"
Felterne#
Det er Parse-Item-From-SharePoint, der bestemmer, hvilke kolonner CSV-filen får. Et fyldigere eksempel:
1function Parse-Item-From-SharePoint($item) {
2 try {
3 # Map SharePoint fields to CSV columns
4 $data = [PSCustomObject]@{
5 # Text fields
6 ID = $item['ID']
7 Title = $item['Title']
8 Description = $item['Description']
9
10 # Choice fields (single selection)
11 Status = $item['Status']?.Value
12 Priority = $item['Priority']?.Value
13
14 # Person fields
15 CreatedBy = $item['Author']?.Email
16 AssignedTo = $item['AssignedTo']?.DisplayName
17
18 # Date fields
19 DueDate = $item['DueDate']
20 Created = $item['Created']
21 Modified = $item['Modified']
22
23 # Number fields
24 Amount = $item['Amount']
25 Quantity = $item['Quantity']
26
27 # Yes/No fields
28 IsActive = $item['IsActive']
29
30 # Lookup fields
31 Project = $item['Project']?.LookupValue
32 Category = $item['Category']?.LookupValue
33 }
34
35 return @{
36 Status = 200
37 Content = $data
38 }
39 }
40 catch {
41 return @{
42 Status = 500
43 Message = $_.Exception.Message
44 }
45 }
46}
Hvordan hver felttype læses:
| Felttype | Sådan læses den | Eksempel |
|---|---|---|
| En linje tekst | $item['FieldName'] | $item['Title'] |
| Flere linjer tekst | $item['FieldName'] | $item['Description'] |
| Valg (ét) | $item['FieldName'] | $item['Status'] |
| Valg (flere) | $item['FieldName'] -join '; ' | |
| Person (én) | $item['FieldName'].Email | $item['Author'].Email |
| Person (flere) | løkke over samlingen | se herunder |
| Dato og tid | $item['FieldName'] | $item['DueDate'] |
| Tal | $item['FieldName'] | $item['Amount'] |
| Ja/nej | $item['FieldName'] | $item['IsActive'] |
| Opslag (ét) | $item['FieldName'].LookupValue | $item['Project'].LookupValue |
| Hyperlink | $item['FieldName'].Url | $item['Link'].Url |
Et valgfelt kommer ud som en almindelig tekst, ikke som et objekt med .Value. En person er en FieldUserValue med .Email og .LookupValue (visningsnavnet); .DisplayName findes ikke.
Flere personer i ét felt, samlet til én kolonne:
1# Get all assigned users as semicolon-separated emails
2$assignedUsers = @()
3if ($item['AssignedTo'] -ne $null) {
4 foreach ($user in $item['AssignedTo']) {
5 $assignedUsers += $user.Email
6 }
7}
8$data.AssignedTo = $assignedUsers -join '; '
Kør det i hånden og se efter:
1# Navigate to script directory
2cd "D:\Scripts\SharePointExport"
3
4# Run script manually
5.\Export-SharePointToCSV.ps1
6
7# Check log
8Get-Content ".\logs\$(Get-Date -Format 'yyyyMMdd').log"
9
10# Verify CSV created
11Test-Path $global:csvPath
12Import-Csv $global:csvPath | Select-Object -First 5
Antallet af rækker skal passe med antallet af elementer i listen, valgfelter skal vise teksten og ikke et id, og æ, ø og å skal se rigtige ud, når filen åbnes i Excel.
Den planlagte opgave: SharePoint CSV Export, dagligt kl. 17.30, pwsh.exe med argumenterne -ExecutionPolicy Bypass -File "D:\Scripts\SharePointExport\Export-SharePointToCSV.ps1" og Start in D:\Scripts\SharePointExport, run whether user is logged on or not.
Varianter#
Kun det seneste døgn med en CAML-forespørgsel:
1function Get-Items-From-SharePoint {
2 try {
3 # Filter for items created in last 24 hours
4 $yesterday = (Get-Date).AddDays(-1).ToString("yyyy-MM-dd")
5 $camlQuery = "<View><Query><Where><Geq><FieldRef Name='Created'/><Value Type='DateTime'>$yesterday</Value></Geq></Where></Query></View>"
6
7 $items = Get-PnPListItem -List $global:sharePointList -Query $camlQuery
8
9 # Or use simpler filtering:
10 # $items = Get-PnPListItem -List $global:sharePointList | Where-Object {
11 # $_.FieldValues.Status -eq "Active"
12 # }
13
14 # Parse items...
15 }
16 catch {
17 # Error handling
18 }
19}
Flere lister, hver til sin fil:
1$listsToExport = @(
2 @{Name="<listenavn>"; FileName="<listenavn>.csv"},
3 @{Name="Invoices"; FileName="Invoices.csv"},
4 @{Name="Customers"; FileName="Customers.csv"}
5)
6
7foreach ($listConfig in $listsToExport) {
8 $global:sharePointList = $listConfig.Name
9 $global:outputCSV = $listConfig.FileName
10 $global:csvPath = Join-Path $global:outputDir $global:outputCSV
11
12 Log-Entry "Exporting list: $($listConfig.Name)" "INFO"
13 $result = Start-Process
14}
Kun det, der er ændret siden sidst. Tidspunktet for sidste kørsel gemmes i en fil ved siden af scriptet:
1# Store last export timestamp
2$lastExportFile = "$($global:runningFolderPath)\last-export.txt"
3
4if (Test-Path $lastExportFile) {
5 $lastExport = Get-Content $lastExportFile
6}
7else {
8 $lastExport = (Get-Date).AddYears(-10).ToString("yyyy-MM-ddTHH:mm:ssZ")
9}
10
11# Filter items
12$camlQuery = "<View><Query><Where><Geq><FieldRef Name='Modified'/><Value Type='DateTime'>$lastExport</Value></Geq></Where></Query></View>"
13$items = Get-PnPListItem -List $global:sharePointList -Query $camlQuery
14
15# After successful export, update timestamp
16(Get-Date).ToString("yyyy-MM-ddTHH:mm:ssZ") | Set-Content $lastExportFile
Send filen med mail:
1function Send-Export-Email($csvPath, $itemCount) {
2 Send-MailMessage `
3 -From "reports@company.com" `
4 -To "finance@company.com" `
5 -Subject "SharePoint Export: $itemCount items exported" `
6 -Body "Automated export completed successfully.`n`nFile: $csvPath`nItems: $itemCount" `
7 -Attachments $csvPath `
8 -SmtpServer "smtp.company.com"
9}
10
11# Call after successful export
12if ($result -eq $true) {
13 Send-Export-Email $global:csvPath $response.Content.Count
14}
Kryptér filen, når den indeholder personoplysninger. Kræver et certifikat til dokumentkryptering i brugerens lager:
1# Encrypt CSV file after export
2function Protect-CSVFile($filePath) {
3 $cert = Get-ChildItem Cert:\CurrentUser\My | Where-Object { $_.Subject -like "*Encryption*" }
4
5 $content = Get-Content $filePath -Raw
6 $encryptedContent = Protect-CmsMessage -To $cert -Content $content
7 $encryptedContent | Set-Content "$filePath.encrypted"
8
9 Remove-Item $filePath -Force # Delete unencrypted file
10}
Slet gamle eksporter efter 90 dage:
1# Delete exports older than 90 days (GDPR compliance)
2$exports = Get-ChildItem $global:outputDir -Filter "*.csv"
3foreach ($export in $exports) {
4 if ($export.LastWriteTime -lt (Get-Date).AddDays(-90)) {
5 Remove-Item $export.FullName -Force
6 Log-Entry "Deleted old export: $($export.Name)" "INFO"
7 }
8}
Kun de felter, der faktisk skal bruges, kommer med. E-mailadresser og navne i en CSV-fil på et fildrev er personoplysninger, der ligger uden for SharePoints rettigheder.
Fejlfinding#
“List ‘ListName’ does not exist”. Forkert navn. Slå listerne op:
1# List all lists in site
2Connect-PnPOnline -Url $siteUrl -Interactive
3Get-PnPList | Select-Object Title, Id | Format-Table
4
5# Use exact display name (case-sensitive)
6$global:sharePointList = "<listenavn>" # Not "<LISTENAVN>"
“Cannot access path ‘\fileserver\reports’”. Drevet kan ikke nås, eller kontoen må ikke skrive:
1# Test path access
2Test-Path "\\fileserver\reports"
3
4# Check write permissions
5New-Item -Path "\\fileserver\reports\test.txt" -ItemType File -Force
6Remove-Item "\\fileserver\reports\test.txt" -Force
Æ, ø og å er gået i stykker. Excel vil have en BOM for at læse UTF-8:
1# Ensure UTF-8 encoding with BOM
2$items | Export-Csv -Path $csvPath -NoTypeInformation -Encoding UTF8
3
4# Or use UTF-8 with BOM explicitly
5$utf8BOM = New-Object System.Text.UTF8Encoding $true
6[System.IO.File]::WriteAllLines($csvPath, ($items | ConvertTo-Csv -NoTypeInformation), $utf8BOM)
“Exception calling ‘ExecuteQuery’”. Throttling. Mindre sider og en pause:
1# Reduce page size
2$items = Get-PnPListItem -List $spList -PageSize 100 -ScriptBlock {
3 Param($items)
4 $items.Context.ExecuteQuery()
5 Start-Sleep -Milliseconds 500 # Throttle protection
6}
CSV-filen er tom, eller kolonnerne er tomme. Feltnavnene i Parse-Item-From-SharePoint er forkerte. Det er de interne navne, der gælder:
1# Get actual field internal names
2Get-PnPField -List $global:sharePointList | Select-Object InternalName, Title | Format-Table
3
4# Use internal names in parsing:
5$data.CustomerName = $item['Customer_x0020_Name'] # Internal name for "Customer Name"
Det der driller#
- Klienthemmeligheden er ACS og virker ikke efter 2. april 2026. Certifikat, se øverst.
Export-Csv -Encoding UTF8skriver en BOM i Windows PowerShell 5.1, men ikke i PowerShell 7, hvorUTF8betyder uden BOM. I PowerShell 7 skal det være-Encoding utf8BOM, ellers viser Excel æ, ø og å forkert.- Dansk Excel vil have semikolon.
Export-Csvskriver komma, og så lander hele rækken i kolonne A.-Delimiter ';'eller-UseCultureløser det — medmindre filen skal læses af et system, der forventer komma. ?.findes kun i PowerShell 7.1 og nyere. Mappingen med$item['Status']?.Valuefejler i 5.1, og.Valueer i øvrigt forkert for et valgfelt (se tabellen).-ScriptBlock { $items.Context.ExecuteQuery() }gør intet nyttigt her.-PageSizesørger selv for at hente side for side. Scriptblokken er til at gemme ændringer pr. side og koster et ekstra kald.-Queryslår-PageSizefra. En CAML-forespørgsel henter det, forespørgslen siger. På en liste med over 5.000 elementer skal der<RowLimit Paged="TRUE">med i forespørgslen, og feltet i<Where>skal være indekseret, ellers rammer den grænsen.- Delta-tidsstemplet lyver om tidszonen.
(Get-Date).ToString("yyyy-MM-ddTHH:mm:ssZ")er lokal tid med etZbagpå, altså en eller to timer forkert.(Get-Date).ToUniversalTime()først. CAML ser desuden bort fra klokkeslættet, medmindre<Value>harIncludeTimeValue='TRUE'. Log-Entry "-" * 100 "INFO"skriver ikke en streg, og funktionenStart-Processskygger for PowerShells egen. Samme to fejl som i upload-scriptet.Send-MailMessageer forældet og kan ikke logge ind mod Exchange Online med moderne godkendelse.