How to import Dates from Excel into Access with text in some cells?
I am attempting to import a date column from Excel into Access. The problem is that over the years, users of the Excel spreadsheet sporadically inserted text notes into this date column a few thousand times. It would be impossible to delete all of the text manually. I was able to import this date column into Access by converting the format to Text at the time of import. The problem is that I still need to find a way to convert the new Access Text field containing my dates back into a Date field, so that I can perform basic arithmetic functions on it (adding days for various different projected project lead times). Unfortunately, Access won't allow me to convert the entries with text in them back to Dates, any more than it let me import them as Dates.
My first question would be, is there a way to override this? If not, then I was thinking about some kind of conditional UPDATE statement that would let me eliminate only the text in these cells, while leaving the numeric values untouched (i.e.- “IF any text is found, UPDATE this field to delete the text only”). I'm not sure if this would be possible though.
If neither of these are possible, then does anyone have any other ideas? Thanks in advance!
microsoft-excel microsoft-access sql import
asked Jun 13 '14 at 13:38
user333102user333102
1112
add a comment |
I am attempting to import a date column from Excel into Access. The problem is that over the years, users of the Excel spreadsheet sporadically inserted text notes into this date column a few thousand times. It would be impossible to delete all of the text manually. I was able to import this date column into Access by converting the format to Text at the time of import. The problem is that I still need to find a way to convert the new Access Text field containing my dates back into a Date field, so that I can perform basic arithmetic functions on it (adding days for various different projected project lead times). Unfortunately, Access won't allow me to convert the entries with text in them back to Dates, any more than it let me import them as Dates.
My first question would be, is there a way to override this? If not, then I was thinking about some kind of conditional UPDATE statement that would let me eliminate only the text in these cells, while leaving the numeric values untouched (i.e.- “IF any text is found, UPDATE this field to delete the text only”). I'm not sure if this would be possible though.
If neither of these are possible, then does anyone have any other ideas? Thanks in advance!
microsoft-excel microsoft-access sql import
asked Jun 13 '14 at 13:38
user333102user333102
1112
2
Does this have to be done in Access? Seems to me it would be a good idea to condition the data BEFORE it is imported into the database. Have you tried removing text from the column, format as date, then import into Access?
– CharlieRB
Jun 13 '14 at 13:45
If the notes are always following or preceding the date you can extract easily the date in excel with left(cell;x) or right(a,x). Where x is the number of characters you need to extract. After that you just have to use the convert command and manually correct he few excpetiosn left. It's a job of twenty minutes. Alternatively you can use ctrl+h and suppress all the characters you don't want going through the alphabet one by one. Or you can write a macro to to that at once .Either case it should take 30mn or os. Provide a sample if you want more precise help.
– P. O.
Jun 13 '14 at 14:00
Hi Charlie. While I would have no personal issue with removing the text, it would be near impossible to do manually. Users of this spreadsheet have entered textual notes in thousands of these "Date" cells. Further complicating the matter is the fact that they may actually need to keep these notes, meaning that I'd have to find a way to copy all of the text into the adjacent cells of another blank column before deleting the text in the Date column.
– user333102
Jun 13 '14 at 14:05
Make a column adjacent to the one in question and copy a formula like this throughout it:=IF(ISTEXT(A1),"",A1)(this assumes columnAis the one you want to import). The new column will contain only the dates and you can import from there without fear of including the text or spending hours removing it.
– techturtle
Jun 13 '14 at 14:14
add a comment |
I am attempting to import a date column from Excel into Access. The problem is that over the years, users of the Excel spreadsheet sporadically inserted text notes into this date column a few thousand times. It would be impossible to delete all of the text manually. I was able to import this date column into Access by converting the format to Text at the time of import. The problem is that I still need to find a way to convert the new Access Text field containing my dates back into a Date field, so that I can perform basic arithmetic functions on it (adding days for various different projected project lead times). Unfortunately, Access won't allow me to convert the entries with text in them back to Dates, any more than it let me import them as Dates.
My first question would be, is there a way to override this? If not, then I was thinking about some kind of conditional UPDATE statement that would let me eliminate only the text in these cells, while leaving the numeric values untouched (i.e.- “IF any text is found, UPDATE this field to delete the text only”). I'm not sure if this would be possible though.
If neither of these are possible, then does anyone have any other ideas? Thanks in advance!
microsoft-excel microsoft-access sql import
asked Jun 13 '14 at 13:38
user333102user333102
1112
I am attempting to import a date column from Excel into Access. The problem is that over the years, users of the Excel spreadsheet sporadically inserted text notes into this date column a few thousand times. It would be impossible to delete all of the text manually. I was able to import this date column into Access by converting the format to Text at the time of import. The problem is that I still need to find a way to convert the new Access Text field containing my dates back into a Date field, so that I can perform basic arithmetic functions on it (adding days for various different projected project lead times). Unfortunately, Access won't allow me to convert the entries with text in them back to Dates, any more than it let me import them as Dates.
My first question would be, is there a way to override this? If not, then I was thinking about some kind of conditional UPDATE statement that would let me eliminate only the text in these cells, while leaving the numeric values untouched (i.e.- “IF any text is found, UPDATE this field to delete the text only”). I'm not sure if this would be possible though.
If neither of these are possible, then does anyone have any other ideas? Thanks in advance!
microsoft-excel microsoft-access sql import
microsoft-excel microsoft-access sql import
asked Jun 13 '14 at 13:38
user333102user333102
1112
asked Jun 13 '14 at 13:38
user333102user333102
1112
asked Jun 13 '14 at 13:38
user333102user333102
1112
asked Jun 13 '14 at 13:38
user333102user333102
1112
asked Jun 13 '14 at 13:38
user333102user333102
1112
1112
2
Does this have to be done in Access? Seems to me it would be a good idea to condition the data BEFORE it is imported into the database. Have you tried removing text from the column, format as date, then import into Access?
– CharlieRB
Jun 13 '14 at 13:45
If the notes are always following or preceding the date you can extract easily the date in excel with left(cell;x) or right(a,x). Where x is the number of characters you need to extract. After that you just have to use the convert command and manually correct he few excpetiosn left. It's a job of twenty minutes. Alternatively you can use ctrl+h and suppress all the characters you don't want going through the alphabet one by one. Or you can write a macro to to that at once .Either case it should take 30mn or os. Provide a sample if you want more precise help.
– P. O.
Jun 13 '14 at 14:00
Hi Charlie. While I would have no personal issue with removing the text, it would be near impossible to do manually. Users of this spreadsheet have entered textual notes in thousands of these "Date" cells. Further complicating the matter is the fact that they may actually need to keep these notes, meaning that I'd have to find a way to copy all of the text into the adjacent cells of another blank column before deleting the text in the Date column.
– user333102
Jun 13 '14 at 14:05
Make a column adjacent to the one in question and copy a formula like this throughout it:=IF(ISTEXT(A1),"",A1)(this assumes columnAis the one you want to import). The new column will contain only the dates and you can import from there without fear of including the text or spending hours removing it.
– techturtle
Jun 13 '14 at 14:14
add a comment |
2
Does this have to be done in Access? Seems to me it would be a good idea to condition the data BEFORE it is imported into the database. Have you tried removing text from the column, format as date, then import into Access?
– CharlieRB
Jun 13 '14 at 13:45
If the notes are always following or preceding the date you can extract easily the date in excel with left(cell;x) or right(a,x). Where x is the number of characters you need to extract. After that you just have to use the convert command and manually correct he few excpetiosn left. It's a job of twenty minutes. Alternatively you can use ctrl+h and suppress all the characters you don't want going through the alphabet one by one. Or you can write a macro to to that at once .Either case it should take 30mn or os. Provide a sample if you want more precise help.
– P. O.
Jun 13 '14 at 14:00
Hi Charlie. While I would have no personal issue with removing the text, it would be near impossible to do manually. Users of this spreadsheet have entered textual notes in thousands of these "Date" cells. Further complicating the matter is the fact that they may actually need to keep these notes, meaning that I'd have to find a way to copy all of the text into the adjacent cells of another blank column before deleting the text in the Date column.
– user333102
Jun 13 '14 at 14:05
Make a column adjacent to the one in question and copy a formula like this throughout it:=IF(ISTEXT(A1),"",A1)(this assumes columnAis the one you want to import). The new column will contain only the dates and you can import from there without fear of including the text or spending hours removing it.
– techturtle
Jun 13 '14 at 14:14
2
2
Does this have to be done in Access? Seems to me it would be a good idea to condition the data BEFORE it is imported into the database. Have you tried removing text from the column, format as date, then import into Access?
– CharlieRB
Jun 13 '14 at 13:45
Does this have to be done in Access? Seems to me it would be a good idea to condition the data BEFORE it is imported into the database. Have you tried removing text from the column, format as date, then import into Access?
– CharlieRB
Jun 13 '14 at 13:45
If the notes are always following or preceding the date you can extract easily the date in excel with left(cell;x) or right(a,x). Where x is the number of characters you need to extract. After that you just have to use the convert command and manually correct he few excpetiosn left. It's a job of twenty minutes. Alternatively you can use ctrl+h and suppress all the characters you don't want going through the alphabet one by one. Or you can write a macro to to that at once .Either case it should take 30mn or os. Provide a sample if you want more precise help.
– P. O.
Jun 13 '14 at 14:00
If the notes are always following or preceding the date you can extract easily the date in excel with left(cell;x) or right(a,x). Where x is the number of characters you need to extract. After that you just have to use the convert command and manually correct he few excpetiosn left. It's a job of twenty minutes. Alternatively you can use ctrl+h and suppress all the characters you don't want going through the alphabet one by one. Or you can write a macro to to that at once .Either case it should take 30mn or os. Provide a sample if you want more precise help.
– P. O.
Jun 13 '14 at 14:00
Hi Charlie. While I would have no personal issue with removing the text, it would be near impossible to do manually. Users of this spreadsheet have entered textual notes in thousands of these "Date" cells. Further complicating the matter is the fact that they may actually need to keep these notes, meaning that I'd have to find a way to copy all of the text into the adjacent cells of another blank column before deleting the text in the Date column.
– user333102
Jun 13 '14 at 14:05
Hi Charlie. While I would have no personal issue with removing the text, it would be near impossible to do manually. Users of this spreadsheet have entered textual notes in thousands of these "Date" cells. Further complicating the matter is the fact that they may actually need to keep these notes, meaning that I'd have to find a way to copy all of the text into the adjacent cells of another blank column before deleting the text in the Date column.
– user333102
Jun 13 '14 at 14:05
Make a column adjacent to the one in question and copy a formula like this throughout it:
=IF(ISTEXT(A1),"",A1) (this assumes column A is the one you want to import). The new column will contain only the dates and you can import from there without fear of including the text or spending hours removing it.– techturtle
Jun 13 '14 at 14:14
Make a column adjacent to the one in question and copy a formula like this throughout it:
=IF(ISTEXT(A1),"",A1) (this assumes column A is the one you want to import). The new column will contain only the dates and you can import from there without fear of including the text or spending hours removing it.– techturtle
Jun 13 '14 at 14:14
add a comment |
1 Answer
1
active
oldest
votes
This is quite easy. Add a date column to your table in Access. Open up the query design view and write an update query to transfer the good dates to the new field, deleting them from the old field.
UPDATE Table1 SET [Table1].[NewDateField] = [Table1].[OldDateField], [Table1].[OldDateField] = "" WHERE IsDate([Table1].[OldDateField])=True;
This SQL query works in Access.
answered Jun 28 '14 at 16:16
wbeard52wbeard52
2,71622238
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%2f768400%2fhow-to-import-dates-from-excel-into-access-with-text-in-some-cells%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
This is quite easy. Add a date column to your table in Access. Open up the query design view and write an update query to transfer the good dates to the new field, deleting them from the old field.
UPDATE Table1 SET [Table1].[NewDateField] = [Table1].[OldDateField], [Table1].[OldDateField] = "" WHERE IsDate([Table1].[OldDateField])=True;
This SQL query works in Access.
answered Jun 28 '14 at 16:16
wbeard52wbeard52
2,71622238
add a comment |
This is quite easy. Add a date column to your table in Access. Open up the query design view and write an update query to transfer the good dates to the new field, deleting them from the old field.
UPDATE Table1 SET [Table1].[NewDateField] = [Table1].[OldDateField], [Table1].[OldDateField] = "" WHERE IsDate([Table1].[OldDateField])=True;
This SQL query works in Access.
answered Jun 28 '14 at 16:16
wbeard52wbeard52
2,71622238
add a comment |
This is quite easy. Add a date column to your table in Access. Open up the query design view and write an update query to transfer the good dates to the new field, deleting them from the old field.
UPDATE Table1 SET [Table1].[NewDateField] = [Table1].[OldDateField], [Table1].[OldDateField] = "" WHERE IsDate([Table1].[OldDateField])=True;
This SQL query works in Access.
answered Jun 28 '14 at 16:16
wbeard52wbeard52
2,71622238
This is quite easy. Add a date column to your table in Access. Open up the query design view and write an update query to transfer the good dates to the new field, deleting them from the old field.
UPDATE Table1 SET [Table1].[NewDateField] = [Table1].[OldDateField], [Table1].[OldDateField] = "" WHERE IsDate([Table1].[OldDateField])=True;
This SQL query works in Access.
answered Jun 28 '14 at 16:16
wbeard52wbeard52
2,71622238
answered Jun 28 '14 at 16:16
wbeard52wbeard52
2,71622238
answered Jun 28 '14 at 16:16
wbeard52wbeard52
2,71622238
answered Jun 28 '14 at 16:16
wbeard52wbeard52
2,71622238
2,71622238
add a comment |
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%2f768400%2fhow-to-import-dates-from-excel-into-access-with-text-in-some-cells%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
2
Does this have to be done in Access? Seems to me it would be a good idea to condition the data BEFORE it is imported into the database. Have you tried removing text from the column, format as date, then import into Access?
– CharlieRB
Jun 13 '14 at 13:45
If the notes are always following or preceding the date you can extract easily the date in excel with left(cell;x) or right(a,x). Where x is the number of characters you need to extract. After that you just have to use the convert command and manually correct he few excpetiosn left. It's a job of twenty minutes. Alternatively you can use ctrl+h and suppress all the characters you don't want going through the alphabet one by one. Or you can write a macro to to that at once .Either case it should take 30mn or os. Provide a sample if you want more precise help.
– P. O.
Jun 13 '14 at 14:00
Hi Charlie. While I would have no personal issue with removing the text, it would be near impossible to do manually. Users of this spreadsheet have entered textual notes in thousands of these "Date" cells. Further complicating the matter is the fact that they may actually need to keep these notes, meaning that I'd have to find a way to copy all of the text into the adjacent cells of another blank column before deleting the text in the Date column.
– user333102
Jun 13 '14 at 14:05
Make a column adjacent to the one in question and copy a formula like this throughout it:
=IF(ISTEXT(A1),"",A1)(this assumes columnAis the one you want to import). The new column will contain only the dates and you can import from there without fear of including the text or spending hours removing it.– techturtle
Jun 13 '14 at 14:14