Parse the Data and pump it onto SQL Sever

Question

Parse the Data and pump it onto SQL Sever

Mac1986

SSCertifiable

Points: 6216
More actions
March 18, 2016 at 1:10 pm

#305748

Hello All,
I have a requirement to pump the data from .txt file into SQL table. I was able to do this in the past. but this time i have a problem in the data format.
Below is the sample of data format in text file.
ID, Active, User, Client, Machine, StartTime, Duration, KernelTime, UserTime
306287760, active, <unknown>, <unknown>, <unknown>, 12:07:13, 0:00:00.004, 0.000, 0.000
306287758, netwait, <unknown>, <unknown>, <unknown>, 12:07:13, 0:00:00.041, 0.000, 0.000
306287757, netwait, <unknown>, <unknown>, <unknown>, 12:07:13, 0:00:00.046, 0.000, 0.000
306287755, netwait, <unknown>, <unknown>, <unknown>, 12:07:13, 0:00:00.050, 0.000, 0.000
306287753, authwait, <unknown>, <unknown>, <unknown>, 12:07:13, 0:00:00.054, 0.000, 0.015
306287752, netwait, <unknown>, <unknown>, <unknown>, 12:07:13, 0:00:00.085, 0.000, 0.000
306287751, netwait, <unknown>, <unknown>, <unknown>, 12:07:13, 0:00:00.121, 0.000, 0.000
306287746, authwait, <unknown>, <unknown>, <unknown>, 12:07:13, 0:00:00.126, 0.000, 0.000
As you can see, the data is delimited by ,
it has 9 columns in this data format. I just need to pump the above data in SQL table using powershell. (I know how to pump the data into SQL from powershell. I need help to parse the above data which delimited by ,)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gary Varga SSC Guru Points: 82166 More actions · Answer 1

No access to a computer at the moment, however, I believe that this can be achieved by string.Split and trimming the results.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Mac1986 SSCertifiable Points: 6216 More actions · Answer 2

I'd want the data to be read in below example command.

Get-Data | select ID, Active, User,Client,Machine, StartTime, Duration, KernelTime, UserTime

Mac1986 SSCertifiable Points: 6216 More actions · Answer 3

I got it working. we can archive this thread. Thanks a lot

foreach ($line in $ReturnString)

{

$array = @()

if ($line -notmatch "ID, Active, User, Client, Machine, StartTime, Duration, KernelTime, UserTime")

{ $ProcID, $Active, $User, $Client, $Machine, $StartTime, $Duration, $KernelTime, $UserTime= $line.split(",",9) }

Gary Varga SSC Guru Points: 82166 More actions · Answer 4

Thanks for posting your solution.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Jeff Moden SSC Guru Points: 1004280 More actions · Answer 5

Mac1986 (3/18/2016)
I got it working. we can archive this thread. Thanks a lot
foreach ($line in $ReturnString)
{
$array = @()
if ($line -notmatch "ID, Active, User, Client, Machine, StartTime, Duration, KernelTime, UserTime")
{ $ProcID, $Active, $User, $Client, $Machine, $StartTime, $Duration, $KernelTime, $UserTime= $line.split(",",9) }

Why would you use PowerShell for such a thing? Why not just use BULK INSERT?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)