0

I've got a fairly simple csv with 3 columns that I would like to import into SQL using powershell. I thought I it working except I'm literally getting the whole line object in each column in SQL rather than each column from the csv file. Just to complicate matters I'm also adding in an extra guid field into SQL that isn't in the csv but that seems to be the only column that's actually working.

Powershell

  Import-CSV D:\sample.csv | ForEach-Object { Invoke-Sqlcmd `
 -Database $DbName -ServerInstance $ServerName -OutputSqlErrors $true `
 -Query "insert into $DbTable ([item],[lob],[cc],[file_import_guid]) VALUES ('$_.Item','$_.LOB','$_.CC','$ImportGUID')"
 }

and my sample csv is simply

Item,LOB,CC
0029544090511,LOBJ17_0003559,PCC
0029544090511,LOBJ17_0003560,PCC

Therefore I expected each column to import but instead I got this in SQL which was unexpected.

item    lob cc  file_import_guid
@{Item=0029544090511; LOB=LOBJ17_0003559; CC=PCC}.Item  @{Item=0029544090511; LOB=LOBJ17_0003559; CC=PCC}.LOB   @{Item=0029544090511; LOB=LOBJ17_0003559; CC=PCC}.CC    7550d245-32b9-447a-bcfa-bbe6e7e22fd8
@{Item=0029544090511; LOB=LOBJ17_0003560; CC=PCC}.Item  @{Item=0029544090511; LOB=LOBJ17_0003560; CC=PCC}.LOB   @{Item=0029544090511; LOB=LOBJ17_0003560; CC=PCC}.CC    7550d245-32b9-447a-bcfa-bbe6e7e22fd8

The guid seems to be working fine so its something in the object loop. I tried changing to a simple foreach loop but get the same result. When I try and check my data with a write-host command it looks fine (on screen at least)

PS SQLSERVER:\> Import-CSV D:\sample.csv | ForEach-Object {write-host $_.Item $_.LOB $_.CC $ImportGUID}
0029544090511 LOBJ17_0003559 PCC e55019b0-d783-4fd8-9dda-0f409323e8bc
0029544090511 LOBJ17_0003560 PCC e55019b0-d783-4fd8-9dda-0f409323e8bc

Any ideas?

user6769642
  • 33
  • 1
  • 6
  • 1
    Change last part of the SQL statement to `VALUES ('$($_.Item)','$($_.LOB)','$($_.CC)','$($ImportGUID)')` – Mathias R. Jessen Apr 17 '17 at 22:03
  • Can we not help other people opening themselves up to [SQL injection](https://en.wikipedia.org/wiki/SQL_injection)? Pretty please? If you must insert data like this, please use prepared statements. – Ansgar Wiechers Apr 17 '17 at 22:27
  • Have you looked into [bulk-importing](https://learn.microsoft.com/en-us/sql/relational-databases/import-export/bulk-import-and-export-of-data-sql-server) the CSV? See also [this question](https://stackoverflow.com/q/15242757/1630171). – Ansgar Wiechers Apr 17 '17 at 22:29
  • @ Mathias R. Jessen - Thanks a lot that's exactly what I needed its working now. – user6769642 Apr 18 '17 at 07:01

0 Answers0