none
Shell Script While Loop reading csv files and changing file name RRS feed

  • Question

  • Hi Guys,

    I need urgent help to create power shell script for below requirement. I don't know power shell scripting language help me out on this thanks.

    My requirement is to read my csv “|" delimiter files from source folder as loop using shell script and create new file by using column1_column2 and then move the file to that monthly folder YYYYMM.
    Source file: //serverftp/source
    Destination: //server2/destination

    Column1: StoreId
    Column2: Date
    Example:
    File format
    Column1| Column2
    12345|2016-01-09 06:23:17
    12345|2016-01-11 09:11:18
    12345|2016-01-15 10:10:25
    12345|2016-02-08 11:14:40

    Read the above file and create file name using col1_col2 data from inside the file. It has to same date while creating file name.

    Column1| Column2
    12345|2016-01-09 06:23:17
    File Name: 12345_20160109.csv


    Column1| Column2
    12345|2016-01-11 09:11:18
    File Name: 12345_20160111.csv


    Column1| Column2
    12345|2016-01-15 10:10:25
    File Name: 12345_20160115.csv

    Column1| Column2
    12345|2016-02-08 11:14:40
    File Name: 12345_20160208.csv

    And then move this file to respective YYYYMM folder to other destination on different destination server.

    Example Folder Name: 201601
    12345_20160109.csv
    12345_20160111.csv
    12345_20160115.csv

    Folder Name: 201602
    File Name: 12345_20160208.csv

    I really appreciate if someone help me out and give me the power shell script to run this job in task schedule job as batch file.

    Kind Regards,
    Tuesday, January 12, 2016 3:34 AM

Answers

  • Tired not to get confused. Please check and let me know. if this is what you are expecting.
    Change the locations accordingly.

    $filenames = gci c:\temp\test*.csv | select -ExpandProperty fullname
    
    foreach($file in $filenames){
    
    $files  = Import-Csv -Delimiter "|" -Path $file -Header column1,column2
    $files | % {
    
    $dt = ((($_.column2 -split(" "))[0]).Replace("-",""))
    $cn2=$dt
    $cn2
    
    $dt= $dt.Substring(0,$dt.Length -2)
    $fn  = "$($_.column1)"+'_'+"$cn2"
    $fn
    
    if(Test-Path "c:\temp\$dt"){
    
    write-host "$dt is available under c:\temp"
    
    if(test-path "c:\temp\$dt\$fn.csv"){
    
    write-host "$fn is available under c:\temp\$dt"
    $ft=@()
    
    $fileContent = Import-csv "c:\temp\$dt\$fn.csv"
    $newRow = New-Object PsObject -Property @{column1="$($_.Column1)";column2="$($_.Column2)"}
    $ft+=$fileContent
    $ft += $newRow
    $ft | Export-Csv "c:\temp\$dt\$fn.csv" -Force -NoTypeInformation 
    
    } 
    else{
    
    
    New-Item -Path "c:\temp\$dt\$fn.csv" -ItemType File
    $ft=@()
    
    $fileContent = Import-csv "c:\temp\$dt\$fn.csv"
    $newRow = New-Object PsObject -Property @{column1="$($_.Column1)";column2="$($_.Column2)"}
    $ft+=$fileContent
    $ft += $newRow
    $ft | Export-Csv "c:\temp\$dt\$fn.csv" -Force -NoTypeInformation 
    
    
    }
    }
    else{
    
    New-Item -Path c:\temp\$dt -ItemType directory
    
    New-Item -Path c:\temp\$dt\$fn.csv -ItemType File
    $ft=@()
    
    $fileContent = Import-csv "c:\temp\$dt\$fn.csv"
    $newRow = New-Object PsObject -Property @{column1="$($_.Column1)";column2="$($_.Column2)"}
    $ft+=$fileContent
    $ft += $newRow
    $ft | Export-Csv "c:\temp\$dt\$fn.csv" -Force -NoTypeInformation 
    
    
    }
    
    
    }
    
    
    }


    Tuesday, January 12, 2016 7:22 AM

All replies

  • Hi,

    Please change the path locations to your directories.

    $files  = Import-Csv -Delimiter "|" -Path c:\temp\test1.csv 
    $files | % {
    $dt = ((($_.column2 -split(" "))[0]).Replace("-",""))
    $cn2=$dt

    $dt= $dt.Substring(0,$dt.Length -2)
    $fn  = "$($_.column1)"+'_'+"$cn2"

    if(Test-Path "c:\temp\$dt"){

    New-Item -Path "c:\temp\$dt\$fn.csv" -ItemType File

    }
    else{

    New-Item -Path c:\temp\$dt -ItemType directory

    New-Item -Path c:\temp\$dt\$fn.csv -ItemType File

    }

    }



    Thanks Azam

    When you see answers please Mark as Answer if Helpful..vote as helpful.


    • Edited by mohdazam89 Tuesday, January 12, 2016 4:18 AM removed sleep
    Tuesday, January 12, 2016 4:01 AM
  • Hi Azam,

    Check the screen shot. I am getting error message while running this script. 

    Also I not creating file name with column0_column1. I need the data save by date and the column0_column1

    Regards,

    Tuesday, January 12, 2016 5:16 AM
  • My requirement is to read my csv “|" delimiter files from source folder as loop using shell script and create new file by using column1_column2 and then move the file to that monthly folder YYYYMM.

    $files  = Import-Csv -Delimiter "|" -Path c:\temp\test1.csv  My requirement is to read my csv “|" delimiter files from source folder
    $files | % {
    $dt = ((($_.column2 -split(" "))[0]).Replace("-",""))
    $cn2=$dt

    $dt= $dt.Substring(0,$dt.Length -2)
    $fn  = "$($_.column1)"+'_'+"$cn2"

    if(Test-Path "c:\temp\$dt"){

    New-Item -Path "c:\temp\$dt\$fn.csv" -ItemType File #create new file by using column1_column2 and then move the file to that monthly folder YYYYMM.

    }
    else{

    New-Item -Path c:\temp\$dt -ItemType directory

    New-Item -Path c:\temp\$dt\$fn.csv -ItemType File

    }

    }

    Is it possible for you to paste the contents of the csv file what your are using.
    From what i understood from your question is

    1.Read my csv “|" delimiter files from source folder 
    2.Create new file with names as the date of  column1_Column2
    3.Copy the Files in to respective directories according to the year and month.

    Reg the above error: What are the properties of the csv file you are reading. If possible post it.

    Thanks
    Azam


    Thanks Azam When you see answers please Mark as Answer if Helpful..vote as helpful.

    Tuesday, January 12, 2016 5:30 AM
  • Hi Azam,

    Here is the content looks like

    007298|2016-01-10 05:45:57
    007298|2016-01-10 05:45:57
    007298|2016-01-11 11:35:45
    007298|2016-01-11 11:35:45
    007298|2016-01-12 10:05:12

    1.Read my csv “|" delimiter files from source folder 

    Yes - You are right there are multiple files in this source folder not just one file. All the files are csv format. Its ftp folder were multiple stores ftp there transaction files overnight

    2.Create new file with names as the date of  column1_Column2

    Yes - thats right but if you notice in my initial post the  column1_Column2 name file should have the same dated set of transactions in it. File and the set of transaction of that date of that store has to split accordingly and then put those transaction in that file , save it column1_Column2 which is storeID_Date .

    3.Copy the Files in to respective directories according to the year and month

    Yes - I will create this now. It was my mistake I didn't realise there is variable already.

    Let me know if you still find it confusing. 

    Thanks, 





    • Edited by ozsql Tuesday, January 12, 2016 5:48 AM
    Tuesday, January 12, 2016 5:43 AM
  • Tired not to get confused. Please check and let me know. if this is what you are expecting.
    Change the locations accordingly.

    $filenames = gci c:\temp\test*.csv | select -ExpandProperty fullname
    
    foreach($file in $filenames){
    
    $files  = Import-Csv -Delimiter "|" -Path $file -Header column1,column2
    $files | % {
    
    $dt = ((($_.column2 -split(" "))[0]).Replace("-",""))
    $cn2=$dt
    $cn2
    
    $dt= $dt.Substring(0,$dt.Length -2)
    $fn  = "$($_.column1)"+'_'+"$cn2"
    $fn
    
    if(Test-Path "c:\temp\$dt"){
    
    write-host "$dt is available under c:\temp"
    
    if(test-path "c:\temp\$dt\$fn.csv"){
    
    write-host "$fn is available under c:\temp\$dt"
    $ft=@()
    
    $fileContent = Import-csv "c:\temp\$dt\$fn.csv"
    $newRow = New-Object PsObject -Property @{column1="$($_.Column1)";column2="$($_.Column2)"}
    $ft+=$fileContent
    $ft += $newRow
    $ft | Export-Csv "c:\temp\$dt\$fn.csv" -Force -NoTypeInformation 
    
    } 
    else{
    
    
    New-Item -Path "c:\temp\$dt\$fn.csv" -ItemType File
    $ft=@()
    
    $fileContent = Import-csv "c:\temp\$dt\$fn.csv"
    $newRow = New-Object PsObject -Property @{column1="$($_.Column1)";column2="$($_.Column2)"}
    $ft+=$fileContent
    $ft += $newRow
    $ft | Export-Csv "c:\temp\$dt\$fn.csv" -Force -NoTypeInformation 
    
    
    }
    }
    else{
    
    New-Item -Path c:\temp\$dt -ItemType directory
    
    New-Item -Path c:\temp\$dt\$fn.csv -ItemType File
    $ft=@()
    
    $fileContent = Import-csv "c:\temp\$dt\$fn.csv"
    $newRow = New-Object PsObject -Property @{column1="$($_.Column1)";column2="$($_.Column2)"}
    $ft+=$fileContent
    $ft += $newRow
    $ft | Export-Csv "c:\temp\$dt\$fn.csv" -Force -NoTypeInformation 
    
    
    }
    
    
    }
    
    
    }


    Tuesday, January 12, 2016 7:22 AM
  • Thank You Azam.

    I will test and let you know. 

    Thanks

    Tuesday, January 12, 2016 9:44 AM