2

In Excel 2013, I used to be able to specify a sheet to Data Refresh and at the time of creating a linked csv file, I could prompt for the location or file with every Data Refresh. In Excel 2016, I am unsure how to mimic this behavior. I've been able to load my CSV into a new sheet, but not into the existing sheet and where I can delete some rows if I need.

Improve this question
4
  • I think you are going to have to explain in more detail. You might also have better luck in stackoverflow. I am unfamiliar with the prompt for CSV location on Data Refresh myself. Excel in Office365 here, I go to Data tab, From Tax/CSV, specify file, load. To tell it to use another CSV: click table/data, Query tab, Edit (open Power Query Editor window), Data source settings, Change Source
    – gregg
    Commented Mar 14, 2023 at 21:38
  • it was a workflow in excel 2013. cant figure out same workflow in excel 2016
    – Sun
    Commented Mar 15, 2023 at 4:32
  • 1
    Could you define your workflow in more detail?
    – harrymc
    Commented Mar 15, 2023 at 9:11
  • @Sun: Without more information, you will only get very general answers that might help less.
    – harrymc
    Commented Mar 18, 2023 at 9:56

1 Answer 1

Reset to default
1

I think you are going to have to explain in more detail from memory or by replicating it in Excel 2013 if you still have it. I am unfamiliar with the prompt for CSV location on Data Refresh myself. Newest Excel here (Office365 desktop apps) so my options might be different than Excel 2016. Here is some info that MAY answer your question.

Likely start by going to Data tab, From Tax/CSV, select CSV file, after specify CSV file you get this prompt: ExcelImportCSVPrompt

Change to another CSV file/source

  1. After importing/loading CSV, click table/data

  2. Query tab

    2a. Side note: query tab, Load To tells allows you to change where CSV data will be placed (ex: new sheet, PivotChart, etc.)

  3. Edit (opens Power Query Editor window)

  4. Data source settings

  5. Change Source

Choose Excel sheet CSV imported into & Data Model concepts

  1. At CSV import prompt clicking arrow next to Load & selecting 'Load To...'

  2. You can then tell it which Excel sheet to put it in. There is a checkbox for 'Data Model', if you check it, then go to Data tab, Manage Data Model its some tool called 'Power Pivot' I don't even know, but may be what you are looking for.

    ExcelImportCsvLoadTo

Power Query Editor: where lots of magic happens

  • At CSV import prompt click Transform Data
  • Change to another CSV file by clicking 'Data source settings', Change source
  • Marvel at all of the options, too many for me to explain. In my eyes this can quickly automate things you may normally do (delete rows, change date/number formats, etc.). Basically can take a dirty data source & can make it pretty in set programmed ways.

https://support.microsoft.com/en-us/office/about-power-query-in-excel-7104fbee-9e62-4cb9-a02e-5bfb1a6c536a

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .