Import data to SQL and adding fields to input

Question

I have powershell version 2.0.0.1082 on my server. I want to import data from a text file to SQL server, and add two new fields (character and current datetime) to the input to SQL 2008 database. I have been using bulk import or SSIS to do it but I want to use powershell for the ease of maintaining the process. The file has column name at top and each field is seperated by "|". Code so far:

clear

 import-csv "Disk.txt" -Delimiter "|" | 
 foreach { add-member -in $_ -membertype noteproperty DateRecorded $((Get-Date).ToString("yyyy-MM-dd")) 
 add-member -in $_ -membertype noteproperty SystemName 'System Name'
 add-member -in $_ -membertype noteproperty Drive 'Drive Letter'
 add-member -in $_ -membertype noteproperty TotalSizeGB 'Total Size'
 add-member -in $_ -membertype noteproperty UsedGB Used
 add-member -in $_ -membertype noteproperty FreeGB Free
 add-member -in $_ -membertype noteproperty PercentFree '% Free'}|
select DateRecorded,SystemName,Drive,TotalSizeGB,UsedGB,FreeGB,PercentFree |Format-Table

Any ideas please?

Thanks!

Manjot

Do you have any code started? Are you stuck on any particular part? For starters you'll want to use import-csv and loop through each object adding your additional fields. After that you can use invoke-sqlcmd to add the data to the database. — Andy Arismendi
– Andy Arismendi, Commented Dec 29, 2011 at 21:43
Yes. i have updated the code in question. the problem with this is...it is not showing anything. I wanted to see the output first before jumping to the SQL part — Manjot
– Manjot, Commented Dec 29, 2011 at 21:52

Andy Arismendi · Accepted Answer · 2011-12-30 01:54:04Z

1

You'll either need to add the -PassThru switch to the Add-Member cmdlet to forward the objects on in the pipeline or put $_ all by itself at the end of the ForEach block.

Here is an example using -PassThru:

Import-Csv "Disk.txt" -Delimiter "|" | ForEach {
    Add-Member -InputObject $_ -MemberType NoteProperty -name "DateRecorded" -value $((Get-Date).ToString("yyyy-MM-dd"))
    Add-Member -InputObject $_ -MemberType NoteProperty -name "SystemName" -value 'System Name' -PassThru
} | Select DateRecorded,SystemName,Drive,TotalSizeGB,UsedGB,FreeGB,PercentFree | Format-Table

Another example without -PassThru:

Import-Csv "Disk.txt" -Delimiter "|" | ForEach {
    Add-Member -InputObject $_ -MemberType NoteProperty -name "DateRecorded" -value $((Get-Date).ToString("yyyy-MM-dd"))
    Add-Member -InputObject $_ -MemberType NoteProperty -name "SystemName" -value 'System Name'
    $_
} | Select DateRecorded,SystemName,Drive,TotalSizeGB,UsedGB,FreeGB,PercentFree | Format-Table

Once you've prepared your CSV data for input into the database you can use the Invoke-SqlCmd cmdlet that comes with SQL Server 2008 to perform Inserts. There is a wealth of information on MSDN here to show you how.

edited Dec 30, 2011 at 1:54

answered Dec 29, 2011 at 22:48

Andy Arismendi

53.2k17 gold badges113 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Manjot Over a year ago

Thanks. any idea why it seperates each text with "" ? I don't want to see ""C:" etc in my database.

Andy Arismendi Over a year ago

@Manjot If you are referring to the CSV text file. Values with spaces in them will be double quoted so they are treated as single token. Once you access the property values after importing it with Import-Csv you will not see the double quotes.

Manjot Over a year ago

Thanks for the quick reply. Yes I can't see it when using Import-CSV but I can't directly export the output to SQL database. There are 10 text files to which I am adding fields, importing to the SQL database. I am exporting the updated data to file and then importing to SQL database. I checked invoke-sqlcmd as you mentioned but it doesn't accept direct input. Tired using bulk import but it keeps the double quotes.

Andy Arismendi Over a year ago

@Manjot I was assuming you were mapping CSV columns to database columns in which case all you'd have to do is an Insert statement with Invoke-SqlCmd for each line of the CSV file. Are you trying to store the entire text file in a single database field?

Chad Miller Over a year ago

@Manjot The double quotes are text delimiters. Most programs including Excel, Access and SSIS correctly recognize text delimiters, however BULK INSERT and bcp do not. There's a few ways to handle this. I've written an article describing various techniques here blogs.technet.com/b/heyscriptingguy/archive/2011/11/28/…

|

Collectives™ on Stack Overflow

Import data to SQL and adding fields to input

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related