Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

daily trick for xls files

Posted on 2014-02-14
24
Medium Priority
?
261 Views
Last Modified: 2014-02-15
To My Respected SQL Server and Scripting Gurus,
I am biting my nailes if this is possible - There is a daily xls file that is system generated. I want it to be unmerged (since it comes that with merged columns) – the goal – using the power of sql server – I want to make it daily task that does this – let me know if this is impossible and I am just dreaming—
Read the xls file and then unmerge all the columns and then import that table into a dest table in sql server – I want to do it via sql server as it is very easy to set it daily- I am open to any suggestion as long as I can automate the daily unmerging of the xls file and then importing that thing into a dest table in sql server
0
Comment
Question by:Rayne
[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
  • 16
  • 8
24 Comments
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39860614
Can you please provide sample data? http://sscce.org/

Thank you.

Dan
0
 

Author Comment

by:Rayne
ID: 39860700
Hello Dan,

i have attached the before  and after look alike worksheet  - please see it

1) Unmerge the entire sheet

2) delete empty columns B & E


3)then, move the summing cell content to the right cell if right cell is empty (see the red fonted text) in its new position

4) move all the relevant period and quarter total headers in one single row
data-This.xlsx
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39860944
OK, the simplest way I can think of to do this is:
1. Convert the file to csv
2. delete all the headers - first 6 lines
3. delete all the lines that contain "Total" - no point in inserting calculated fields in db

After that, you can simply import the csv into your prepared db (with the same number of columns) or add a header to match db table column names.

I'll try to make today a powershell script that will do 1-3.

Dan
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

Author Comment

by:Rayne
ID: 39861522
Hello Dan,

thank you for kindness :)
 One thing I updated the data - so that it has the actual entire year set  - just few extra  columns for all months
data-This-updated1.xlsx
0
 

Author Comment

by:Rayne
ID: 39861525
Dan,

I am not sure on the "remove totals" thing and if user need it- may be we can have a version that doesn't delete the totals :(
0
 
LVL 35

Accepted Solution

by:
Dan Craciun earned 2000 total points
ID: 39861785
Use the following script:
$inputXlsx= "X:\your\path\data-This-updated1.xlsx"
$workFile = "X:\your\path\data.csv"
$outputFile = "X:\your\path\mod_data.csv"
$output = @()                                            # initialize hash output

if(Test-Path $workFile) {Remove-Item $workFile -Force}  # delete work file, if present

$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Open($inputXlsx)           # open the original xlsx
$sheet = $workbook.Worksheets.Item(1)                   # select the first sheet, just in case
$workbook.SaveAs($workFile, 6)                          # save as csv
$workbook.Close($false)                                 # close the file


#remove first 4 lines from file - unneeded headers
(Get-Content $workFile | Select-Object -Skip 4) | Set-Content $outputFile

# imports the generated csv, then constructs the output object
Import-Csv -path $outputFile -Header "District+item", h2, "City+item", "account+item", h5, "Q1 P1", "Q1 P2", "Q1 P3", "Q1 total", "Q2 P4", "Q2 P5", "Q2 P6", "Q2 total", "Q3 P7", "Q3 P8", "Q3 P9", "Q3 total", "Q4 P10", "Q4 P11", "Q4 P12", "Q4 total" |
    %{ if (!($_."City+item" -like '*Total*')){
        $output += [psCustomObject] [Ordered] @{ 
            "District+item" = $_."District+item"; 
            "City+item"     = $_."City+item";
            "account+item"  = $_."account+item"; 
            "Q1 P1"         = $_."Q1 P1" -replace "\$", "" -replace ",", "" -replace " ", "";   # delete "$", "," and " " from numbers 
            "Q1 P2"         = $_."Q1 P2" -replace "\$", "" -replace ",", "" -replace " ", "";   # probably should create a function
            "Q1 P3"         = $_."Q1 P3" -replace "\$", "" -replace ",", "" -replace " ", ""; 
            "Q2 P4"         = $_."Q2 P4" -replace "\$", "" -replace ",", "" -replace " ", "";
            "Q2 P5"         = $_."Q2 P5" -replace "\$", "" -replace ",", "" -replace " ", ""; 
            "Q2 P6"         = $_."Q2 P6" -replace "\$", "" -replace ",", "" -replace " ", ""; 
            "Q3 P7"         = $_."Q3 P7" -replace "\$", "" -replace ",", "" -replace " ", ""; 
            "Q3 P8"         = $_."Q3 P8" -replace "\$", "" -replace ",", "" -replace " ", ""; 
            "Q3 P9"         = $_."Q3 P9" -replace "\$", "" -replace ",", "" -replace " ", ""; 
            "Q4 P10"         = $_."Q4 P10" -replace "\$", "" -replace ",", "" -replace " ", ""; 
            "Q4 P11"         = $_."Q4 P11" -replace "\$", "" -replace ",", "" -replace " ", ""; 
            "Q4 P12"         = $_."Q4 P12" -replace "\$", "" -replace ",", "" -replace " ", ""
            } # end @
        } #end if
      } # end %

#output the final csv
$output | Export-Csv -Path $outputFile -Force -NoTypeInformation

Open in new window


Replace paths as needed.

The script will generate a file (mod_data.csv) ready to be imported into an SQL table.

You will need to create the database and the table to contain this, along with any other data you might need (I suspect you will need the date, at least).

The script removes all calculated rows/columns.
It's bad form to insert a calculated field into SQL, because:
1. you can simply use SELECT SUM(fields) to get the sum
2. if you alter/remove a field that was used to obtain the calculated field, that field loses meaning.

HTH,
Dan
0
 

Author Comment

by:Rayne
ID: 39862009
Hello Dan,

first of all, thank you so much for your gold help.

Just a note: I am getting this error when I ma running in powerShell GUI -
File C:\Users\AYY\AppData\Local\Temp\5deefc25-4262-46d9-849e-31dd4b76c475.ps1 cannot be loaded because the execution of
scripts is disabled on this system. Please see "get-help about_signing" for more details.
At line:1 char:2
+ . <<<<  'C:\Users\AYY\AppData\Local\Temp\5deefc25-4262-46d9-849e-31dd4b76c475.ps1'
    + CategoryInfo          : NotSpecified: (:) [], PSSecurityException
    + FullyQualifiedErrorId : RuntimeException
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39862017
As the error says, you should try to run get-help about_signing in Powershell.

Or, if you want to just skip to the good part, run this:
Set-ExecutionPolicy RemoteSigned

Open in new window


Afterwards, right click on your ps1 file and choose "Unblock".

Dan
0
 

Author Comment

by:Rayne
ID: 39862033
Hello Dan,

I tried to do this:

Windows PowerShell
Copyright (C) 2009 Microsoft Corporation. All rights reserved.

PS C:\Users\ThisUser> Set-ExecutionPolicy RemoteSigned

Execution Policy Change
The execution policy helps protect you from scripts that you do not trust. Changing the execution policy might expose
you to the security risks described in the about_Execution_Policies help topic. Do you want to change the execution
policy?
[Y] Yes  [N] No  [S] Suspend  [?] Help (default is "Y"): Y
Set-ExecutionPolicy : Access to the registry key 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft
.PowerShell' is denied.
At line:1 char:20
+ Set-ExecutionPolicy <<<<  RemoteSigned
    + CategoryInfo          : NotSpecified: (:) [Set-ExecutionPolicy], UnauthorizedAccessException
    + FullyQualifiedErrorId : System.UnauthorizedAccessException,Microsoft.PowerShell.Commands.SetExecutionPolicyComma
   nd

PS C:\Users\ThisUser> Set-ExecutionPolicy RemoteSigned

Open in new window

0
 

Author Comment

by:Rayne
ID: 39862035
When I type in Y, it gives me this error
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39862036
You need to run Powershell as administrator to be able to run set-executionPolicy
0
 

Author Comment

by:Rayne
ID: 39862041
i right on the pst file - where do i get the unblock thing?
0
 

Author Comment

by:Rayne
ID: 39862050
Hello Dan,

I did changed it via Admin mode. Then I copied your code ( with changed the folders and file name) in to a powershell admin windows and this is what get>>

Note_ i have attached the two files that i got from it
data.csv
mod-data.csv
0
 

Author Comment

by:Rayne
ID: 39862052
Windows PowerShell
Copyright (C) 2009 Microsoft Corporation. All rights reserved.

PS C:\WINDOWS\system32> $inputXlsx= "C:\Users\ThisUser\Pictures\test\data-This-updated1.xlsx"
PS C:\WINDOWS\system32> $workFile = "C:\Users\ThisUser\Pictures\test\data.csv"
PS C:\WINDOWS\system32> $outputFile = "C:\Users\ThisUser\Pictures\test\mod_data.csv"
PS C:\WINDOWS\system32> $output = @()                                            # initialize hash output
PS C:\WINDOWS\system32>
PS C:\WINDOWS\system32> if(Test-Path $workFile) {Remove-Item $workFile -Force}  # delete work file, if present
PS C:\WINDOWS\system32>
PS C:\WINDOWS\system32> $excel = New-Object -ComObject Excel.Application
PS C:\WINDOWS\system32> $workbook = $excel.Workbooks.Open($inputXlsx)           # open the original xlsx
PS C:\WINDOWS\system32> $sheet = $workbook.Worksheets.Item(1)                   # select the first sheet, just in case
PS C:\WINDOWS\system32> $workbook.SaveAs($workFile, 6)                          # save as csv
PS C:\WINDOWS\system32> $workbook.Close($false)                                 # close the file
PS C:\WINDOWS\system32>
PS C:\WINDOWS\system32>
PS C:\WINDOWS\system32> #remove first 4 lines from file - unneeded headers
PS C:\WINDOWS\system32> (Get-Content $workFile | Select-Object -Skip 4) | Set-Content $outputFile
PS C:\WINDOWS\system32>
PS C:\WINDOWS\system32> # imports the generated csv, then constructs the output object
PS C:\WINDOWS\system32> Import-Csv -path $outputFile -Header "District+item", h2, "City+item", "account+item", h5, "Q1 P
1", "Q1 P2", "Q1 P3", "Q1 total", "Q2 P4", "Q2 P5", "Q2 P6", "Q2 total", "Q3 P7", "Q3 P8", "Q3 P9", "Q3 total", "Q4 P10"
, "Q4 P11", "Q4 P12", "Q4 total" |
>>     %{ if (!($_."City+item" -like '*Total*')){
>>         $output += [psCustomObject] [Ordered] @{
>>             "District+item" = $_."District+item";
>>             "City+item"     = $_."City+item";
>>             "account+item"  = $_."account+item";
>>             "Q1 P1"         = $_."Q1 P1" -replace "\$", "" -replace ",", "" -replace " ", "";   # delete "$", "," and
 " " from numbers
>>             "Q1 P2"         = $_."Q1 P2" -replace "\$", "" -replace ",", "" -replace " ", "";   # probably should cre
ate a function
>>             "Q1 P3"         = $_."Q1 P3" -replace "\$", "" -replace ",", "" -replace " ", "";
>>             "Q2 P4"         = $_."Q2 P4" -replace "\$", "" -replace ",", "" -replace " ", "";
>>             "Q2 P5"         = $_."Q2 P5" -replace "\$", "" -replace ",", "" -replace " ", "";
>>             "Q2 P6"         = $_."Q2 P6" -replace "\$", "" -replace ",", "" -replace " ", "";
>>             "Q3 P7"         = $_."Q3 P7" -replace "\$", "" -replace ",", "" -replace " ", "";
>>             "Q3 P8"         = $_."Q3 P8" -replace "\$", "" -replace ",", "" -replace " ", "";
>>             "Q3 P9"         = $_."Q3 P9" -replace "\$", "" -replace ",", "" -replace " ", "";
>>             "Q4 P10"         = $_."Q4 P10" -replace "\$", "" -replace ",", "" -replace " ", "";
>>             "Q4 P11"         = $_."Q4 P11" -replace "\$", "" -replace ",", "" -replace " ", "";
>>             "Q4 P12"         = $_."Q4 P12" -replace "\$", "" -replace ",", "" -replace " ", ""
>>             } # end @
>>         } #end if
>>       } # end %
>>
Unable to find type [Ordered]: make sure that the assembly containing this type is loaded.
At line:3 char:46
+         $output += [psCustomObject] [Ordered] <<<<  @{
    + CategoryInfo          : InvalidOperation: (Ordered:String) [], RuntimeException
    + FullyQualifiedErrorId : TypeNotFound

Unable to find type [Ordered]: make sure that the assembly containing this type is loaded.
At line:3 char:46
+         $output += [psCustomObject] [Ordered] <<<<  @{
    + CategoryInfo          : InvalidOperation: (Ordered:String) [], RuntimeException
    + FullyQualifiedErrorId : TypeNotFound

Unable to find type [Ordered]: make sure that the assembly containing this type is loaded.
At line:3 char:46
+         $output += [psCustomObject] [Ordered] <<<<  @{
    + CategoryInfo          : InvalidOperation: (Ordered:String) [], RuntimeException
    + FullyQualifiedErrorId : TypeNotFound

Unable to find type [Ordered]: make sure that the assembly containing this type is loaded.
At line:3 char:46
+         $output += [psCustomObject] [Ordered] <<<<  @{
    + CategoryInfo          : InvalidOperation: (Ordered:String) [], RuntimeException
    + FullyQualifiedErrorId : TypeNotFound

Unable to find type [Ordered]: make sure that the assembly containing this type is loaded.
At line:3 char:46
+         $output += [psCustomObject] [Ordered] <<<<  @{
    + CategoryInfo          : InvalidOperation: (Ordered:String) [], RuntimeException
    + FullyQualifiedErrorId : TypeNotFound

Unable to find type [Ordered]: make sure that the assembly containing this type is loaded.
At line:3 char:46
+         $output += [psCustomObject] [Ordered] <<<<  @{
    + CategoryInfo          : InvalidOperation: (Ordered:String) [], RuntimeException
    + FullyQualifiedErrorId : TypeNotFound

Unable to find type [Ordered]: make sure that the assembly containing this type is loaded.
At line:3 char:46
+         $output += [psCustomObject] [Ordered] <<<<  @{
    + CategoryInfo          : InvalidOperation: (Ordered:String) [], RuntimeException
    + FullyQualifiedErrorId : TypeNotFound

Unable to find type [Ordered]: make sure that the assembly containing this type is loaded.
At line:3 char:46
+         $output += [psCustomObject] [Ordered] <<<<  @{
    + CategoryInfo          : InvalidOperation: (Ordered:String) [], RuntimeException
    + FullyQualifiedErrorId : TypeNotFound

Unable to find type [Ordered]: make sure that the assembly containing this type is loaded.
At line:3 char:46
+         $output += [psCustomObject] [Ordered] <<<<  @{
    + CategoryInfo          : InvalidOperation: (Ordered:String) [], RuntimeException
    + FullyQualifiedErrorId : TypeNotFound

Unable to find type [Ordered]: make sure that the assembly containing this type is loaded.
At line:3 char:46
+         $output += [psCustomObject] [Ordered] <<<<  @{
    + CategoryInfo          : InvalidOperation: (Ordered:String) [], RuntimeException
    + FullyQualifiedErrorId : TypeNotFound

PS C:\WINDOWS\system32> #output the final csv
PS C:\WINDOWS\system32> $output | Export-Csv -Path $outputFile -Force -NoTypeInformation
PS C:\WINDOWS\system32>

Open in new window

0
 

Author Comment

by:Rayne
ID: 39862054
the mod file is empty
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39862058
What operating system are you using?

Looks like the [Ordered] cast is not available in your powershell version.
0
 

Author Comment

by:Rayne
ID: 39862073
Dan

I am Windows 7
0
 

Author Comment

by:Rayne
ID: 39862074
wind 7 enterprice
0
 

Author Comment

by:Rayne
ID: 39862079
64 bit
using.png
0
 

Author Comment

by:Rayne
ID: 39862126
Hello Dan,

I greatly appreciate the level of handwork you put in. Thank you. Meanwhile the issue is still there. Should I open a second question for that - whichever  - I will do it - please suggest...
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39862305
You need Powershell 3 for the [Ordered] cast to work.
While I can modify the script so it does not need that construct, it's a good idea for you anyway to upgrade to Powershell 3.

Get the WMF 3 from here: http://www.microsoft.com/en-us/download/details.aspx?id=34595

Quick notes:
-  on line 19 you might need to modify the 4 to 6 - number of header lines in your file. Let me know if that can change, so I put it as a variable
- the script will convert to csv whatever sheet you saved as active in your excel file. If you have multiple sheets, let me know so I can modify it to select the right sheet before export.

HTH,
Dan
0
 

Author Comment

by:Rayne
ID: 39862341
Hello Dan,

I can't thank you enough - this is not just a brilliant life saver but  might also tempt me to get in powershell direction than a simple but less powerful vbscript for all things.

This change might ultimately be a life changer for me. You have now shown me the torch -  thank you Sire :) for opening my eyes to the world of scripting
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39862347
You can use vbscript for the same end result, but I find Powershell to be faster (for me) to write and it is the recommended MS way for scripting.
I don't think shell scripting and vb scripting will go away anytime soon, but Powershell is the future.

Glad I could help.

Dan
0
 

Author Comment

by:Rayne
ID: 39862351
Thank you Dan :) and yes I want to be in THE future
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
New style of hardware planning for Microsoft Exchange server.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

704 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