I have a csv file which is comma delimited but it has some address field with commas in them and a description field which starts and ends with double quotes and has commas within it. Import wizrad of sql server just adds in extra columns and therefore errors on the data type for other fields which this data is extending into. If I use excel the field is correctly seen and I can therefore import it but I want to automate this import so dont want to have to open each file (of which I have thousands )into in excel.
I have tried powershell
DECLARE @file NVARCHAR(250);
DECLARE @cmd NVARCHAR(255);
SET @cmd = 'powershell "Import-csv -path '+@file+'.csv -Delimiter '','' | Export-CSV -path '+@file+'_converted.csv -Delimiter ''|'' -NoType " '
but get the error
Import-Csv : A positional parameter cannot be found that accepts argument
At line:1 char:1
+ Import-csv -path C:\DWfilestoload\Collect Premium Insolvency Output.csv
+ CategoryInfo : InvalidArgument: (:) [Import-Csv], ParameterBind
+ FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.PowerShell
Any other ideas to get this file in a good format for import or to swap the delimiter, I have tried replacing the , for a | but its the same effect.