Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-01-03
6
Medium Priority
?
1,299 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 1000 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 19

Accepted Solution

by:
Raheman M. Abdul earned 1000 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

A recent project that involved parsing Tableau Desktop and Server log files to extract reusable user queries for use in other systems. I chose to use PowerShell to gather the data, and SharePoint to present it...
In previous parts of this Nano Server deployment series, we learned how to create, deploy and configure Nano Server as a Hyper-V host. In this part, we will look for a clustering option. We will create a Hyper-V cluster of 3 Nano Server host nodes w…
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

704 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