Share via

How to handle CSV input which constantly changing column order

Anonymous
2021-04-28T20:03:39+00:00

Hi,

I'm taking an CSV export from a web portal and then use that as input into my sheet.  The sheet has various logic to derive values out of the input CSV.  So the approach I took is copy & paste the input into an input tab, leave it alone and then do all the work on other tabs, using data from the input tab.

The problem is that export from portal does not maintain column ordering.  So each time I have to adjust the input sheet columns to match the new export and then copy the data.

I looked up Structured Reference Table but each time you paste new data the column will be renamed and excel will update the formula to assume the new value in the header row as the column name.

Is there any way to manage this situation?

Thanks

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-05-06T07:48:11+00:00

    do a power query against your csv file, in the power query dialog click transform, then you can clean up the columns you don't need, then you can save it back as a csv file or work directly on it. but if you clean your csv file to what you desire you can drop all the cleaned up csv file and to a power query by folder (where you drop all your cleaned csv file, and the power query will consolidate all the csv file automatically combining all of them.  When a new cleaned csv file gets dropped into the cleaned csv file, all you have to do is press the refresh button to update your worksheet with the new data that you dropped into that folder.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2021-04-28T21:46:25+00:00

    re: column arangement

    Another possible way is to create a "List" of the column header names in the desired order and add it to Excel's Custom Lists.
    Then use your new custom list as the sort order and sort the columns.

    Also, i observed that you asked 15 questions since November 2020 and none of them are marked as answers.  Were the responses that bad?

    NLtL

    https://1drv.ms/u/s!Au8Lyt79SOuhiXoNjAh-\_-zLi49O

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2021-04-28T22:50:33+00:00

    I have never used sorting left to right.  This will solve this particular problem.

    I'd add one more challenge.  The export CSV also has repetition.  One column can appear multiple times.  Is there a way to say only take the left or right most one?

    Thanks

    Was this answer helpful?

    0 comments
  4. Anonymous
    2021-04-28T20:40:29+00:00

    Hi redhonker

    My name is Eduardo. I am an independent consultant, first of all thanks for being part of the Microsoft Community, I will try to help you.

    I have 2 ideas for your case, first try to push the data from the web inside the excel through the data import

    Or, your csv file it´s separted by "," or ";"? Because you can create a macro to organize your data, if the file keeps the same aspect every import. In this way you can create a macro and replay in every import you do

    If you do the data import from the web you also create a macro to organize your files

    I hope this information is useful for you. If you still have questions, answer here so I can continue helping you.

    Sincerely,

    Eduardo

    Was this answer helpful?

    0 comments
  5. Anonymous
    2021-04-28T20:13:50+00:00

    If the names are consistent and only the order changes, you might be able to use Power Query to obtain the CSV file and put the columns into the desired order.

    Was this answer helpful?

    0 comments