Columns shift

Question

I have used python tool to collate data from multiple files to process in the workflow. When I checked the output some columns have shifted to right in the excel output like below. I want to know how to rectify this. There are lot of records so I'm finding it difficult to come with some logic to do this.

NAMEDATEAMOUNTA16/1/202620B17/1/202730C18/1/202840null()D19/1/2029null()E19/1/2030null()F19/1/2031

jrlindem · Accepted Answer

I would probably just split the data using a filter tool on the Date Field where the value is not a date (or the Name field where Null), then just use a Select tool to rename and then Union back together.  Annoying, but at least it's a way forward for you.

-Jay

Pilsner · Accepted Answer

Hello @gitcode,

Based on your current input method, I too would recommend using a filter followed by some further logic to rename the columns, as @jrlindem suggested. However, I would like to suggest a different input method if possible.

I have created two Excel files which when stacked on top of each other, would produce the same results as your screenshot.

File 1:

File 2:

The way I was able to read them in so that the columns are rearranged correctly, was actually using a batch macro.

1) The first step is to simply list all your input files (they don't all have to eb excel, I have simply used excel for my example). A directory tool should help with this.

2)  Once you have read in the full paths of all your files, you feed them into a batch macro. Inside the macro itself you should have a data input, with a control parameter attached, which should be set to overwrite the full file path.

3) The final trick, is actually in the interface designer. Inside of here, you can go to properties, then select the output mode to "Auto configure by name (wait until all iteration run)". This will then read all the files in separately, and align the columns based on the header instead of just its original "position".

The output from the macro will then look like the following:

If you still have to stick with the python script, you can indeed place that inside the batch macro instead to achieve the same results.

I've attached the workflow and macro below, please let me know how you get on.

Regards - Pilsner