Solved

Power Shell Script Maybe?

Posted on 2014-04-12
8
256 Views
Last Modified: 2014-04-15
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
Comment
Question by:mike1142
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 29

Expert Comment

by:becraig
ID: 39996200
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
 

Author Comment

by:mike1142
ID: 39996224
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
 
LVL 19

Expert Comment

by:Raheman M. Abdul
ID: 39996432
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 29

Expert Comment

by:becraig
ID: 39996643
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
 

Author Comment

by:mike1142
ID: 39996693
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
 
LVL 19

Accepted Solution

by:
Raheman M. Abdul earned 500 total points
ID: 39996759
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
 
LVL 29

Expert Comment

by:becraig
ID: 39996768
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
 

Author Closing Comment

by:mike1142
ID: 40002796
this worked thank you
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
A project that enables an administrator to perform actions within a user session context not just at the time of login but any time later on day(s) or week(s) later.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question