March 15, 2021 at 8:15 pm
Here is something that might work:
param (
[parameter(mandatory = $false, ValueFromPipelineByPropertyName = $true)][string]$Sender = "038962429",
[parameter(mandatory = $false, ValueFromPipelineByPropertyName = $true)][datetime]$FileDate = "2021-03-09",
[parameter(mandatory = $false, ValueFromPipelineByPropertyName = $true)][string]$RecordType = "PO"
)
# Convert input date to string YYMMDD
$strFileDate = $FileDate.ToString("yyMMdd");
# Get list of files
$fileList = Get-ChildItem -Path C:\Temp | Where-Object {$_ -like "*temp_edi_*.txt"};
$fileList | ForEach-Object {
$fileName = $_.FullName;
# Get the first 300 bytes from the file
$bytes = Get-Content $fileName -Encoding byte -TotalCount 300;
$fileData = [System.Text.Encoding]::Unicode.GetString($bytes);
# Validate this is an EDI file
if ($fileData.Substring(1,3) -eq "ISA") {
# Get the data element separator and segment element separator
$dataElement = $fileData.Substring(4,1);
$segmentElement = $fileData.Substring(106,1);
# Split first row based on segment and data element separators - Index = 0
$firstRow = $fileData.Split($segmentElement)[0].Split($dataElement);
# If we match the sender and the date - get the second row and check the record type
if ($firstRow[6].Trim() -eq $Sender -and $firstRow[9] -eq $strFileDate) {
# Get the second row based on the segment and data element separators - Index = 1
$secondRow = $fileData.Split($segmentElement)[1].Split($dataElement);
if ($secondRow[1] -eq $RecordType) {
# Copy the file to the new location
Copy-Item -Path $fileName -Destination "C:\Temp\Archive\$($filename)" -WhatIf;
}
}
}
}
The basic logic:
Notice also the parameters - I set them to Mandatory=$false and provided default values. If you set default values - then someone calling the script doesn't need to provide them, but can always do something like:
PS> .\ScriptFile.ps1 -Sender 1234567 -FileDate 2021-03-09
PS> .\ScriptFile.ps1 -Sender 1212121 -RecordType IN
PS> .\ScriptFile.ps1 -FileDate 2021-03-09
For this - I expect you want to set the RecordType default to PO and prompt for the sender and file date.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 16, 2021 at 12:27 am
param (
[parameter(mandatory = $false, ValueFromPipelineByPropertyName = $true)][string]$sender,
[parameter(mandatory = $false, ValueFromPipelineByPropertyName = $true)][datetime]$fileDate,
[parameter(mandatory = $false, ValueFromPipelineByPropertyName = $true)][string]$RecordType
)
# Convert input date to string YYMMDD
$strFileDate = $FileDate.ToString("yyMMdd");
# Get list of files
$fileList = Get-ChildItem -Path C:\ftp_in\ | Where-Object {$_ -like "*.int"};
$fileList | ForEach-Object {
$fileName = $_.FullName;
# Get the first 300 bytes from the file
$bytes = Get-Content $fileName -Encoding byte -TotalCount 300;
$fileData = [System.Text.Encoding]::UTF8.GetString($bytes);
$fileData.Substring(0,3)
# Validate this is an EDI file
if ($fileData.Substring(0,3) -eq "ISA") {
# Get the data element separator and segment element separator
$dataElement = $fileData.Substring(3,1);
$segmentElement = $fileData.Substring(106,1);
# Split first row based on segment and data element separators - Index = 0
$firstRow = $fileData.Split($segmentElement)[0].Split($dataElement);
$firstRow[6].Trim()
# If we match the sender and the date - get the second row and check the record type
if ($firstRow[6].Trim() -eq $Sender -and $firstRow[9] -eq $strFileDate) {
# Get the second row based on the segment and data element separators - Index = 1
$secondRow = $fileData.Split($segmentElement)[1].Split($dataElement);
if ($secondRow[1] -eq $RecordType) {
# Copy the file to the new location
Copy-Item -Path $fileName -Destination "C:\Temp\Archive\$($filename)" -WhatIf;
}
}
}
}
First off .. Many Thanks!!! -- script works
I had to modify slightly after running thru debug with actual data values. Can I call the script using any one of the 3 parms? sometimes they will want any file that has sender and not search by date or recordtype, or they will search by date and not sender or
recordtype, or maybe they might just want to find certain recordtype, and not sender or date. I guess what I'm asking if a parm is supplied use it for lookup, and if not just use what was given. I was also thinking about another parm for source and if the source is Inbound then I default the -path do a certain share in the script..
Thanks ...
March 16, 2021 at 12:33 am
I provided examples how to call the script with different parameters.
You can add any other parameters needed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 16, 2021 at 1:11 am
When I call with just sender parn
You cannot call a method on a null-valued expression.
At C:\PowerShell\edi_archive_lookup.ps1:8 char:1
+ $strFileDate = $FileDate.ToString("yyMMdd");
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
March 16, 2021 at 1:49 am
You have to set a default value for the parameter, as in my example.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 16, 2021 at 11:32 am
If I set a default value that maybe not the date I'm searching for that sender or the RecordType.
or if I search for Date it maybe not the sender(if I set default values). I want the user of the script to pick there search criteria of any one of the 3 parms they choose to fill in.
Thanks.
March 16, 2021 at 2:01 pm
Another option might be to default the parameters to wild cards and use -like instead of -eq in your comparisons since everything is strings.
Otherwise you're going to have to write a more involved control flow based on what parameters they may or may not have passed(you'll also have to set the parameters to not be required). So something like,
If($sender -ne $null -and $fileDate -ne $null -and $RecordType -ne $null)
{
.....
}
ElseIf($sender -eq $null -and $fileDate -ne $null -and $RecordType -ne $null)
{
.....
}
And so forth. You could also try to dynamically create the search/file copy but for 3 parameters that's probably not worth the effort.
March 16, 2021 at 3:10 pm
Could you inject that into the code so I can see how that may work, do I still you default values in script?
Thanks for response..
March 16, 2021 at 4:17 pm
Just the parts that would be impacted,
For the params, if you want to keep the date as a datetime to let powershell auto validate the format you'll need to check for that explicitly,
param(
[string]$sender = "*",
[datetime]$fileDate,
[string]$RecordType = "*"
)
If($fileDate -eq $null)
{
$strFileDate = "*"
}
Else
{
$strFileDate = $FileDate.ToString("yyMMdd")
}
And then your search specs would look like, this is assuming the data in the file is clean enough you won't have over lap in the results.
if ($firstRow[6].Trim() -like $Sender -and $firstRow[9] -like $strFileDate) {
# Get the second row based on the segment and data element separators - Index = 1
$secondRow = $fileData.Split($segmentElement)[1].Split($dataElement);
if ($secondRow[1] -like $RecordType) {
# Copy the file to the new location
Copy-Item -Path $fileName -Destination "C:\Temp\Archive\$($filename)" -WhatIf;
}
}
March 16, 2021 at 5:13 pm
Okay - we can make the parameters required and allow for blank values. For further information on how to setup and configure parameters, see this article: https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_functions_advanced_parameters?view=powershell-7.1
param (
[parameter(mandatory = $true, ValueFromPipelineByPropertyName = $true)][AllowEmptyString()][string]$Sender,
[parameter(mandatory = $true, ValueFromPipelineByPropertyName = $true)][AllowEmptyString()][string]$FileDate,
[parameter(mandatory = $true, ValueFromPipelineByPropertyName = $true)][AllowEmptyString()][string]$RecordType
)
# Get list of files
$fileList = Get-ChildItem -Path C:\Temp | Where-Object {$_ -like "*temp_edi_*.txt"};
$fileList | ForEach-Object {
$fileName = $_.FullName;
# Get the first 300 bytes from the file
$bytes = Get-Content $fileName -Encoding byte -TotalCount 300;
$fileData = [System.Text.Encoding]::Unicode.GetString($bytes);
# Validate this is an EDI file
if ($fileData.Substring(1,3) -eq "ISA") {
# Get the data element separator and segment element separator
$dataElement = $fileData.Substring(4,1);
$segmentElement = $fileData.Substring(106,1);
# Split first row based on segment and data element separators - Index = 0
$firstRow = $fileData.Split($segmentElement)[0].Split($dataElement);
# If we match the sender and the date - get the second row and check the record type
if (($firstRow[6].Trim() -eq $Sender -or $Sender -eq "") -and ($firstRow[9] -eq $FileDate -or $FileDate -eq "")) {
# Get the second row based on the segment and data element separators - Index = 1
$secondRow = $fileData.Split($segmentElement)[1].Split($dataElement);
if ($secondRow[1] -eq $RecordType -or $RecordType -eq "") {
# Copy the file to the new location
Copy-Item -Path $fileName -Destination "C:\Temp\Archive\$($filename)" -WhatIf;
}
}
}
}
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 16, 2021 at 7:24 pm
Thanks for replies... I'll test that code out..
Do you anticipate with 1000's of files to search this lookup to be slow?
Thanks.
March 16, 2021 at 7:57 pm
Thanks for replies... I'll test that code out..
Do you anticipate with 1000's of files to search this lookup to be slow?
Thanks.
Not sure - no real way to determine how fast it will be until you test it. I do have one additional change - you need to validate that at least one parameter is selected. To do that, add this to the script prior to getting the list of files:
# Check for at least one parameter selected
if ($Sender -eq "" -and $FileDate -eq "" -and $RecordType -eq "") {
Write-Host -ForegroundColor Yellow "At least one parameter must be selected. Please try again.";
Exit;
}
If you select all three as blanks - then all files would be matched and returned. The above forces at least one of the parameters to be selected.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 16, 2021 at 8:11 pm
Thanks for update to script...
I started it and got a message:
You cannot call a method on a null-valued expression
Error came from:
if (($firstRow[6].Trim() -eq $Sender -or $Sender -eq "") -and ($firstRow[9] -eq $FileDate -or $FileDate -eq "")) {
I ran it with FileDate and RecordType.. it's still running but through out this error every so often.
Many tahnks for the followups..
March 16, 2021 at 8:40 pm
Add an output statement to show which file is being processed - then review the file to see why the data is not being parsed as expected. The error is probably being generated because $firstRow[6] is null and you cannot call Trim on a null value.
The code is seeing the file as an ISA file (first 3 characters equals ISA) - but for some reason we are not getting the correct element separators.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 16, 2021 at 9:12 pm
You may also need to increase the number of bytes - that could be causing the 'second' row to be missing data that could cause an issue. Change the line to:
$bytes = Get-Content $fileName -Encoding byte -TotalCount 500 -ReadCount 500;
Adding the ReadCount might improve performance of reading the characters, but not sure you will see that any difference because that portion will be much less time consuming than getting the list of files and copying the files.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 31 through 45 (of 88 total)
You must be logged in to reply to this topic. Login to reply