• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1402
  • Last Modified:

Need Help - System.Data.DataRow to string???

Powershell v4

I wrote a 900 line PS script and thought it was great until I ran it on real data. The SQL query using invoke-sql did not work because the query never finished running with over 1,000,000 records and growing, so I change the program to work on each record instead of each table. This solved the query timeout issue. (Query ran for 15 hours on 1 table and I had to stop it).

However, the program use to export the modified table to a CSV file and now the most I can export is 1 record per CSV file.

I know the $a=$a+$record wont work because $record is just a pointer to where the actual data is stored and it is not a string.

Can you think of a way that I can solve this problem?
If not, I will have to write a CSV file to disk for each record and then merge them all when I'm done with each table. This is the only way I can figure out how to solve this issue.

In my head, I'm thinking there must be a way to create an empty system.data.table? and then add rows to it, but all of the examples I found on the Internet were for C# and I do not know how to convert them to PS, but I did find that in the following example $table has things that are mentioned in the C# code like:
$table.add
$table.clone
$table.copyto
.etc




$table=invoke-sql(...)
foreach ($record in $table)
{
#record.columns are modified here
#done modifying current record
$a = $a + $record
}
#save the table to a CSV file
$a|export-csv ...


Here's some more information in case it helps:

$table.GetType()

IsPublic IsSerial Name                                     BaseType                                                                                                      
-------- -------- ----                                     -------                          
True     True     String                                   System.Object                                                                                                

$table.GetTypeCode()
String

$Record.GetType()

IsPublic IsSerial Name                                     BaseType                                                                                                      
-------- -------- ----                                     --------
True     False    DataRow                                  System.Object
0
johnj_01201
Asked:
johnj_01201
  • 4
2 Solutions
 
johnj_01201Author Commented:
Another option would be to append to the existing .CSV file using PS 3 or 4 code and using export-csv. This way I would only have 1 CSV file per table and it would be written to as I process each record in the table.
0
 
johnj_01201Author Commented:
I added the -Append switch to export-csv and it works perfectly!!! I don't know what the big deal is where everyone says it is buggy and should not be used???

My problem is solved, but I would love to know if there's a way to copy $Record to a string and then have the string be like 1 large CSV file that could then be saved or exported to a file.

I like to work with databases and it would be great if table\row objects worked better with normal PS usage. I have a BASIC and a little C background.
thanks!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
hmmm, maybe you should check back the first sql and see why it is actually timing out?
eventually a well-placed index or 2, eventually some rewrite of the sql, and you are back on the t-sql power?
0
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

 
Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
You can still run the C# code using and from within Powershell and you don't need to convert the code You found.
0
 
johnj_01201Author Commented:
I'm using PS because C# is too complicate. I don't want to know about methods or reflections or how to code in C#. The examples in C# are written with a different syntax and some people who know both languages can convert them, but not me.
0
 
johnj_01201Author Commented:
no good answer, but it was more interesting to find out C# code could be run in PS. thanks anyways!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Worried about phishing attacks?

90% of attacks start with a phish. It’s critical that IT admins and MSSPs have the right security in place to protect their end users from these phishing attacks. Check out our latest feature brief for tips and tricks to keep your employees off a hackers line!

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now