Excel: how to import identically formatted CSV files
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
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.
microsoft-excel csv import power-query
asked Feb 6 at 21:34
Karl PerryKarl Perry
1
add a comment |
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.
microsoft-excel csv import power-query
asked Feb 6 at 21:34
Karl PerryKarl Perry
1
add a comment |
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.
microsoft-excel csv import power-query
asked Feb 6 at 21:34
Karl PerryKarl Perry
1
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.
microsoft-excel csv import power-query
microsoft-excel csv import power-query
asked Feb 6 at 21:34
Karl PerryKarl Perry
1
asked Feb 6 at 21:34
Karl PerryKarl Perry
1
asked Feb 6 at 21:34
Karl PerryKarl Perry
1
asked Feb 6 at 21:34
Karl PerryKarl Perry
1
asked Feb 6 at 21:34
Karl PerryKarl Perry
1
1
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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
answered Feb 6 at 21:45
YHoffmanYHoffman
1314
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
Feb 6 at 22:45
Perhaps read the folder in power query, filter it to the newest date, and read that csv.
– YHoffman
Feb 6 at 22:50
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1402873%2fexcel-how-to-import-identically-formatted-csv-files%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Feb 6 at 21:45
YHoffmanYHoffman
1314
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
Feb 6 at 22:45
Perhaps read the folder in power query, filter it to the newest date, and read that csv.
– YHoffman
Feb 6 at 22:50
add a comment |
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
answered Feb 6 at 21:45
YHoffmanYHoffman
1314
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
Feb 6 at 22:45
Perhaps read the folder in power query, filter it to the newest date, and read that csv.
– YHoffman
Feb 6 at 22:50
add a comment |
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
answered Feb 6 at 21:45
YHoffmanYHoffman
1314
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
answered Feb 6 at 21:45
YHoffmanYHoffman
1314
edited Feb 6 at 22:56
answered Feb 6 at 21:45
YHoffmanYHoffman
1314
answered Feb 6 at 21:45
YHoffmanYHoffman
1314
answered Feb 6 at 21:45
YHoffmanYHoffman
1314
1314
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
Feb 6 at 22:45
Perhaps read the folder in power query, filter it to the newest date, and read that csv.
– YHoffman
Feb 6 at 22:50
add a comment |
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
Feb 6 at 22:45
Perhaps read the folder in power query, filter it to the newest date, and read that csv.
– YHoffman
Feb 6 at 22:50
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
Feb 6 at 22:45
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
Feb 6 at 22:45
Perhaps read the folder in power query, filter it to the newest date, and read that csv.
– YHoffman
Feb 6 at 22:50
Perhaps read the folder in power query, filter it to the newest date, and read that csv.
– YHoffman
Feb 6 at 22:50
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1402873%2fexcel-how-to-import-identically-formatted-csv-files%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
