Solved

Power shell script to read text file and populate data to SQL database tables

Posted on 2013-12-11
6
500 Views
Last Modified: 2014-02-06
I am new to power shell so need you help in this guys. I have attached a word file detailing the requirement.
Summary.docx
20130930-OPEQCNTRL
20130930-OPEQBAL
20130930-OPEQINT
20130930-OPEQLMT
TestOPDB.sql
OP-EQ-BAL.txt
OP-EQ-INT.txt
OP-EQ-LMT.txt
0
Comment
Question by:josyp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 70

Expert Comment

by:Qlemo
ID: 39714232
Here is the frame to execute your stuff. But there are some flaws in your "dynamic" design - you need to know the file-to-table relation, or set up a rule like
20130930_OPEQBAL
DDDDDD_AABBCCC  => Table AA_BB_CCC

Open in new window

and your parameter file wouldn't help with that, or would it make more complicated than needed.

The parameter file isn't a bad idea, but it is much easier to keep just some vars in the beginning of the script, unless you need to run the same script with different parameter files.

And I would not use BCP. It is easy to access MSSQL from PS, so I would choose that approach (using DataReader or other .NET methods).
0
 

Author Comment

by:josyp
ID: 39720842
The frame that you pasted only contains two lines.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 39721102
Strange, I'm pretty sure I posted some code ... I remember I've checked the archive folder for the most recent control files and such.

Your question consists of several parts:
1. Using a parameter file or similar to configure paths and names
2. Waiting for a control file and the respective files (noted therein) to be written until 7:00 AM.
3. Comparing against an archive folder - only if current files are newer they should be considered
4. Parsing/converting the data files in a way they can be processed with BCP
5. Running the BCP import into respective tables

Part 4 is discussed in http://www.experts-exchange.com/Q_28318533.html.

Part 1 should be discussed in another thread as soon as we have crafted all the other stuff.

Parts 2 and 3 are what I had posted here as a code frame, now lost in space. I'll have to rebuild that.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 70

Expert Comment

by:Qlemo
ID: 39721149
This is what I've put together so far. It does all the checking; doing the data import with BCP would be the next step now.
$DataFolder     = 'C:\Data'
$ArchiveFolder  = 'C:\Archive'
$CtrlFileSuffix = '_OPEQCNTRL'
$LogFile        = 'C:\Data\Log.txt'

$CtrlFile = "$(get-date -format yyyymmdd)$CtrlFileSuffix"

# Check for control file, exit if not created
# needs to get started after midnight!
while ((Get-Date).Hour -lt 7 -and !(Test-Path "$DataFolder\$CtrlFile"))
{
  Start-Sleep 20*60 # wait 20 minutes
}
if (!(Test-Path "$DataFolder\$CtrlFile"))
{
  "$(get-date) - Error: Control file not found" | Out-File -Append $LogFile
  return
}

# Log file exists, check against archive files
if (
    (Get-ChildItem "$ArchiveFolder\*$CtrlFileSuffix" -Name |
      sort -Descending |
      select -First 1
    ).Name -ge $CtrlFile
   )
{
  "$(get-date) - Error: No more recent control file found" | Out-File -Append $LogFile
  return
}

# Check for data files as noted in control file
$datafiles = Import-Csv "$DataFolder\$CtrlFile" -Header Type, Row, Name |
             ? { $_.Type -eq '02' } |
             Select -ExpandProperty Name

if ($missing = $datafiles | ? { !(Test-Path "$DataFolder\$_") })
{
  "$(get-date) - Error: Missing data files $($missing -join ',')" | Out-File -Append $LogFile
  return
}
Remove-Variable missing

# -- All checks done, BCP preps will start here, based on $datafiles ----

Open in new window

0
 

Author Comment

by:josyp
ID: 39735539
I don't see any thing happening with the above code tried to rename the control file to the current date and still not able to see anything happening, there is no log file being generated.
0
 
LVL 70

Accepted Solution

by:
Qlemo earned 500 total points
ID: 39739128
There was a bug in the checking stuff for the data files, and some other issues, so I've changed some more and introduced a logging function to see the log contents both on screen and in a file.
cls
Set-StrictMode -version Latest

<# Parameter section #>
$DataFolder     = 'C:\Data'
$ArchiveFolder  = 'C:\Archive'
$CtrlFileSuffix = '_OPEQCNTRL'
$LogFile        = 'C:\Data\Log.txt'
<# End #>

$CtrlFile = "$(get-date -format yyyyMMdd)$CtrlFileSuffix"

function write-log ([String]$msg)
{
  Write-Host $msg
  "$(get-date -format 'yyyy-MM-dd HH:mm:ss') - $msg" | Out-File -Append $LogFile
}

# Check for control file, exit if not created
# needs to get started after midnight!
while ((Get-Date).Hour -lt 7 -and !(Test-Path "$DataFolder\$CtrlFile"))
{
  Start-Sleep 20*60 # wait 20 minutes
}
if (!(Test-Path "$DataFolder\$CtrlFile"))
{
  write-log "Error: Control file '$DataFolder\$CtrlFile' not found"
  return
}

# Log file exists, check against archive files
if (
    (Get-ChildItem "$ArchiveFolder\*$CtrlFileSuffix" -Name |
      sort -Descending |
      select -First 1
    ) -ge $CtrlFile
   )
{
  write-log 'Error: No more recent control file found'
  return
}

# Check for data files as noted in control file
$datafiles = Import-Csv "$DataFolder\$CtrlFile" -Header Type, Row, Name -Delimiter '|' |
             ? { $_.Type -eq '02' } |
             Select -ExpandProperty Name

if ($missing = $datafiles | ? { !(Test-Path "$DataFolder\$_") })
{
  write-log "Error: Missing data file(s) $($missing -join ',')" 
  return
}
Remove-Variable missing

# -- All checks done, BCP preps will start here, based on $datafiles ----

Open in new window

Please note that you have posted files of format *-* here, while they should be *_* according to your description and the control file content.
Also note that http:/Q_28318533.html should use $datafiles, which contains the file names of the data files, if we put both scripts together.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

751 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