Link to home
Create AccountLog in
Avatar of Jase Alexander
Jase AlexanderFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Powershell SQL Excel Removing inverted commas from column headers on export CSV

HI Experts

Hope you can help


Ive recently wrote a basic Powershell script that extracts table results from our server and exports it to a CSV in one of our folder locations


After examining the CSV file and interrogating it in Notepad++ for compatibility purposes, I discovered that importing into our database caused the column headers to come through with surrounding "" 


For Example, instead of FirstName, it imported as "FirstName"


Normally, we were opening a new excel sheet and formatting it to TEXT, pasting in the results from the SQL table and saving it as a CSV UTF-8 file - when I resent it via this method, the above issues were resolved


Is there a way to insert a line or command to remove any "" from the column headers upon exporting or format the exported file / sheet into TEXT as above to try and simulate the manual process as close as possible?


cls


$serverInstance = 'S1004190'

$database = "mfdata"

$query = "SELECT  [DateStamp]

      ,[PayrollNumber]

      ,[FirstName]

      ,[LastName]

      ,[JobTitle]

      ,[EmployeeManager]

      ,[EmailAddress]

      ,[ContactNumber]

      ,[Location]

      ,[StartDate]

      ,[LeaveDate]

      ,[Discount]

      ,[Gateway]

  FROM [EmployeeData]



$date = Get-Date -Format "yyyyMMdd_HHmm"

$fileName = "EmployeeData"

$csvPath = "\\s1004183\Exports\Employee Data\" + $fileName + $date + ".csv"

$dataTable = Invoke-Sqlcmd -Query $query -Database $database -ServerInstance $serverInstance # Export the DataTable to a CSV file

$dataTable | Export-Csv -Path $csvPath -NoTypeInformation


Write-Host "File exported to" + $csvPath -ForegroundColor Magenta


Any suggestions or assistance would be welcome


Regards

J

Avatar of oBdA
oBdA


importing into our database caused the column headers to come through with surrounding "" 
"importing" means what exactly in this context? No database import tool should have issues with a csv where the cells are enclosed in double quotes.
In other words: it's your import method that needs to be fixed, not your script.
That said, the following will remove the quotes from the first line of the csv (instead of "$dataTable | Export-Csv -Path $csvPath -NoTypeInformation"):
$datatable |
	ConvertTo-Csv -NoTypeInformation |
	ForEach-Object -Begin {$first = $true} -Process {If ($first) {$_.Replace('"', ''); $first = $false} Else {$_}} |
	Set-Content -Path $csvPath -Encoding UTF8

Open in new window

Avatar of Jase Alexander

ASKER

Hi

I totally agree - having looked into the recipient the translation of the file is very generic and without the capability to manipulate in terms of amending the data at source

Thank you for the suggestion - it looks perfect - I shall try this out on the next scheduled run

Many Thanks
J
Hi

Thank you for the above and it performed exactly how I originally requested in my query

However, I believe I may have misconstrued the original feedback as it appears that ALL the information in the file are surrounded by ""

Is there a command to remove all "" in the exported file - not just the column headers?

Regards
J
ASKER CERTIFIED SOLUTION
Avatar of oBdA
oBdA

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account