Solved

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

Posted on 2014-01-03
6
1,061 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 142

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 18

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The article will show you how you can maintain a simple logfile of all Startup and Shutdown events on Windows servers and desktops with PowerShell. The script can be easily adapted into doing more like gracefully silencing/updating your monitoring s…
"Migrate" an SMTP relay receive connector to a new server using info from an old server.
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…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

705 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

18 Experts available now in Live!

Get 1:1 Help Now