[AccessD] Date Importing from Excel

[Stuart McLachlan]

Yep, That's my approach too. I've done in many times.

On 13 Aug 2020 at 15:04, Gary Kjos wrote:

> 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
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>