import XLSX?

Feb 24, 2016 at 8:54 AM
Hi, I'm trying to figure out how to import an XLSX file, but I don't see anything in the docs.
Help?

bkp
Coordinator
Feb 24, 2016 at 10:08 AM
Hi,

First of all import the module, then you'll be able to find and use the Import-OOXML cmdlet.

This Cmdlet is pretty straight forward as shown in the following example :
Import-OOXML -FileFullPath C:\Temp\DevBook.xlsx -WorksheetNumber 1 -KeepDataType
Always try the Get-Help cmdlet to find help about Cmdlet usage.
Get-Help Import-OOXML -Detailed
Have a nice day ;-)
Marked as answer by Avalon77 on 2/24/2016 at 3:09 AM
Feb 25, 2016 at 7:40 AM
Thanks, but now I am receiving the error, "You cannot call a method on a null-valued expression."
I can successfully open the XLSX spreadsheet with Excel, Numbers, and OpenOffice.

(I tried both from a UNC path as well as a local path.)
Coordinator
Feb 25, 2016 at 8:16 AM
Could you give me an XSLX file giving this problem so I can troubleshoot your problem and maybe find a solution ?

Thanks
Feb 25, 2016 at 9:48 AM
Edited Feb 25, 2016 at 1:32 PM
So, I didn't realize that Excel must be installed on the machine. I was under the impression that your DLL took the place of that. Sorry for the misunderstanding. We don't normally install productivity apps on our servers, but I did install Excel on one server. The error above went away, but now I'm getting a new error.
Add-Type : c:\Users\user\AppData\Local\Temp\xtv255wu.0.cs(16) : Keyword, identifier, or string expected 
after verbatim specifier: @
c:\Users\user\AppData\Local\Temp\xtv255wu.0.cs(15) : public string @GraduationYear;
c:\Users\user\AppData\Local\Temp\xtv255wu.0.cs(16) : >>> public string @;
c:\Users\user\AppData\Local\Temp\xtv255wu.0.cs(17) : }
At C:\Windows\system32\WindowsPowerShell\v1.0\Modules\ExcelPSLib\ExcelPSLib.psm1:2564 char:26
+                     try {Add-Type -Language CSharp -TypeDefinition $FinalClassDe ...
+                          ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (c:\Users\user...im specifier: @:CompilerError) [Add-Type], Ex 
   ception
    + FullyQualifiedErrorId : SOURCE_CODE_ERROR,Microsoft.PowerShell.Commands.AddTypeCommand
  
Cannot add type. Compilation errors occurred.
I'll see if I can make a copy which contains less sensitive information and still reproduces the error.

Thanks again!
Feb 25, 2016 at 1:29 PM
Edited Feb 25, 2016 at 1:44 PM
Ok, more info...

I get the first error each time I do use the syntax suggested:
Import-OOXML -FileFullPath "filename.xlsx" -WorksheetNumber 1 -KeepDataType
You cannot call a method on a null-valued expression.
When I remove -KeepDataType, then I get:
Import-OOXML -FileFullPath "C:\Users\user\Copy.xlsx" -WorksheetNumber 1
Add-Type : c:\Users\user\AppData\Local\Temp\ofwj204o.0.cs(11) : Keyword, identifier, or 
string expected after verbatim specifier: @
c:\Users\user\AppData\Local\Temp\ofwj204o.0.cs(10) : public string @GraduationYear;
c:\Users\user\AppData\Local\Temp\ofwj204o.0.cs(11) : >>> public string @;
c:\Users\user\AppData\Local\Temp\ofwj204o.0.cs(12) : }
At C:\Windows\system32\WindowsPowerShell\v1.0\Modules\excelpslib\ExcelPSLib.psm1:2564 char:26
+                     try {Add-Type -Language CSharp -TypeDefinition $FinalClassDe ...
+                          ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (c:\Users\user...im specifier: @:CompilerError) 
    [Add-Type], Exception
    + FullyQualifiedErrorId : SOURCE_CODE_ERROR,Microsoft.PowerShell.Commands.AddTypeCommand
 
Add-Type : c:\Users\user\AppData\Local\Temp\ofwj204o.0.cs(11) : Invalid token '@' in 
class, struct, or interface member declaration
c:\Users\user\AppData\Local\Temp\ofwj204o.0.cs(10) : public string @GraduationYear;
c:\Users\user\AppData\Local\Temp\ofwj204o.0.cs(11) : >>> public string @;
c:\Users\user\AppData\Local\Temp\ofwj204o.0.cs(12) : }
At C:\Windows\system32\WindowsPowerShell\v1.0\Modules\excelpslib\ExcelPSLib.psm1:2564 char:26
+                     try {Add-Type -Language CSharp -TypeDefinition $FinalClassDe ...
+                          ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (c:\Users\user...ber declaration:CompilerError) 
    [Add-Type], Exception
    + FullyQualifiedErrorId : SOURCE_CODE_ERROR,Microsoft.PowerShell.Commands.AddTypeCommand
 
Cannot add type. There were compilation errors.
PM me with an email address so I can send you the file.
Coordinator
Feb 25, 2016 at 3:38 PM
Edited Feb 25, 2016 at 3:40 PM
First of all, you do NOT need Excel or anything else to be installed to use my Powershell Module, ONLY the EPPLUS.dll is required ;-)

Concerning your problem I have an idea ;-) The Import-OOXML only works with simple format. If you have 5 columns the first row is the headers and the other row are the datas, do no let any data outside the table. I'l PM you but I'm about 100% sure that... "string expected after verbatim specifier: @" mean that an expected column header is empty...

I can't PM you it is written "User prefers to not be contacted"