Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Power Shell Script Maybe?

Posted on 2014-04-12
8
Medium Priority
?
260 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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 2000 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

Does Powershell have you tied up in knots?

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

Question has a verified solution.

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

Windows 10 came with  a lot of built in applications, Some organisations leave them there, some will control them using GPO's. This Article is useful for those who do not want to have any applications in their image (example:me).
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

604 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