I have an Excel sheet where a few of my columns contain mixed data-types (i.e. For an Expiration Date column, I can input a date in m/dd/yyyy format or have strings like "Waiver", "N/A", or "TBD". Is there a way to import an Excel sheet with this type of column into an Access table while keeping mixed data types? Alternatively, on Excel, is there a way to format these columns so the date format isn't converted to a bunch of numbers if I do a "text" format?
Sami Shamma
@Reply 16 months ago
One of the main purposes of moving from the chaos of Excel to the structure of Access is to gain data consistency. My advice is to clean the data in Excel first, then import consistent data types into Access.
To answer your question, the only way to import all of those things from one column is to treat it as a short text. having said that, you will not be able to manipulate dates or numbers without some significant VBA coding.
Kevin Robertson
@Reply 16 months ago
Here are some options for arranging your data (see screenshot below).
Kevin Robertson
@Reply 16 months ago
Matt Hall
@Reply 16 months ago
Another way to look at is that you don't have mixed data types as much as two sets of data, condensed into one column. You really have "expiration note" data and "expiration date" data.
Your records with an expiration date you assume to have a note, maybe like, "Has expiration date". Your records with an expiration note you assume to not have an expiration date. You have mixed the dates for some records in with the no-date-reasons for the other records.
Does that make sense?
You might create a new column in the excel file and separate the notes from the dates.
Sorry, only students may add comments.
Click here for more
information on how you can set up an account.
If you are a Visitor, go ahead and post your reply as a
new comment, and we'll move it here for you
once it's approved. Be sure to use the same name and email address.
This thread is now CLOSED. If you wish to comment, start a NEW discussion in
Visitor Forum.
