• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to load CSV into a Template

R

Ron0000

New Member
I have a CSV with 3 columns of data.
Assume I have imported it to worksheet "data" in a workbook based on a template.
I have a template worksheet that uses those 3 columns of data in columns C, E, G, on worksheet "template"
columns A, B have constant text
columns D, F, H have calculations based on values in C,E,G

Right now, the only way I can think of to get the data into the template in the right places is explicit references in each cell on the template ie
=data!a1 in template!c3, and copy that all down column C?

Is there some powerQuery "magic" I can use?
Or named range trick?
 
This isn't something I have done a lot of but PQ appears to work faultlessly.
The splitting by separator and promoting of headers is done in PQ.
When the data is refreshed in the workbook, changes, including any additional rows, appear in the Table and any calculation columns operate correctly,.
 
Alternative native Excel solution might be to use the legacy feature to load a text/csv file into Excel.
See the last checkbox of the properties settings.
60682
 
Thanks for trying.

Peter, can you point me to more specific instructions how to get PQ to do this?

Guido: how do I get to this command? I googled the option you mention and at least a few people reported problems with it in the past. I suspect it won't quite do what I want

Here is a more visual demonstration of approximately what I want to do
60686

The upper part is my "template" with blank columns for data to be moved in.
The bottom image is my CSV data, in this case imported to Excel.
So what I want to do is some how get data from
CSV column A into template Col B
CSV column B into template Col D
CSV column C into template Col F
 
Ron
A sample pair of files to play with might help.
What I did was limited to importing the CSV data to the left of a table with the formulae held in the rightmost columns.
The read was done using 'From Text/CSV' from within the 'Get &Transform Data' tab.

I am not sure about ways forward but my experience with PQ is limited. Something I tried was to bring in the template text with a separate query (each first saved as a connection only) and then merge the columns in the correct order. Including worksheet formulas within the table was an issue though. They came through as text and needed to be selected and entered again by the user before they functioned as formulas.

I suppose there is the possibility of bringing each separate block from the CSV file in as a separate table and 'hiding the joins'.

Another option for the formulas that doesn't exist now but will soon is to use dynamic array formulas (outside the table) to implement the calculations. These formulas evaluate the entire array as a property of a single cell but displayed it by using a fully dynamic 'spill range'. That would also cope with variable-sized data sets from the CSV file whereas standard formulas a stuck where you place them.
 
Can't help but wondering why not do everything over in PQ? Including adding the columns from the template?
Sample files indeed would help. First offered alternative will not work if we want to insert other columns.
 
You must log in or register to reply here.
Back
Top