parsing records with empty spaces

Question

Post reply

parsing records with empty spaces

ebooklub

Hall of Fame

Points: 3905
More actions
January 18, 2017 at 10:13 am

#339502

I have text data in following format
ServerName      Server.db             PowerUser   Role
SERVER1         SERVER1.Test user1           1
                                                       user_X        1
SERVER1         SERVER1.Test2 User3          1
                                                       User_X       1
                                                      User_Y        3
SERVER3         SERVER3.Test4 User1           2
                                                      User_X         1
I need to load it into table using powershell
$x = Get-Content C:\test01.txt # save data above in test file to try
$x = $x.split("`n")
$cnt =0
foreach( $x_row in $x)
{
$x_row = ($x_row -replace '\s+', ' ')
$col1 = $x_row.split()[0]
$col2 = $x_row.split()[1]
$col3 = $x_row.split()[2]
$col4 = $x_row.split()[3]
$cnt = $cnt + 1
write-host $cnt , $col1, $col2, $col3, $col4
}

problem
when I use -replace '\s+', ' ') it eliminate empty spaces, but also moves records on row containing only "PowerUser"   "Role" to the left so they become $col1 and $col2
Question
how to parse data in my dataset allowing each record keep original positions ?<

Viewing 15 posts - 1 through 15 (of 24 total)

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

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

First, to improve efficiency and, in my opinion, clarity change:

$x_row = ($x_row -replace '\s+', ' ') $col1 = $x_row.split()[0] $col2 = $x_row.split()[1]
To:

$x_row = ($x_row -replace '\s+', ' ') $split_row = $x_row.split() $col1 = $split_row[0] $col2 = $split_row[1]

Gaz

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

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

Do not remove spaces with the -replace i.e. replace:

$x_row = ($x_row -replace '\s+', ' ') $col1 = $x_row.split()[0] $col2 = $x_row.split()[1]
With:
$col1 = $x_row.split()[0].Trim() $col2 = $x_row.split()[1].Trim()
Or:

$col1 = $split_row[0].Trim() $col2 = $split_row[1].Trim()

Gaz

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

ebooklub - Wednesday, January 18, 2017 10:13 AM
I have text data in following format
ServerName      Server.db             PowerUser   Role
SERVER1         SERVER1.Test user1           1
                                                       user_X        1
SERVER1         SERVER1.Test2 User3          1
                                                       User_X       1
                                                      User_Y        3
SERVER3         SERVER3.Test4 User1           2
                                                      User_X         1
I need to load it into table using powershell
$x = Get-Content C:\test01.txt # save data above in test file to try
$x = $x.split("`n")
$cnt =0
foreach( $x_row in $x)
{
$x_row = ($x_row -replace '\s+', ' ')
$col1 = $x_row.split()[0]
$col2 = $x_row.split()[1]
$col3 = $x_row.split()[2]
$col4 = $x_row.split()[3]
$cnt = $cnt + 1
write-host $cnt , $col1, $col2, $col3, $col4
}

problem
when I use -replace '\s+', ' ') it eliminate empty spaces, but also moves records on row containing only "PowerUser"   "Role" to the left so they become $col1 and $col2
Question
how to parse data in my dataset allowing each record keep original positions ?<

I'm afraid I don't understand what your text file is supposed to look like. It appears that you have four columns but six values in each row.

You say you have to use PowerShell - why is that? Have you considered BULK INSERT or bcp? They may simplify your task and/or improve performance.

John

ebooklub Hall of Fame Points: 3905 More actions · Answer 4

John Mitchell-245523 - Thursday, January 19, 2017 3:12 AM
ebooklub - Wednesday, January 18, 2017 10:13 AM
I have text data in following format
ServerName      Server.db             PowerUser   Role
SERVER1         SERVER1.Test user1           1
                                                       user_X        1
SERVER1         SERVER1.Test2 User3          1
                                                       User_X       1
                                                      User_Y        3
SERVER3         SERVER3.Test4 User1           2
                                                      User_X         1
I need to load it into table using powershell
$x = Get-Content C:\test01.txt # save data above in test file to try
$x = $x.split("`n")
$cnt =0
foreach( $x_row in $x)
{
$x_row = ($x_row -replace '\s+', ' ')
$col1 = $x_row.split()[0]
$col2 = $x_row.split()[1]
$col3 = $x_row.split()[2]
$col4 = $x_row.split()[3]
$cnt = $cnt + 1
write-host $cnt , $col1, $col2, $col3, $col4
}

problem
when I use -replace '\s+', ' ') it eliminate empty spaces, but also moves records on row containing only "PowerUser"   "Role" to the left so they become $col1 and $col2
Question
how to parse data in my dataset allowing each record keep original positions ?<
I'm afraid I don't understand what your text file is supposed to look like. It appears that you have four columns but six values in each row.
You say you have to use PowerShell - why is that? Have you considered BULK INSERT or bcp? They may simplify your task and/or improve performance.
John

Hi,
file was set as example...

the all story
we work with PowerShell and unix commands integrated in PowerShell code

Data bellow is output of Unix command generated inside PowerShell

output has 4 columns , first 2 columns could be empty

ServerName      Server.db     PowerUser   Role
SERVER1         SERVER1.Test user1       1
                                                    user_X      1
SERVER1         SERVER1.Test2 User3       1
                                                     User_X      1
                                                     User_Y
SERVER3         SERVER3.Test4 User1       2
                                                    User_X      1

goal is assign output to $x then using PowerShell parse it and load to SQL server table
when col1 and col2 empty it should take values from previous row

(sorry about formatting, need time to adapt to new interface of forum)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Goodness, yes, you can't process that with BULK INSERT. The ideal solution is to get your Unix command changed so that it outputs a file with rows and columns. Essentially, the processing that you're trying to do above should be done by the Unix command. Is that an option?

John

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

No problem. Let's clarify what you have for a file, though. I've indicated spaces in the following with filled circles just to make them painfully apparent.

Is this what your file actually looks like?

ServerNameÄServer.dbÄPowerUserÄRole
SERVER1ÄSERVER1.TestÄuser1Ä1
ÄÄuser_XÄ1
SERVER1ÄSERVER1.Test2ÄUser3Ä1
ÄÄUser_XÄ1
ÄÄUser_YÄ3
SERVER3ÄSERVER3.Test4ÄUser1Ä2
ÄÄUser_XÄ1

[/code]
Notice the two leading spaces on each partial line.

--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)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

John Mitchell-245523 - Thursday, January 19, 2017 9:01 AM
Goodness, yes, you can't process that with BULK INSERT. The ideal solution is to get your Unix command changed so that it outputs a file with rows and columns. Essentially, the processing that you're trying to do above should be done by the Unix command. Is that an option?
John

Don't be so sure, yet. 😉 The UNIX output may have the leading spaces I asked about built in.

--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)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

Jeff Moden - Thursday, January 19, 2017 9:06 AM
John Mitchell-245523 - Thursday, January 19, 2017 9:01 AM
Goodness, yes, you can't process that with BULK INSERT. The ideal solution is to get your Unix command changed so that it outputs a file with rows and columns. Essentially, the processing that you're trying to do above should be done by the Unix command. Is that an option?
John
Don't be so sure, yet. 😉 The UNIX output may have the leading spaces I asked about built in.

Oh, I see - two blanks followed by column 3 and then column 4. Sneaky!

John

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

John Mitchell-245523 - Thursday, January 19, 2017 9:10 AM
Jeff Moden - Thursday, January 19, 2017 9:06 AM
John Mitchell-245523 - Thursday, January 19, 2017 9:01 AM
Goodness, yes, you can't process that with BULK INSERT. The ideal solution is to get your Unix command changed so that it outputs a file with rows and columns. Essentially, the processing that you're trying to do above should be done by the Unix command. Is that an option?
John
Don't be so sure, yet. 😉 The UNIX output may have the leading spaces I asked about built in.
Oh, I see - two blanks followed by column 3 and then column 4. Sneaky!
John

Again... maybe, maybe not. We need confirmation from the OP. I'm making a logical guess here, but it's still just a guess based on when I expanded the OPs datapost during a reply.

--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)

ebooklub Hall of Fame Points: 3905 More actions · Answer 10

John Mitchell-245523 - Thursday, January 19, 2017 9:10 AM
Jeff Moden - Thursday, January 19, 2017 9:06 AM
John Mitchell-245523 - Thursday, January 19, 2017 9:01 AM
Goodness, yes, you can't process that with BULK INSERT. The ideal solution is to get your Unix command changed so that it outputs a file with rows and columns. Essentially, the processing that you're trying to do above should be done by the Unix command. Is that an option?
John
Don't be so sure, yet. 😉 The UNIX output may have the leading spaces I asked about built in.
Oh, I see - two blanks followed by column 3 and then column 4. Sneaky!
John

1.
yes output is exactly
ServerNameÃ„Server.dbÃ„PowerUserÃ„Role
SERVER1Ã„SERVER1.TestÃ„user1Ã„1
Ã„Ã„user_XÃ„1
SERVER1Ã„SERVER1.Test2Ã„User3Ã„1
Ã„Ã„User_XÃ„1
Ã„Ã„User_YÃ„3
SERVER3Ã„SERVER3.Test4Ã„User1Ã„2
Ã„Ã„User_XÃ„1

2. Unix command ...
command is unix script ,I have to use custom build PowerShell cmdlet that call those Unix scripts, return of this cmdlet could be assigned to objects of you choice
for now I simply do inside my powershell script

$x = (Run-OurUnixCmd -command "getpriv -server ServerX -user").split(" `n")
and then I work with $x ...

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 11

ebooklub - Thursday, January 19, 2017 10:18 AM
John Mitchell-245523 - Thursday, January 19, 2017 9:10 AM
Jeff Moden - Thursday, January 19, 2017 9:06 AM
John Mitchell-245523 - Thursday, January 19, 2017 9:01 AM
Goodness, yes, you can't process that with BULK INSERT. The ideal solution is to get your Unix command changed so that it outputs a file with rows and columns. Essentially, the processing that you're trying to do above should be done by the Unix command. Is that an option?
John
Don't be so sure, yet. 😉 The UNIX output may have the leading spaces I asked about built in.
Oh, I see - two blanks followed by column 3 and then column 4. Sneaky!
John
1.
yes output is exactly
ServerNameÃ„Server.dbÃ„PowerUserÃ„Role
SERVER1Ã„SERVER1.TestÃ„user1Ã„1
Ã„Ã„user_XÃ„1
SERVER1Ã„SERVER1.Test2Ã„User3Ã„1
Ã„Ã„User_XÃ„1
Ã„Ã„User_YÃ„3
SERVER3Ã„SERVER3.Test4Ã„User1Ã„2
Ã„Ã„User_XÃ„1

2. Unix command ...
    command is unix script ,I have to use custom build PowerShell cmdlet that call those Unix scripts, return of this cmdlet could be assigned to objects of you choice
   for now I simply do inside my powershell script
$x = (Run-OurUnixCmd -command "getpriv -server ServerX -user").split(" `n")
and then I work with $x ...

No need for PowerShell to import to SQL Server, then. Just do a BULK INSERT using a space as an assigned delimiter. If you have an IDENTITY column on the receiving table, the order of the input will be preserved and you can simple do a "data smear" to copy the data down in columns 1 and 2.

--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)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 12

What version of SQL Server are you using for this?

--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)

ebooklub Hall of Fame Points: 3905 More actions · Answer 13

Jeff Moden - Thursday, January 19, 2017 9:38 PM
What version of SQL Server are you using for this?

1.SQL 2014

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 14

ebooklub - Friday, January 20, 2017 8:05 AM
Jeff Moden - Thursday, January 19, 2017 9:38 PM
What version of SQL Server are you using for this?
1.SQL 2014

K. Thanks. I've already got some demonstrable code for doing the import of these files using BULK INSERT. I just need to write a "data smear" to copy the server and database names down through the empty spots in the first 2 columns. I'll try to get to that tonight.

--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)