Solved

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

Posted on 2014-01-03
6
1,169 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
  • 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Windows 10 came with  a lot of built in applications, Some organisations leave them there, some will control them using GPO's. This Article is useful for those who do not want to have any applications in their image (example:me).
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

763 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