CODEWITHSUNDEEP
excelms-access
Skyblue
Skyblue

Reputation: 35

Importing Excel to Access: Date be field name for Access table

I was trying to import an Excel worksheet into Access table and the worksheet had specific dates(E.g. 12/4/2017) as headers for columns.

And when i tried to import to Access, Access did not allow me to import that worksheet into table as "12/4/2017 isnt a valid field name"

Is there other ways to import the worksheet or work about this?

Thanks

Upvotes: 1

Views: 2108

Answers (1)

ashleedawg
ashleedawg

Reputation: 21639

Names of fields, controls & objects in Access:

  • Can be up to 64 characters long.

  • Can include any combination of letters, numbers, spaces, and special characters except a period (.), an exclamation point (!), accent grave (``) or brackets ([ ]).

  • Can't begin with leading spaces.

  • Can't include control characters (ASCII values 0 through 31).

  • Can't include a double quotation mark (") in table, view, or stored procedure names in a Microsoft Access project. (Source)

Date and time values in Excel are stored internally as a 64-bit floating point number. The value to the left of the decimal represents the number of days since December 30, 1899. The value to the right of the decimal represents the fraction of a day since midnight.

For example:

  • 12:00 Noon is stored as 0.5.

  • 1.0 represents midnight on January 1, 1900.

  • 2.25 represents 6:00 AM on January 2, 1900.

  • Your example date 12/4/2017 would be stored as 43073.

Interpretation of datetime's depend on customization of regional settings according to Microsoft (not necessarily the country's government standard date format). For example, I live in North America, so by default, Excel would interpret 12/4/2017 as a date.

However, for various reasons, I prefer a date format of YYYY-MM-DD (technically named "ISO 8601"), so I changed the format in my Windows Settings. Therefore, when I enter 12/4/2017, Excel does not recognize it as a date, so it is stored as text, yet when I enter 2017-12-4, Excel knows to store it as a date.

Regional settings aside, I suspect that your field names may have times attached to them (even if they aren't formatted to display as such).

If the cell you'd like to use as a field name actually contains:

April 12, 2017 6:00 AM

which, if formatted as M/D/YYYY, "hides" the time, to display as:

12/4/2017

even though it is actually stored internally as:

43073.25

Given the Access field names can't contain a period (see above), Access becomes "confused" with the fraction of a day (.25).

Make sure your dates to be used as field names don't contain times.

You could:

  • Format the row that has the field names as text.

    1. Right-click the row number and choose Format Cells.
    2. Under the Number tab, choose Text

  • Use a function to remove the times:

    If B1 contains a datetime you want to use as a field name in A1, you could use the Int function in cell A1 (to round the value down to a whole number):

    =Int(B1)

    The fraction (time) is removed but the value is still stored as a number/date.

  • Use a function to convert the datetime to text:

    If B1 contains a date you want to use as a field name in A1, you could use the Text function in cell A1:

    =Text(B1, "M/D/YYYY HH:MM")

As you can see in the image, Access allows me to use the dates as field names if they are properly formatted:

Access import of Excel sheet with dates as field names

Related Further Reading:

A note about Database Normalization:

Just because you can use dates as field names, that doesn't mean that you should. It is generally considered poor database design to have a field name so specific.

Perhaps your intention is to import the poorly-structured data into Access to fix this issue, but if not, you should consider storing the data in a more organized way that is conducive to database expansion and normalization.

If your data has date-specific field names:

ex.1

...then the date should be added as part of the record, not as a field name:

ex.2

...although this is still not normalized. Normalization is about optimizing efficiency and allowing for expansion, so perhaps the database could be setup more like:

ex.3

With this method it would be database expansion and data analysis would be more logical (perhaps making it easier to find trends in Jane's troubling eating habits).

Alas, I digress. There is plenty of information available online about database normalization, to suit any experience level.

Further Reading about Normalization:

EDIT: (the result)

You didn't mention which method you're using to import the data from Excel to Access, which may be relevant (as there are several possible combinations). Access might handle the source data differently if your Excel data is saved in an XLSM vs XLS vs CSV, etc. Data could be imported using the New Source Data…from File interface, vs programmatically with VBA, or even other languages. Therefore, if you can't get one method working (with the dates formatted a specific way), try one of the other combinations.

For simplicity's sake, I used the built-in interface with an XLSM into an ACCDB. The result is demonstrated below:

example importing data

Note that it worked even though I included times in the headers (and would work without times), since they are properly formatted as text, and First Column Contains Column Headers is selected.

Upvotes: 2

Related Questions