Forum Discussion

KrisSub2023's avatar
KrisSub2023
Brass Contributor
Jul 26, 2023

Powershell-Read multiple csv files available in SharePoint online document library and Merge all

Powershell-Read multiple csv files available in SharePoint online document library and Merge all of them to a different folder.

 

Hello,Can someone help with the powershell script which reads all the csv files from document library and merge all of them into a single file via powershell-pnp commands ? I'm not sure about reading the file from document library.Please help

powershell
SharePoint Online
  • LeonPavesic's avatar
    LeonPavesic
    Silver Contributor
    Jul 26, 2023

    Hello KrisSub2023,

    This PowerShell script should help you merge multiple CSV files stored in a SharePoint Online document library into a single CSV file.

     

    Before running the script, make sure you have the SharePoint PnP PowerShell module installed. You can install it using the command: Install-Module SharePointPnPPowerShellOnline -Force

    You can also download SharePoint Online Management to execute the PowerShell Script:
    Download SharePoint Online Management Shell from Official Microsoft Download Center

    Here's the example PowerShell script you can then adapt and use in your environment:


    # Connect to SharePoint Online
    Connect-PnPOnline -Url "https://your-sharepoint-site-url" -UseWebLogin

    # Set the document library and target folder path
    $documentLibraryName = "YourDocumentLibraryName"
    $targetFolderPath = "C:\Path\To\Target\Folder"

    # Get all CSV files from the document library
    $csvFiles = Get-PnPListItem -List $documentLibraryName | Where-Object { $_.File.Name -like "*.csv" }

    # Initialize an empty array to store content from all CSV files
    $mergedContent = @()

    # Read content from each CSV file and store it in the $mergedContent array
    foreach ($file in $csvFiles) {
    $fileContent = Get-PnPFile -Url $file.FieldValues.FileRef -AsString
    $mergedContent += $fileContent
    }

    # Create the merged CSV file in the target folder
    $mergedFilePath = Join-Path $targetFolderPath "MergedFile.csv"
    $mergedContent | Out-File -FilePath $mergedFilePath -Encoding UTF8

    # Disconnect from SharePoint Online
    Disconnect-PnPOnline

    Write-Host "All CSV files have been merged into $mergedFilePath."


    Replace `"https://your-sharepoint-site-url"` with your SharePoint Online site URL and `"YourDocumentLibraryName"` with the name of your document library. Also, set `"C:\Path\To\Target\Folder"` to the location where you want the merged CSV file to be saved.



    Please click Mark as Best Response & Like if my post helped you to solve your issue.
    This will help others to find the correct solution easily. It also closes the item.


    If the post was useful in other ways, please consider giving it Like.


    Kindest regards,


    Leon Pavesic

    • KrisSub2023's avatar
      KrisSub2023
      Brass Contributor
      Jul 26, 2023

      How can i save my output csv (merged file)in sharepoint library folder? how can i remove the duplicate header 

      • LeonPavesic's avatar
        LeonPavesic
        Silver Contributor
        Jul 26, 2023

        Hi KrisSub2023,

        To save the merged CSV file directly to a SharePoint Online document library folder, you can try to use this modified script:


        # Connect to SharePoint Online
        Connect-PnPOnline -Url "https://your-sharepoint-site-url" -UseWebLogin

        # Set the document library and target folder path
        $documentLibraryName = "YourDocumentLibraryName"
        $targetFolderPath = "/sites/YourSite/YourDocumentLibraryName/FolderName" # Update with the desired folder path

        # Get all CSV files from the document library
        $csvFiles = Get-PnPListItem -List $documentLibraryName | Where-Object { $_.File.Name -like "*.csv" }

        # Initialize an empty array to store content from all CSV files
        $mergedContent = @()

        # Read content from each CSV file and store it in the $mergedContent array
        foreach ($file in $csvFiles) {
        $fileContent = Get-PnPFile -Url $file.FieldValues.FileRef -AsString
        $mergedContent += $fileContent
        }

        # Create the merged CSV file in the target folder
        $mergedFilePath = Join-Path $targetFolderPath "MergedFile.csv"
        $mergedContent | Out-File -FilePath $mergedFilePath -Encoding UTF8

        # Upload the merged CSV file to SharePoint document library folder
        Add-PnPFile -Path $mergedFilePath -Folder $targetFolderPath -List $documentLibraryName

        # Disconnect from SharePoint Online
        Disconnect-PnPOnline

        Write-Host "All CSV files have been merged and saved to $mergedFilePath in the SharePoint document library folder."

        In this modified script, you need to update the `$targetFolderPath` variable with the desired folder path in the SharePoint document library where you want to save the merged CSV file. The `$targetFolderPath` variable should be set to a relative URL starting with `/sites/` followed by your site name, document library name, and the folder name where you want to save the file.

         

        Please click Mark as Best Response & Like if my post helped you to solve your issue.
        This will help others to find the correct solution easily. It also closes the item.


        If the post was useful in other ways, please consider giving it Like.


        Kindest regards,


        Leon Pavesic

Resources