asked on

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.

becraig

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

mike1142

ASKER

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.

Raheman M. Abdul

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

becraig

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

mike1142

ASKER

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.

ASKER CERTIFIED SOLUTION

Raheman M. Abdul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

becraig

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.

mike1142

ASKER

this worked thank you