0

Apologies if this has been asked and answered before - I have looked but not found.

I get CSV files every two weeks that are identically structured, and I need to import them to the same Excel 2016 worksheet.

I have seen articles about importing every CSV file in a folder to the same worksheet but that is not what I need to do. I need to import today's file; in two weeks I'll need to import that day's file; in two weeks do it again; etc.

The file does need to be lightly scrubbed before import: delete top three rows and delete several columns. Then the entire file can be appended to the end of the current worksheet.

I can't seem to figure out how to make a Power Query generic enough to do this work - maybe it can't.

Is VBA the only way to automate this? Suggestions?

Thanks.

Improve this question

1 Answer 1

Reset to default
0

I can think of 2 ways for you to do it.
edit After hearing in the comment that the first 2 way don't work I had a new idea
1. if you can rename the new csv to the name of the old one, then that will be the easiest. (The idea is that you will have one file name to import and you will need to ensure that the file nme stays the same, then PowerQuery can just import the file with that name again).
2. If you get the file with a date appended to the filename, then you can work a way for power query for example to use todays date and format it to match the file name with the date format and then pass the self constructed filename to PowerQuery to find that file.
new edit 3. Read the folder with Power Query and filter it to the latest created file, (there is an option in the filter gui to autocreate the filter, just filter by the Date Created column and select Is Latest) and then extract the data from that file using csv.contents

Improve this answer
2
  • Thanks. 1. While that is one way, I'd prefer not to have to rename the file. Computers are supposed to make life easier, and having to rename a file makes life harder. 2. Unfortunately the files don't have dates. For now I brute-forced it. I may try the idea to rename the file to something simple with the next file in a couple of weeks.
    – Karl Perry
    Commented Feb 6, 2019 at 22:45
  • Perhaps read the folder in power query, filter it to the newest date, and read that csv.
    – Zubda
    Commented Feb 6, 2019 at 22:50

You must log in to answer this question.

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