Formatting data into a excel file

I want to format the data i extract from one file into the format of the other file. I have looked into reporting tools but they seem very tedious and I am not even sure how I would use them to build my template. Any easy ways to transform my data into this sheet? (I need to keep formatting as this sheet)

Here is the incoming data,

(Some columns missing in photo but just assume they are there)

Here is the format I want it in,

(The red highlighting is where data will go and the field name is to the left, everything else should stay the same always)

Transformation

Accepted answers

Yoshiro_Fujimori

Hi @Jackf99 ,

I guess your case is similar to the case of this Blog page.

https://community.alteryx.com/t5/Engine-Works/The-Blob-Reporting-That-Isn-t-From-a-Horror-Film/ba-p/970280

If you know each field name and its coordinate (Cell No) in the sheet, you can make it in a table,

create a full path of the data range with Formula tool,

and put it to output data tool as below.

I hope this helps. Good luck.

BlobTemplate.yxzp

Yoshiro_Fujimori

@Jackf99 ,

As "Access Denied" Error has not occurred on my PC, I am not exactly sure what is happening.

But if the error occurred at the last Output Data Tool,

I guess the tool may be trying to open the file for updating the 2nd cell, before closing the same file for the 1st cell.

If it is the case, separating the output jobs may solve the conflict of the file access.

In the attachment, I packaged the output process in a batch macro, hoping the file is closed at the end of each iteration.

I hope it works on your end.

Main Workflow

Batch Macro

BlobTemplate2.yxzp

All comments

Yoshiro_Fujimori

Hi @Jackf99 ,

I guess your case is similar to the case of this Blog page.

https://community.alteryx.com/t5/Engine-Works/The-Blob-Reporting-That-Isn-t-From-a-Horror-Film/ba-p/970280

If you know each field name and its coordinate (Cell No) in the sheet, you can make it in a table,

create a full path of the data range with Formula tool,

and put it to output data tool as below.

I hope this helps. Good luck.

BlobTemplate.yxzp

CoG

I learned something new today. Thanks, @Yoshiro_Fujimori !

Jackf99

Thanks for the help! This is exactly what I was looking for. I looked at the blob post before but it confused me your workflow clears everything up.

Jackf99

The solution works on and off, sometimes I am getting a unable to create backup, access denied error. Do you know how to fix this? I think its happening because the blobs .bak file stops the output from creating one. Im not sure what to do at this point

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335