Mixing
Top row blank in xlsx import into MS-Access
I have a collection of MS-Excel datasets in both .xlsx and .csv that get updated regularly.During an update, I save them to my C drive and convert the .csv files as .xlsx. Following this, I import them into MS-Access for processing.
However, I've a tedious issue, which I think is related to the previous file format before the conversion, but not entirely sure:
Every time I import the collection to MS-Access, some of the tables have an extra blank row added to the top of the table, which can interfere with some of the processes I run.
Now, it would appear that the files that were originally .xlsx are the ones that have this extra row, while the files that were originally .csv and then converted do not have this problem.
I can't give a reproducible example due to data sensitivity, but I'd like to ask if there is any general solution to this issue?
Help is greatly appreciated.
Thanks
ms-access xlsx
asked Jan 2 at 8:51
PryorePryore
199111
add a comment |
I have a collection of MS-Excel datasets in both .xlsx and .csv that get updated regularly.During an update, I save them to my C drive and convert the .csv files as .xlsx. Following this, I import them into MS-Access for processing.
However, I've a tedious issue, which I think is related to the previous file format before the conversion, but not entirely sure:
Every time I import the collection to MS-Access, some of the tables have an extra blank row added to the top of the table, which can interfere with some of the processes I run.
Now, it would appear that the files that were originally .xlsx are the ones that have this extra row, while the files that were originally .csv and then converted do not have this problem.
I can't give a reproducible example due to data sensitivity, but I'd like to ask if there is any general solution to this issue?
Help is greatly appreciated.
Thanks
ms-access xlsx
asked Jan 2 at 8:51
PryorePryore
199111
Quick and dirty solution would be to check for blank rows and delete after importing.
– Santosh
Jan 2 at 8:55
@Santosh That's what I currently do, but I'm looking to make incremental improvements by eliminating that step. I have several files to apply this change to.
– Pryore
Jan 2 at 8:58
2
Check if the excel file to be imported doesn't have blank rows at bottom and correctly pointing to last cell by pressing (Ctrl + End)
– Santosh
Jan 2 at 9:02
I usually import into an empty table and then use a series of queries to normalise the data. Does the same as @Santosh really except the data is pushed out to multiple tables.
– Darren Bartrup-Cook
Jan 2 at 9:03
Yeah I'm finding that one of the files is pointing to a blank cell at the bottom of the table. Thanks @Santosh
– Pryore
Jan 2 at 9:05
add a comment |
I have a collection of MS-Excel datasets in both .xlsx and .csv that get updated regularly.During an update, I save them to my C drive and convert the .csv files as .xlsx. Following this, I import them into MS-Access for processing.
However, I've a tedious issue, which I think is related to the previous file format before the conversion, but not entirely sure:
Every time I import the collection to MS-Access, some of the tables have an extra blank row added to the top of the table, which can interfere with some of the processes I run.
Now, it would appear that the files that were originally .xlsx are the ones that have this extra row, while the files that were originally .csv and then converted do not have this problem.
I can't give a reproducible example due to data sensitivity, but I'd like to ask if there is any general solution to this issue?
Help is greatly appreciated.
Thanks
ms-access xlsx
asked Jan 2 at 8:51
PryorePryore
199111
I have a collection of MS-Excel datasets in both .xlsx and .csv that get updated regularly.During an update, I save them to my C drive and convert the .csv files as .xlsx. Following this, I import them into MS-Access for processing.
However, I've a tedious issue, which I think is related to the previous file format before the conversion, but not entirely sure:
Every time I import the collection to MS-Access, some of the tables have an extra blank row added to the top of the table, which can interfere with some of the processes I run.
Now, it would appear that the files that were originally .xlsx are the ones that have this extra row, while the files that were originally .csv and then converted do not have this problem.
I can't give a reproducible example due to data sensitivity, but I'd like to ask if there is any general solution to this issue?
Help is greatly appreciated.
Thanks
ms-access xlsx
ms-access xlsx
asked Jan 2 at 8:51
PryorePryore
199111
asked Jan 2 at 8:51
PryorePryore
199111
asked Jan 2 at 8:51
PryorePryore
199111
asked Jan 2 at 8:51
PryorePryore
199111
asked Jan 2 at 8:51
PryorePryore
199111
199111
Quick and dirty solution would be to check for blank rows and delete after importing.
– Santosh
Jan 2 at 8:55
@Santosh That's what I currently do, but I'm looking to make incremental improvements by eliminating that step. I have several files to apply this change to.
– Pryore
Jan 2 at 8:58
2
Check if the excel file to be imported doesn't have blank rows at bottom and correctly pointing to last cell by pressing (Ctrl + End)
– Santosh
Jan 2 at 9:02
I usually import into an empty table and then use a series of queries to normalise the data. Does the same as @Santosh really except the data is pushed out to multiple tables.
– Darren Bartrup-Cook
Jan 2 at 9:03
Yeah I'm finding that one of the files is pointing to a blank cell at the bottom of the table. Thanks @Santosh
– Pryore
Jan 2 at 9:05
add a comment |
Quick and dirty solution would be to check for blank rows and delete after importing.
– Santosh
Jan 2 at 8:55
@Santosh That's what I currently do, but I'm looking to make incremental improvements by eliminating that step. I have several files to apply this change to.
– Pryore
Jan 2 at 8:58
2
Check if the excel file to be imported doesn't have blank rows at bottom and correctly pointing to last cell by pressing (Ctrl + End)
– Santosh
Jan 2 at 9:02
I usually import into an empty table and then use a series of queries to normalise the data. Does the same as @Santosh really except the data is pushed out to multiple tables.
– Darren Bartrup-Cook
Jan 2 at 9:03
Yeah I'm finding that one of the files is pointing to a blank cell at the bottom of the table. Thanks @Santosh
– Pryore
Jan 2 at 9:05
Quick and dirty solution would be to check for blank rows and delete after importing.
– Santosh
Jan 2 at 8:55
Quick and dirty solution would be to check for blank rows and delete after importing.
– Santosh
Jan 2 at 8:55
@Santosh That's what I currently do, but I'm looking to make incremental improvements by eliminating that step. I have several files to apply this change to.
– Pryore
Jan 2 at 8:58
@Santosh That's what I currently do, but I'm looking to make incremental improvements by eliminating that step. I have several files to apply this change to.
– Pryore
Jan 2 at 8:58
2
2
Check if the excel file to be imported doesn't have blank rows at bottom and correctly pointing to last cell by pressing (Ctrl + End)
– Santosh
Jan 2 at 9:02
Check if the excel file to be imported doesn't have blank rows at bottom and correctly pointing to last cell by pressing (Ctrl + End)
– Santosh
Jan 2 at 9:02
I usually import into an empty table and then use a series of queries to normalise the data. Does the same as @Santosh really except the data is pushed out to multiple tables.
– Darren Bartrup-Cook
Jan 2 at 9:03
I usually import into an empty table and then use a series of queries to normalise the data. Does the same as @Santosh really except the data is pushed out to multiple tables.
– Darren Bartrup-Cook
Jan 2 at 9:03
Yeah I'm finding that one of the files is pointing to a blank cell at the bottom of the table. Thanks @Santosh
– Pryore
Jan 2 at 9:05
Yeah I'm finding that one of the files is pointing to a blank cell at the bottom of the table. Thanks @Santosh
– Pryore
Jan 2 at 9:05
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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%2fstackoverflow.com%2fquestions%2f54003480%2ftop-row-blank-in-xlsx-import-into-ms-access%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f54003480%2ftop-row-blank-in-xlsx-import-into-ms-access%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
Quick and dirty solution would be to check for blank rows and delete after importing.
– Santosh
Jan 2 at 8:55
@Santosh That's what I currently do, but I'm looking to make incremental improvements by eliminating that step. I have several files to apply this change to.
– Pryore
Jan 2 at 8:58
2
Check if the excel file to be imported doesn't have blank rows at bottom and correctly pointing to last cell by pressing (Ctrl + End)
– Santosh
Jan 2 at 9:02
I usually import into an empty table and then use a series of queries to normalise the data. Does the same as @Santosh really except the data is pushed out to multiple tables.
– Darren Bartrup-Cook
Jan 2 at 9:03
Yeah I'm finding that one of the files is pointing to a blank cell at the bottom of the table. Thanks @Santosh
– Pryore
Jan 2 at 9:05