[AccessD] Date Importing from Excel

[Gary Kjos]

Hi John,

I used to do these kind of imports often.  My method was to import the
data into text fields into a tblImportedData table.  Then I would use
that table to import into my real table.  Alternatively it worked well
for me to import as text.  Then add another date type column and run
UPDATE queries using the text version of the date info and parsing it
into the correct format and use that re-formatted data to update the
date type column.  That would often take multiple passes, one for each
version of the date.

Depending on the number of records involved and how creative the
various date formats are in the text column I sometimes would take the
easiest way out and simply KEY IN the corrected data if there were
only a small number of outliers.  That is sometimes easier than
working out the parsing etc.

Good luck.

GK

On Thu, Aug 13, 2020 at 2:06 PM John Bartow via AccessD
<accessd at databaseadvisors.com> wrote:
>
> Hi All,
> I'm doing some Access work for the first time in years). It's a pretty simple little db and app. The exception being the importing of the old data from a spreadsheet. The spreadsheet is a mess and that's why they finally asked me for a better solution. This is so basic that I'm just doing an Access app so they can see there is life beyond spreadsheets.
>
> The biggest issue is the old data - specifically all of the date fields. Each date field has dozens of records where the data won't import unless I set them to short text.
>
> I have used queries in the past to add a date field and have the query convert the short text to dates where possible. And then I would use the new date field (where null) to find the old date text fields wouldn't convert so that I don't lose the badly formatted old date field data and can manually investigate it. Problem is none of my old DBs open in the latest access.
>
> How would you suggest I handle the old data?
>
> TIA,
> John B
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com



-- 
Gary Kjos
garykjos at gmail.com