Log In
Register
Home
December 4, 2018
Offline
Hi Team,
I need a VBA script that can copy multiple text file rows and paste in excel columns.
See attached example of file contents and expected excel file output.
Rgds.
See Answer
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Themba,
It's easier with Power Query,
see file attached: just put all text files in a folder, change the path to that folder in the excel output file and press Refresh All button from Data tab.
The only problem I see is that you have a header file mixed between data files, not obvious which file has the headers.
December 4, 2018
Offline
Thanks Catalin,
This will greatly assist.
The files do not necessarily have a header file as they all contain data.
Is there a way of increase the number of columns to be pulled as the files I'm working with have various number of rows.
Rgds.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
All you have to do is to replace in the query:
= Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"})
with:
= Table.ExpandTableColumn(#"Removed Columns", "Custom", Table.ColumnNames(Table.Combine(#"Removed Columns"[Custom])), Table.ColumnNames(Table.Combine(#"Removed Columns"[Custom])))
Answers Post
