Author Posts

August 24, 2018 at 5:53 pm

Hello everybody,

I am new to PowerShell, so please bear with me...

From a .CSV file, I am trying to copy selected data/columns into another file.

After several hours spent googling and experimenting, I thought I would ask the experts.

The furthest I have got is to copy the content of the .CSV into a variable and now I am stuck with finding the text I need and copying it to another file.

I have attempted using the substring command, but cannot find a practical example of how to use it – it might be because I am new to this, if so, I apologize!

August 24, 2018 at 6:01 pm

Hi Tim,

Could you provide a sample of what you've got already? Along with maybe some sample data from the file to further explain what you are trying to accomplish?

SB

August 24, 2018 at 9:02 pm

field1,field2,field3
Test,zero,0
Test1,one,1
Test2,two,2
Test3,Three,3

you can import the fields based off criteria

$result = import-csv c:\temp\mylist.csv | Where {$_.Field3 -gt 1}

$result will only have the last two lines.

PS > $result = Import-Csv .\test.csv |where {$_.Field3 -gt 1}

PS > $result

 

field1 field2 field3
------ ------ ------
Test2  two    2
Test3  Three  3

Or

PS > $result = Import-Csv .\test.csv |where {$_.Field2 -like 'two'}

PS > $result

field1 field2 field3
------ ------ ------
Test2  two    2

Then you could pipe it to Export-CSV and have a brand new file with just the entries you need. Add more steps or more criteria as necessary to get the right items. Good Luck!

August 27, 2018 at 2:13 pm

Thanks Stephen, here's some more information – I hope you can read it ok 🙂

August 27, 2018 at 2:14 pm

Well....even I cant read it, so I'm gonna try again!

I have a .ZIP file that I need to process as follows:

1)  Extract all files

2) Copy .ZIP file to backup folder

3) Open .CSV file in H:\Powershell\ABC\123456\20180827\outputs\i1 and copy data from field 1, field 2, field 3 etc to a new .csv/text file

– the subfolder "20180827" will change every day, otherwise this would be easy – I could tell the script to look in the same folder if this sub-folder was always the same

1) and 2) are working ok

I just need help with 3) 🙂

Field 1
Field 2
Field 3
Field 4
Field 5
Field 6
Field 7
Field 8
Field 9
Field 10

Data 1
Data 2
Data 3
Data 4
Data 5
Data 6
Data 7
Data 8
Data 9
Data 10

 

Field 11
Field 12
Field 13
Field 14
Field 15
Field 16
Field 17
Field 18
Field 19
Field 20
Field 21
Field 22
Field 23

Data 11
Data 12
Data 13
Data 14
Data 15
Data 16
Data 17
Data 18
Data 19
Data 20
Data 21
Data 22
Data 23

Data 43
Data 44
Data 45
Data 46
Data 47
Data 48
Data 49
Data 50
Data 51
Data 52
Data 53
Data 54
Data 55

Data 75
Data 76
Data 77
Data 78
Data 79
Data 80
Data 81
Data 82
Data 83
Data 84
Data 85
Data 86
Data 87

Data 107
Data 108
Data 109
Data 110
Data 111
Data 112
Data 113
Data 114
Data 115
Data 116
Data 117
Data 118
Data 119

Data 139
Data 140
Data 141
Data 142
Data 143
Data 144
Data 145
Data 146
Data 147
Data 148
Data 149
Data 150
Data 151

Data 171
Data 172
Data 173
Data 174
Data 175
Data 176
Data 177
Data 178
Data 179
Data 180
Data 181
Data 182
Data 183

Data 203
Data 204
Data 205
Data 206
Data 207
Data 208
Data 209
Data 210
Data 211
Data 212
Data 213
Data 214
Data 215

Data 235
Data 236
Data 237
Data 238
Data 239
Data 240
Data 241
Data 242
Data 243
Data 244
Data 245
Data 246
Data 247

Data 267
Data 268
Data 269
Data 270
Data 271
Data 272
Data 273
Data 274
Data 275
Data 276
Data 277
Data 278
Data 279

Data 299
Data 300
Data 301
Data 302
Data 303
Data 304
Data 305
Data 306
Data 307
Data 308
Data 309
Data 310
Data 311

Data 331
Data 332
Data 333
Data 334
Data 335
Data 336
Data 337
Data 338
Data 339
Data 340
Data 341
Data 342
Data 343

 

August 27, 2018 at 2:16 pm

Thank you very much John, I'm going to try your suggestions!

August 27, 2018 at 3:22 pm

Tim,

What have you tried? You've posted an example of logic, but no examples of code, errors, etc. that you've tried. You can look at cmdlets Import-CSV, Select-Object and Export-CSV (in that order) to build a solution.

August 27, 2018 at 6:42 pm

Date manipulation can be hard, try this for the date string. Maybe someone else has a better idea you can use.

PS > $variabledate = "{0}" -f (get-date -Format yyyyMMdd)  #must use MM for two digit months
PS > $variabledate    #this is a string variable.
20180827

You can also specify the date to use by just adding

Get-Date("01/01/1999") to produce "19990101"

#Yesterday
#date addition/subtraction:
PS > $variabledate = "{0}" -f (get-date((get-date).adddays(-1)) -Format yyyymmdd)
PS > $variabledate
20184026

August 27, 2018 at 7:44 pm

Perfect, this was exactly what I was looking for, thank you!