Tagged: #SQL; #Web Service; #Data import
December 28, 2016 at 2:27 pm #60909
I have access to a Point of Sales web service from where I can read sales from the registers. I would like to create an automated task in Azure with a PS script that once a day could read the sales and import the numbers to a SQL Azure database.
The source string looks like this (username and password changed): https://login.onlinepos.dk/api/external/?agreementnumber=0123&username=fe3135664ce62ee517caea709ab214ab&password=c0020b0f998c87184b&function=Sales_Export&from=1420412401&to=1424193458
The timestamps needs to be automatically calculated and in EPOCH format when reading data e.g. the last 24 hours each time the runbook run.
When data is read they will have to be imported in to an existing SQL database in a specific view hosted in Azure.
I am a novice into PowerShell but have been told it can do the trick.
Any help will be highly appreciated.
Thanks in advance,
December 29, 2016 at 3:47 pm #60952
Well... what, specifically, do you need help with? Right now you have a pretty high level problem statement, but short of just writing a script for you, I'm not sure what to help with.
December 29, 2016 at 4:33 pm #60970
Here is a short snippet on how to create the "from" and "to" timestamps in EPOCH format (Unix timestamp).
$now = [DateTime]::UtcNow $epochBase = [DateTime]'1970-01-01 00:00:00' $fromEpoch = [UInt32]($now.AddHours(-24) - $epochBase).TotalSeconds $toEpoch = [UInt32]($now - $epochBase).TotalSeconds $fromEpoch $toEpoch
To invoke tha actual request reading data from the web service I would attempt to use the PowerShell cmdlet Invoke-RestMethod cmdlet. As alternative Invoke-WebRequest if the API doesn't return a JSON document.
1. Check out the examples of the Invoke-RestMethod and Invoke-WebRequest documentation pages:
2. Use your Bing/Google fue to look for examples related to Invoke-RestMethod.
3. Have a look at the code of this PowerShell module for the OpenWeather API from a fellow PowerSheller:
I hope above is helpful to get you going further. Please let us know any further questions. If you code already, please feel free to post it in "pre" tags or reference a GitHub Gist.
December 30, 2016 at 7:30 am #60987
Thank you for your help. I must admit this will be too complicated for me and I will need some assistance for making the script right. I think I will go ahead and find a freelance consultant who can assist me on this one.
Thanks again and a happy new year 🙂
You must be logged in to reply to this topic.