Solved

How to add records to a vairable in powershell 3 that is loaded from invoke-sql or import-csv

Posted on 2013-12-12
2
358 Views
Last Modified: 2013-12-12
Example:
$CSV = Invoke-Sqlcmd -Query $Query
I'm using Powershell version 4, so the solution should be done with version 3 or 4.

In this example $CSV.count would return 4,567 records. Same issue happens with import-csv and invoke-sql.

I then have to massage the data. I must ADD a duplicate record to the end of $CSV for each record that contains more than 1 filename in the $CSV.FileList field.

CSV$[102].FileList might equal "00310508.tif 00310509.tif 00310510.tif 00310511.tif 00310512.tif 00310513.tif" and I would then have to duplicate this row of data for each filename in the FileList field. The requirement is to have one filename per record in $CSV.

I've found a few ideas on the Internet, but they cause massive memory corruption and so I figure the additional records added to $CSV are simply added and no memory allocation is done to hold the added data.
So, nothing I have found has worked, except to export $CSV to a file, open it in Excel, and add several thousand records to the end of the file (just copied the last record and pasted it repeatedly), and then import the file remembering the original $CSV.count and re-using the newly added records as placeholders and then changing $CSV.count to equal $CSV.count+$new_record_count  and finally exporting it to a file.

Do you know of a way that I can duplicate the last record of $CSV a thousand times using Powershell?
Is there was a way to append records to $CSV and not have memory corruption? Like I said, I need to duplicate the current record that I am processing for each filename in $CSV.FileList.

THANKS!!!!
0
Comment
Question by:johnj_01201
  • 2
2 Comments
 
LVL 1

Accepted Solution

by:
johnj_01201 earned 0 total points
ID: 39714186
Solved. I'm still learning powershell. I was under the impression that a variable assigned to a command like $CSV=invoke-sql or $CSV=import-csv were "special", but it turns out they are like normal string variables in BASIC.
I was able to do this:
$CSV=$CSV+$CSV
to double the size of the original and now I can easily do what I need to.
Additionally, I can simply take the record that needs duplicated and do this:
$CSV=$CSV+$NewRecord
AMAZING!!!
Even the $CSV.count increases like it is supposed to!!!
Thanks Anyways!
0
 
LVL 1

Author Closing Comment

by:johnj_01201
ID: 39714188
Simple and perfect solution as I only wanted to work in powershell and not appending to the actual file. I bet this would solve many of the same issues others are having trying to append to an file instead of thinking about how to do it in memory.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This script checks a path to see if a folder exists. If the folder does exist you will get output "The folder has previously been created. No action taken" If not it will create the folder. Then adds one user modify permission to the folder. It …
In this previous article (https://oddytee.wordpress.com/2016/05/05/provision-new-office-365-user-and-mailbox-from-exchange-hybrid-via-powershell/), we made basic license assignments to users in O365. When I say basic, the method is the simplest way …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now