Solved

convert data using powershell

Posted on 2016-08-31
4
26 Views
Last Modified: 2016-08-31
HI EE,

I want to take data copied from a sql return and convert it to one line (with commas) using powershell  so i can easily update a sql query .

145613
147869
147869
159758
159758
159758
159758
150874
150874
158153
158153
158153
158153
156311
158153
149379
158153
158153
158153
158153
147869
147869
147869
147869
147869
147869
147869
147869
147869
147869
147869
147869
147869
152609
152609
152609
147869
147869
148114
158153
158153
158153
147869
147869
152609
152609
152609
147869
152609
159758
152609
147869
147869
145613
155883
155925
158772
159172
156363
156721
145613
147235
152884
150874
150874
150874
150874
152024
152027
153726
153726
154959
155357
152963
152963
152963
149379
149379
156486
156486
156486
159747
159747
155883
155883
155883
145935
154386
152963
153150
153408
154722
147211
147211
147211
145016
145016
145016
150273
147933
156069
155819
155819
159057
159434
158874
157221
156512
159697
159537
147236
150101
145592
154630
155429
152961
152854
152854
154892
145921
145921
145921
157506
156311
159474
155554
155554
156706
155876
155876
155876
156220
156220
156235
145896
150258
154630
154630
150874
150874
159697
159172
159758
159758
159758
159747
159747
159747
159747
159747
155554
155554
155554
155554
155883
155883
155883
155883
155883
155883
155883
145016
145016
156363
156363
147211
147211
147211
147211
147211
147211
147211
147933
154630
152854
152854
156069
156069
154892
154892
154892
154892
154892
154892
154892
154892
154892
152609
153150
153408
148114
150101
150101
150101
150101
150101
155925
155925
155925
155925
154386
154386
154386
155819
157221
150874
150874
150874
150874
150874
150874
150874
150874
150874
154722
156721
156721
155429
155429
154722
155429
155429
155429
157506
157506
157506
154959
154959
154959
154959
158874
158874
145896
145896
145896
145896
145896
145896
156069
156069
156069
156069
145896
145896
145896
145896
145896
154630
145613
145613
145613
145613
145935
145613
145613
145613
153150
153150
153150
150258
156235
156235
156220
156220
156220
159434
159434
159434
156220
156311
156311
159057
159057
156706
149379
149379
149379
159172
159172
159172
159537
159537
159537
159537
159537
159537
159537
159697
159697
159697
159537
159537
155876
155876
155876
155876
155876
155876
156512
156512
156512
145921
154630
156486
156486
150874
150874
150874
150874
150874
150874
150874
150874
150874
152024
152024
152024
152024
152027
152027
152027
153408
153408
153726
153726
153726
153726
153726
152884
152961
152961
152963
152963
152963
152963
152963
152963
152963
152963
152963
145016
145016
145016
156363
156363
156363
156363
147869
147869
159537
159537
159537
149379
145613
145613
150273
150273
150273
150273
152961
152961
152961
147869
159537
154630
155357
155357
155357
155357
159537
153726
153726
153726
153726
153726
155883
155883
155883
155883
155883
153726
153726
153726
153726
155925
147869
147869
147869
147869
147869
147869
147869
147869
145896
145896
145896
145896
149379
152609
145896
152609
152609
152609
152609
152609
152609
152609
152609
152609
152609
145896
145896
145613
145613
156235
156235
156235
156235
156235
156220
156220
156220
156220
156220
156220
156220
154630
154630
156486
156486
156486
156486
156486
156486
156486
156486
153726
156486
145016
145016
145016
145016
145016
145016
145016
145016
145016
145016
145016
147235
147235
147235
147235
147236
147236
152884
152884
152884
152884
147236
147236
159057
159057
159057
159057
154630
154630
154630
154630
145613
145613
145613
145613
145613
145613
145613
145613
145613
145613
145613
145613
145613
145613
156721
156721
156721
156721
154722
154722
154722
154722
154722
150874
150874
150874
150874
150874
150874
150874
150874
150874
150874
150874
150874
156706
156706
156706
156706
154892
154892
154892
154892
155876
155876
155876
155876
155876
155876
155876
155876
155876
150258
150258
150258
156220
156220
155876
155876
155876
155554
155554
153726
153726
152963
152963
152963
152963
147869
147869
145613
145613
145016
145016
145016
159747
159758
159758
159758
159758
159758
159758
159758
159758
159758
159758
159747
159747
159747
159747
159747
159747
159747
159747
159747
157221
157221
157221
157221
156512
156512
156512
155883
156512
155883
155883
155883
155883
155883
155883
155883
155883
155883
155883
155883
155883
155883
155883
155883
155883
155883
156512
156512
156512
156512
152854
152854
152854
152854
145921
145921
145921
145921
145921
145921
145921
145921
145921
152854
152854
152854
152854
145921
145921
145921
145921
156311
156311
145935
145935
145935
145935
156311
156311
145592
145592
145592
145592
156311
156311
156311
156311
159474
159474
159474
159474
155819
155819
155819
155819
155819
155819
155819
155819
158874
158874
158874
158874
158772
158772
147933
147933
149379
149379
149379
147933
147933
147211
147211
147211
158772
158772
158772
158772
149379
149379
149379
159434
149379
149379
159697
159697
159697
159697
159697
159697
159697
149379
149379
147211
155819
155819
155819
159537
159537
159537
159537
159537
159537
159537
159537
159537
159474
159474
159474
159474
159474
159474
159474
159434
159434
159434
159434
159434
159434
159434
159434
159434
156706
156706
156706
156706
156706
159057
159057
159057
159057
159057
159057
159057
159057
159057
159057
159172
159172
159172
159172
159172
159172
159172
159057
159056
159057
159057
159057
159057
159057
159057
159057
159057
159057
159057
159057
159057
159057
159057
159057
159057
159057
159057
159057
159057
159057
159057
159057
159056
159056
158874
158874
158874
158874
158874
158874
158874
158772
158772
158772
158772
158772
158772
158772
156069
150101
145613
145613
145592
154630
155883
155883
155883
155883
152024
145935
152884
152961
155925
155819
155819
147235
147236
147933
155876
155876
155876
145016
145016
145016
152963
152963
152963
152963
152854
152854
150258
147933
155925
155925
155925
155925
155925
155925
155925
155925
155925
155925
155883
155883
155925
155925
152884
155925
155925
152024
152024
152024
145016
145016
155819
145935
145592
145592
152854
152854
156069
156069
156069
156069
145613
145613
150101
150101
150101
150258
150258
150258
150258
150258
150101
150101
150101
150101
155876
155876
155876
155876
155876
155876
155876
152963
150258
152961
152961
152961
154630
154630
154630
155357
155357
155357
155357
155357
155357
155357
155357
155357
155357
150101
145613
145613
145613
156069
156069
152961
152884
152884
152884
152884
155876
155876
155876
155876
155876
155876
155876
155876
155876
155876
155876
155357
145592
145592
145592
145592
145592
147236
147236
152963
152963
152963
152963
152963
152963
152963
152963
152963
152963
152963
152963
145613
145613
145613
145613
145613
145613
145613
150101
145613
145613
145613
145613
145613
145613
145613
145613
145613
145613
145016
145016
145016
145016
145016
145016
145016
145016
145016
145016
145016
145016
145016
145016
145016
145016
152854
152854
152854
152854
152854
152854
152854
155819
155819
155819
155819
155819
155819
155819
155819
147933
150258
150258
150258
150258
150258
155883
155883
155883
155883
155883
155883
155883
155883
155883
145935
145935
145935
145935
155883
155883
155883
155883
155883
155883
155883
155883
147236
147236
147235
147235
147236
147236
147933

Open in new window


Wish to make it like this

145016,145016,145016,145592,145613,145613,145896,145935,147211,147211,147211,147235,147236,147869,147869,147933,148114,150101,150258,150874,150874,150874,150874,152024,152027,152609,152854,152854,152884,153408,153726,153726,154386,154415,154630,154959,153726,153726,155429,155554,155554,155564,155564,155564,156069,156220,156220,156235,156311,156486,156486,156486,156512,156706,156721,157221,157506,158153,158153,158153,158772,158874,159056,159057,159172,159434,159697,159758,159747,159747,159537,159474,156363,155925,155883,155883,155883,155883,155876,155876,155876,155819,155819,155357,154892,154722,153150,152963,152963,152963,152963,152961,150273,149379,149379,145921,145921,145921

Open in new window

0
Comment
Question by:davesnb
  • 2
  • 2
4 Comments
 
LVL 82

Expert Comment

by:oBdA
Comment Utility
Assuming you have those numbers in an array, it's a simple join operation:
$NumberArray -join ','

Open in new window

0
 

Author Comment

by:davesnb
Comment Utility
ok , but how do i get it so it outputs as one line in the powerhshell console so i can copy and paste it into the ssms window?
0
 
LVL 82

Accepted Solution

by:
oBdA earned 500 total points
Comment Utility
That's what the command above does. Just paste this directly into an open PS console:
@( 
145613
147869
147869
159758
) -join ','

Open in new window

To facilitate things, especially getting the results into the clipboard, you can use
$NumberArray -join ',' | clip.exe

Open in new window

0
 

Author Closing Comment

by:davesnb
Comment Utility
perfect , thanks
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

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…
This article will help you understand what HashTables are and how to use them in PowerShell.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

762 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

6 Experts available now in Live!

Get 1:1 Help Now