Azure Automation PowerShell read CSV file from SharePoint

Question

Add the moment I am able to read files from our SharePoint with Azure Automation. The problem for me is when I do the import I loss all the property values on the CSV. So the following file has the properties date,time,id,name When I query $GetCSV I get all records on when I query $GetCSV.id it's not showing any data.

#Obtain AccessToken for Microsoft Graph via the managed identity
$headers = Get-AuthToken
$graphurl = "https://graph.microsoft.com/beta"

#Sharepoint parameters
$SharePoint = "%Company%.sharepoint.com"
$SharePointSite = "%Site%"
$ItemPathFile = "file.csv"
$ItemPath = "Apps/Entra%20Automation/$ItemPathFile"

#Get the site from Graph API
$SharePointsitequery = "$graphurl/sites/$($SharePoint):/sites/$($SharePointSite)"
$SiteRequestCall = try{Invoke-RestMethod -Method GET -headers $headers -Uri $SharePointsitequery}catch{Write-output "Error SharePoint SiteId $($_.Exception.Response.StatusCode) $($_.Exception.Response.StatusDescription)"}
$SiteId = $SiteRequestCall.id.split(",")[1]

#Get the file to the documents library
$Querygetfile = "$graphurl/sites/$siteId/drive/items/root:/$($ItemPath):/content"
$GetCSV = try{Invoke-RestMethod -Method GET -Headers $headers -Uri $Querygetfile }catch{Write-output "Error SharePoint Get $($_.Exception.Response.StatusCode) $($_.Exception.Response.StatusDescription)"}

Answer 1

When I query $GetCSV I get all records on when I query $GetCSV.id it's not showing any data.

You can use the below-modified script to get the specific record from the CSV file using Azure automation.

In my share point, my sample CSV file looks like this:

Script:

$headers = Get-AuthToken
$graphurl = "https://graph.microsoft.com/beta"

#Sharepoint parameters
$SharePoint = "%Company%.sharepoint.com"
$SharePointSite = "%Site%"
$ItemPathFile = "file.csv"
$ItemPath = "Apps/Entra%20Automation/$ItemPathFile"

#Get the site from Graph API
$SharePointsitequery = "$graphurl/sites/$($SharePoint):/sites/$($SharePointSite)"
$SiteRequestCall = try{Invoke-RestMethod -Method GET -headers $headers -Uri $SharePointsitequery}catch{Write-output "Error SharePoint SiteId $($_.Exception.Response.StatusCode) $($_.Exception.Response.StatusDescription)"}
$SiteId = $SiteRequestCall.id.split(",")[1]

#Get the file to the documents library
$Querygetfile = "$graphurl/sites/$siteId/drive/items/root:/$($ItemPath):/content"
$GetCSV = try{Invoke-RestMethod -Method GET -Headers $headers -Uri $Querygetfile }catch{Write-output "Error SharePoint Get $($_.Exception.Response.StatusCode) $($_.Exception.Response.StatusDescription)"}


$CSV = $GetCSV | ConvertFrom-Csv -Delimiter ","

# Access the properties of the CSV file
$Numeric = $CSV.Numeric
$Numeric2 = $CSV.'Numeric-2'
$NumericSuffix = $CSV.'Numeric-Suffix'

$Numeric

The above code executed and reads the first column in my Azure automation environment.

Output: