Solved

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

Posted on 2014-01-03
6
1,257 Views
Last Modified: 2014-01-07
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
Comment
Question by:johnj_01201
[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
  • 4
6 Comments
 
LVL 1

Author Comment

by:johnj_01201
ID: 39754557
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
 
LVL 1

Author Comment

by:johnj_01201
ID: 39754576
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 39754948
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 19

Accepted Solution

by:
Raheman M. Abdul earned 250 total points
ID: 39755386
You can still run the C# code using and from within Powershell and you don't need to convert the code You found.
0
 
LVL 1

Author Comment

by:johnj_01201
ID: 39755440
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
 
LVL 1

Author Closing Comment

by:johnj_01201
ID: 39762240
no good answer, but it was more interesting to find out C# code could be run in PS. thanks anyways!
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
In this post we will be converting StringData saved within a text file into a hash table. This can be further used in a PowerShell script for replacing settings that are dynamic in nature from environment to environment.
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…

636 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