• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

Power Shell Script Maybe?

I know BULK is usually the way you upload a csv file into a table but lets just say this is not possible for various reasons. So....

I have aggravatingly played around with a powershell script to create the necessary T-SQL commands to do a INSERT INTO table  VALUES (f1,f2,...) (f1,...) What alludes me is doing the foreach on the the records and then the foreach on the values. I know something is not quite right and my only effort so far has resulted in a long line of referencing each field in the $line.whatever.

Nested loops are not that hard of a programming structure so why I can't get it or find an example that shows where I am failing is baffling.
0
mike1142
Asked:
mike1142
  • 3
  • 3
  • 2
1 Solution
 
becraigCommented:
Difficult to say without seeing a snippet of your code, however here is an idea.

Create your connection to the database
Use import csv (hoping your data has headers)
Do a for each on the csv records
Then do an eval on the destination if you need to (ifexists)
Then do your insert
0
 
mike1142Author Commented:
I do some kind of import CSV to assign the file to $array

foreach($line in $array) 
{Out-File -FilePath ".\values2.txt" -InputObject $("('" + $line.Sheet + "','" + $line.XXXNum + ',' + $line.DataElement + ',' ... + $line.Units + "')`n")  -Append}

Open in new window


NOTE: I do not need to connect to the database. I just want the script.
0
 
Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
Can you be a bit more clear please.  Your code is doing opposite to what you require.
If you are looking to import the contents of .csv with the headers and make a string like that you use in SQL Statement   eg.   (f1, f2, f3,....), (f1, f2, f3,....), (f1, f2, f3,....)
then the following is your code which generates the string :
(f1, f2, f3,....), (f1, f2, f3,....), (f1, f2, f3,....)

csv content:

Laptop      Name      Date
P1013      ITSLTR8D      20/11/2013 12:56
P1035      ITD8EXN      20/11/2013 12:56
P1135      ITSLN      20/11/2013 13:02

#=====================================================
$file=Import-Csv C:\Temp\P10131135.csv
foreach ($row in 0..($file.Length-1))
{
$lt = $file.Item($row).Laptop
$name = $file.Item($row).Name
$dt = $file.Item($row).Date

$sql+='('+$lt+ ',' + `
           $name+ ',' + `
           $dt+ ')'
if($row -ne $file.Length-1)
{ $sql+=',' }

}

$sql
0
Who's Defending Your Organization from Threats?

Protecting against advanced threats requires an IT dream team – a well-oiled machine of people and solutions working together to defend your organization. Download our resource kit today to learn more about the tools you need to build you IT Dream Team!

 
becraigCommented:
Something like this should work for you:

An idea of the format of the input file would be really helpful


import-csv c:\file.csv | % {
function Insert-Info()
  {  
    $conn = new-object system.data.oledb.oledbconnection
    $connstring = "provider=sqloledb;data source=Server;initial catalog=DBName;integrated security=SSPI"
    $conn.connectionstring = $connstring
    $conn.open()
    $query = "INSERT INTO table (value1, value2, value3) VALUES ('$_.val1', '$_.val2', '$_.val3')"
    $cmd = New-Object system.data.oledb.oledbcommand
    $cmd.connection = $conn
    $cmd.commandtext = $query
    $cmd.executenonquery()
    $conn.close()
  }
  Insert-Info 
  }

Open in new window

0
 
mike1142Author Commented:
If this were the ideal circumstance I would do:

BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

Open in new window


csvtest.txt would contain a header row and comma separated values. Such as:

ID,FirstName,LastName,BirthDate
1,James,Smith,19750101
2,Meggie,Smith,19790122
3,Robert,Smith,20071101
4,Alex,Smith,20040202

Open in new window


BULK INSERT is not an option so I wanted to create a script say insertCSV.sql

Such that the resulting output file insertCSV.sql

looks like

INSERT INTO tableName VALUES
('1','James','Smith','19750101'),
('2,'Meggie,Smith','19790122'),
('3','Robert','Smith','20071101'),
('4','Alex','Smith','20040202')

Open in new window


I do not need to connect to the database I just need the file to read as above and I will then upload and execute the SQL commands. tableName is already created with the proper attributes to accept the data.

Maybe powershell is not the right tool and I need to use something else? I could not think of anything else handy that could import values and spit them back out formatted slightly differently.
0
 
Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
Add the following line to the script I posted earlier before the first line.

$sql="insert into tablename values "
Then modify the column values according to your requirement.

$sql contains the SQL statement generated.
0
 
becraigCommented:
When you say spit them back out formatted differently what do you mean ?

Do you want to change something about the information in the text file?

PowerShell cab do what you need it to if you're clear about your requirements. I just don't have a lot to go on here as far as your exact needs.
0
 
mike1142Author Commented:
this worked thank you
0

Featured Post

Who's Defending Your Organization from Threats?

Protecting against advanced threats requires an IT dream team – a well-oiled machine of people and solutions working together to defend your organization. Download our resource kit today to learn more about the tools you need to build you IT Dream Team!

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now