Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • 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
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Building an Effective Phishing Protection Program

Join Director of Product Management Todd OBoyle on April 26th as he covers the key elements of a phishing protection program. Whether you’re an old hat at phishing education or considering starting a program -- we'll discuss critical components that should be in any program.

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