Forum Discussion
change positions
Hello,
you can use Power Query to do that. Power Query is a free add-in from Microsoft for Excel 2010 and 2013 and built into Excel 2016 as Get and Transform.
Create a new query from a file, navigate to the CSV file and open it. Remove the columns you don't need, reorder the columns as you need them and save the query to the worksheet. Save the file as an Excel file.
Then use Save As to save the sheet with the query result as a CSV file.
When the original CSV file changes, you can open the saved Excel file, refresh the Power Query and save the query result as a CSV file again.
And my last problem is that after I run the query I need to delete the data from the source, can I do that?
Hello,
it may take a little while to get the hang of everything that can be done with Power Query.
You can transform values by creating custom columns with functions that manipulate the original data. You may need to use this technique to create a date that Power Query recognizes. Also, there is a setting to specify the locale of the original date.
You can open all files in a specific folder and combine them
You can use VBA in a workbook. Use Power Query to load the data, then use VBA to delete the data source after the Power Query load was successful.
It would be best if you started a new question about a specific problem, so that each issue can be addressed separately.
Thank you it works perfect, the only problem I have now is that when I run it on a batch, I get this error:
Could not find a package session for the given session id
This shows up when I close excel.
The file works ok but no I run it on a batch file and for some weird reason I get this error but the error does not happen if I open the file manually:
Cannot cast null to type system.double
See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.
************** Exception Text **************
Microsoft.Mashup.Client.ClientShared.Com.ComWrapperException: Cannot cast null to type 'System.Double'. ---> System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.Mashup.Client.ClientShared.Com.ComWrapper.As[T](Object value)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Client.ClientShared.Com.ComWrapper.As[T](Object value)
at Microsoft.Mashup.Client.Excel.Com.ExcelComWrapper.AsIntFromDouble(Object value)
at Microsoft.Mashup.Client.Excel.Com.ApplicationFeatures..ctor(IApplication application)
at Microsoft.Mashup.Client.Excel.Com.Application..ctor(Object application, Boolean enableEvents)
at Microsoft.Mashup.Client.Excel.AddIn.TryCreateApplication(Object appObject, IApplication& application)
at Microsoft.Mashup.Client.Excel.AddIn.Extensibility.IDTExtensibility2.OnConnection(Object application, ext_ConnectMode connectMode, Object addInInst, Array& custom)
************** Loaded Assemblies **************
mscorlib
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2114.0 built by: NET47REL1LAST
CodeBase: file:///C:/Windows/Microsoft.NET/Framework64/v4.0.30319/mscorlib.dll
----------------------------------------
Microsoft.Mashup.Client.Excel
Assembly Version: 1.0.0.0
Win32 Version: 2.49.4831.381
CodeBase: file:///C:/Program%20Files/Microsoft%20Power%20Query%20for%20Excel/bin/Microsoft.Mashup.Client.Excel.dll
----------------------------------------
Extensibility
Assembly Version: 7.0.3300.0
Win32 Version: 7.00.9466
CodeBase: file:///C:/Windows/assembly/GAC/Extensibility/7.0.3300.0__b03f5f7f11d50a3a/Extensibility.dll
----------------------------------------
office
Assembly Version: 15.0.0.0
Win32 Version: 15.0.4971.1000
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/office/15.0.0.0__71e9bce111e9429c/office.dll
----------------------------------------
Microsoft.Mashup.Client.Windows
Assembly Version: 1.0.0.0
Win32 Version: 2.49.4831.381
CodeBase: file:///C:/Program%20Files/Microsoft%20Power%20Query%20for%20Excel/bin/Microsoft.Mashup.Client.Windows.DLL
----------------------------------------
Microsoft.Mashup.Document
Assembly Version: 1.0.0.0
Win32 Version: 2.49.4831.381
CodeBase: file:///C:/Program%20Files/Microsoft%20Power%20Query%20for%20Excel/bin/Microsoft.Mashup.Document.DLL
----------------------------------------
Microsoft.Mashup.DocumentServices
Assembly Version: 1.0.0.0
Win32 Version: 2.49.4831.381
CodeBase: file:///C:/Program%20Files/Microsoft%20Power%20Query%20for%20Excel/bin/Microsoft.Mashup.DocumentServices.DLL
----------------------------------------
System
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2114.0 built by: NET47REL1LAST
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System/v4.0_4.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
Microsoft.Mashup.Client.Packaging
Assembly Version: 1.0.0.0
Win32 Version: 2.49.4831.381
CodeBase: file:///C:/Program%20Files/Microsoft%20Power%20Query%20for%20Excel/bin/Microsoft.Mashup.Client.Packaging.DLL
----------------------------------------
System.Drawing
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2053.0 built by: NET47REL1
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Drawing/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------
System.Core
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2116.0 built by: NET47REL1LAST
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Core/v4.0_4.0.0.0__b77a5c561934e089/System.Core.dll
----------------------------------------
Microsoft.Mashup.Client.Models
Assembly Version: 1.0.0.0
Win32 Version: 2.49.4831.381
CodeBase: file:///C:/Program%20Files/Microsoft%20Power%20Query%20for%20Excel/bin/Microsoft.Mashup.Client.Models.DLL
----------------------------------------
System.Windows.Forms
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2114.0 built by: NET47REL1LAST
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Windows.Forms/v4.0_4.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
Microsoft.MashupEngine
Assembly Version: 1.0.0.0
Win32 Version: 2.49.4831.381
CodeBase: file:///C:/Program%20Files/Microsoft%20Power%20Query%20for%20Excel/bin/Microsoft.MashupEngine.DLL
----------------------------------------
Microsoft.Mashup.Client.AddinTelemetry
Assembly Version: 1.0.0.0
Win32 Version: 2.49.4831.381
CodeBase: file:///C:/Program%20Files/Microsoft%20Power%20Query%20for%20Excel/bin/Microsoft.Mashup.Client.AddinTelemetry.DLL
----------------------------------------
Microsoft.Mashup.ProviderShared
Assembly Version: 1.0.0.0
Win32 Version: 2.49.4831.381
CodeBase: file:///C:/Program%20Files/Microsoft%20Power%20Query%20for%20Excel/bin/Microsoft.Mashup.ProviderShared.DLL
----------------------------------------
Microsoft.Office.Interop.Excel
Assembly Version: 15.0.0.0
Win32 Version: 15.0.4569.1506
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/Microsoft.Office.Interop.Excel/15.0.0.0__71e9bce111e9429c/Microsoft.Office.Interop.Excel.dll
----------------------------------------
Microsoft.Mashup.Shims
Assembly Version: 1.0.0.0
Win32 Version: 2.49.4831.381
CodeBase: file:///C:/Program%20Files/Microsoft%20Power%20Query%20for%20Excel/bin/Microsoft.Mashup.Shims.DLL
----------------------------------------
Microsoft.Practices.Unity
Assembly Version: 2.1.505.0
Win32 Version: 2.1.505.2
CodeBase: file:///C:/Program%20Files/Microsoft%20Power%20Query%20for%20Excel/bin/Microsoft.Practices.Unity.DLL
----------------------------------------
System.Configuration
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2053.0 built by: NET47REL1
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Configuration/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
----------------------------------------
System.Xml
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2102.0 built by: NET47REL1LAST
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Xml/v4.0_4.0.0.0__b77a5c561934e089/System.Xml.dll
----------------------------------------
Microsoft.Mashup.EventSource
Assembly Version: 1.0.0.0
Win32 Version: 2.49.4831.381
CodeBase: file:///C:/Program%20Files/Microsoft%20Power%20Query%20for%20Excel/bin/Microsoft.Mashup.EventSource.DLL
----------------------------------------
************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.
For example:
<configuration>
<system.windows.forms jitDebugging="true" />
</configuration>
When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.