I have a power shell script that loads data from a ftp location into sql Server. The script currently loads only yesterdays file. How do I make it look for last 7 days files?

Here is my script

. "$PSScriptRoot\Send-MailMessage.ps1"
. "$PSScriptRoot\DB.ps1"

if ($sqlserver -eq $null)
{
    throw 'Missing database configuration!'
} 
$database = 'XXXX' 
$table = 'dbo.Stage_Data'
$csvDir = "$PSScriptRoot\ResRefNums"
$csvdelimiter = ','
$firstRowCsv = 'Column_1,Column_2,Column_3,Column_4,Column_5,Column_6,Column_7'
$firstRowPipe = 'Column_1|Column_2|Column_3|Column_4|Column_5|Column_6|Column_7'
$databaseCols = 'Column_1,Column_2,Column_3,Column_4,Column_5,Column_6,Column_7'

try
{
Set-Location $PSScriptRoot
$date =  $((Get-Date).AddDays(-1).ToString('dd.MM.yyyy'));

$FileToProcess = "DataFeed_" + $date + ".csv"
if (!(Test-Path "abcd"))
{
    write-output "Remote Dir not found"
}
Copy-Item -Path  "abcd" -Destination $csvDir
$totalRows = 0

if (!(Test-Path $csvDir)){
    write-output "Creating $csvDir"
    mkdir $csvDir    
}
#  $files = Get-ChildItem -file "$csvDir\\*.csv"
$files = Get-ChildItem -file "$csvDir\\$FileToProcess"
for ($i=0; $i -lt $files.Count; $i++)
{
    # CSV variables;  
    $csvfile = $files[$i].FullName
    if (Test-Path "$csvfile.txt")
    {
        write-output "Already imported, skipping: $csvfile."
        continue
    }
    if (Test-Path "$csvfile.err")
    {
        write-output "Previous error, skipping: $csvfile."
        continue
    }

    $rows = 0 
    #get list of csv files to process
    If (!(Test-Path $csvfile)){
        write-output "Cannot find $csvfile"
        continue
    }
    $batchsize = 50000 

    # Build the sqlbulkcopy connection, and set the timeout to infinite 
    $connectionstring = "Data Source=$sqlserver;Integrated Security=true;Initial Catalog=$database;" 
    $bulkcopy = new-object ("Data.SqlClient.Sqlbulkcopy") $connectionstring 
    $bulkcopy.DestinationTableName = $table 
    $bulkcopy.bulkcopyTimeout = 60 
    $bulkcopy.batchsize = $batchsize 
    $bulkcopy.EnableStreaming = 1 

    # Create the datatable, and autogenerate the columns. 
    $datatable = New-Object "System.Data.DataTable" 

    # Open the text file from disk 
    $reader = new-object System.IO.StreamReader($csvfile)
    $line = $reader.ReadLine()
    $reader.Close()
    #csv headers
    $header = @()
    if ($line -eq $firstRowCsv)
    {
        $date = get-date
        $outmsg = $date.ToString() + ":" + "Detected commas in first row of $csvfile"
        Write-Output $outmsg
        $header = $firstRowCsv.Split($csvdelimiter) 
    }
    elseif ($line -eq $firstRowPipe)
    {
        $date = get-date
        $outmsg = $date.ToString() + ":" + "Detected pipes in first row of $csvfile"
        Write-Output $outmsg 
        $csvdelimiter = "|"
        $header = $firstRowPipe.Split($csvdelimiter)
    }
    else
    {
       # $date = get-date
        $outmsg = $date.ToString() + ":" + "First row does not match expected columns, skipping $csvfile"
        Write-Output $outmsg 
        $outmsg | Out-File "$csvfile.err"

        $params = @{ 
            Body = $outmsg 

           Attachments = @($csvfile)
            Priority = 'High' 
        }

        # send email fail, do not fail job
        Send-MailMessage @params
        Write-Output "Sent failure email."
    }

    $dbCols = $databaseCols.Split(',')

    $null = $datatable.Columns.AddRange($dbCols)
    $rownum = 1; #add 1 for column headers
    # Match database column order!!!
    Import-CSV -Delimiter $csvdelimiter $csvfile | Foreach-Object{
        $row = $datatable.NewRow()
        $rownum++;
        for ($c=0; $c -lt $header.Count; $c++)
        {
            $row.$($dbCols[$c]) = $_.$($header[$c])
        }
        $datatable.Rows.Add($row)
    }

    $bulkcopy.WriteToServer($datatable)

  #  $date = get-date
    $outmsg = $date.ToString() + ":" + $datatable.Rows.Count.ToString() + " rows have been inserted into the database from " + $csvfile
    $datatable.Clear()
$updateQuery = "UPDATE " + $table + " SET Source = 'B'
  WHERE DNIS IS NULL 
  AND LEN(Source) <> 1"

if ($updateQuery.Length -gt 0)
{
    Write-Output "Updating SentToVIQFlag in databasename.dbo.tabelname..."
    # $connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
    # $connectionString = [string]::Format($connectionTemplate, $server, $database)
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $connectionString
    $connection.Open()

    $command = New-Object System.Data.SqlClient.SqlCommand
    $command.connection = $connection
    $command.CommandTimeout = 600000
    $command.CommandText = $updateQuery
    $rowsAffected = $command.ExecuteNonQuery()
    $connection.Close()

    Write-Output "Database updated ($rowsAffected rows)."
}

    $outmsg | Out-File "$csvfile.txt"

    Write-Output $outmsg
}
Write-Output "Script Complete"
exit 0;
}
catch [Exception]
{
    Write-Output $_.Exception.Message
    exit 1;
}

closed as unclear what you're asking by sp_BlitzErik, Kin, RDFozz, hot2use, mustaccio Oct 14 '17 at 16:17

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Welcome to the site! It would help us to help you if you posted what you already have... – Mr.Brownstone Oct 13 '17 at 17:53
  • 2
    Subtract seven days from today's date instead of 1? – sp_BlitzErik Oct 13 '17 at 17:56
  • In that case it gives me only 1 days file . I want all the files from past 7 days. – Kaya Oct 13 '17 at 17:58
  • We don't know how you're finding yesterday's file now. You may need to use a different method; that's where seeing the current code helps. Also, are old files normally removed from the FTP site? If not, and if you're going to keep running this daily, will you need to avoid picking up files you've already picked up and processing them multiple times? do you have a plan to handle that? – RDFozz Oct 13 '17 at 18:13
up vote 1 down vote accepted

Your script only ever attempts to process a single date, you don't produce a list of dates to be processed. You can simply resolve this by just wrapping the FTP call in a for loop:

# always cache the root date as multiple calls to Get-Date could cross day boundaries...
$startdate = Get-Date

for($offset = -7; $offset -lt 0; $offset++)
{
    $date =  $($startDate.AddDays($offset).ToString('dd.MM.yyyy'));

    $FileToProcess = "DataFeed_" + $date + ".csv"

    # the rest of your script...
}

Not the answer you're looking for? Browse other questions tagged or ask your own question.