and something I've noticed - your SQL settings are default ones - you should change them - maxdop normally should not be set to zero, and Cost Threshold for Parallelism...

the issue should be clearer even for someone not experienced.

there is no index on the FIRST_NM - so for each update it needs to do a table scan.

add a index on...

That is a very ugly file. Guessing it came from a mainframe?

It may be faster and more supportable to learn some python to preclean and format the file than...

as Brian said you should not be trying to convert as is but rather understand the requirements and do fresh and better code targetting SQL Server.

for the particular error  you...

• This reply was modified 2 years ago by frederico_fonseca.

you better give us your .ps1 file - and if using powershell you really should be outputting a valid CSV file instead of dumping the output of the command into...

you can't - googling show you lots of threads on that and the answer is always the same.

depending on what you are trying to accomplish there are ways to bypass...

AV blocking powershell

Access to that networkshare not set correctly (both to the location of the csv file and to the PS script itself.)

you can check it by using a using...

reason why you got "double quotes" is because you set the field quote to an empty string - try the basic import

e.g. remove the following 2 options

, FIELDQUOTE = ''

,...

first - while you mentioned you had "memory errors" the errors you supplied had nothing to do with memory, but only with a file format error when using the bulk...

yes. a standard pattern for this...

whenever I see these errors on BCP its one of the following

• invalid CSV file - e.g. not Double quoted on "fields" that contains double quotes or CR or LF

...

have a look at the code below.

basically I've created a temp table (on your case it would be a Permanente one) staging table to load the raw data

these types of...

replace the strings and values with others - as long as the position and sizes are the same it will do - without it not much we can tell.

but regardless...

a text file would never look like that - that has formatting that would not be possible on a text file so you are showing us potentially a rendering of...

Also, if the %of duplicates is higher than the % of singles, then it may be better to copy the singles into a new table and switch out...