CODEWITHSUNDEEP
sqlexcelms-access
nfplee
nfplee

Reputation: 7977

Import Excel Spread Sheet into Access - Format Issue with [h]:mm:ss

I need to import an Excel spread sheet into Microsoft Access. I have a column which is in the format [h]:mm:ss. When I import it with Access I have specified the column to be in Date/Time format but it displays incorrectly.

For example in Excel it would show 452:32:00 but in Access it would show 18/01/1900 20:32:00. I need to write a query in Access to display this in the same format it is shown in Excel.

I'd appreciate if someone could show me the SQL to do this. Thanks

Upvotes: 2

Views: 981

Answers (1)

Yawar
Yawar

Reputation: 11627

Update 1: a new approach to storing durations--store everything in seconds, instead of three separate columns for hours, minutes and seconds. Access' double type allows us to store durations in the scale of geological ages.

Steps:

  1. Import spreadsheet into an Access table of durations--say, tblDurations.
  2. Perform arithmetic operations on the durations, e.g. add (the corresponding columns in) two rows together, multiple a row by 2, sum all the rows, etc.
  3. Format and display the output.

Below, I'll be using mostly Access's ANSI-92 SQL syntax which works through the DAO CurrentProject.Connection.Execute method. You'll have to use DAO for best results here instead of the Access GUI query editor(s). Let me know if you need any clarification on this.

Import Spreadsheet

Create Table

create table tblDurations (
  ID autoincrement primary key
, durSeconds double not null
)

Import Durations

I like to import data from a simple CSV file. I'll show you that approach here. Say you have a CSV file durations.csv that looks like this in Excel:

Duration
01:59:59
02:01:57
00:00:04

You can run the following to import it:

insert into tblDurations (durSeconds)
select
  hour(Duration) * 60 * 60
+ minute(Duration) * 60
+ second(Duration)
from [Text;FMT=CSVDelimited;HDR=Yes;DATABASE=C:\Users\YourName\Documents;].[durations.csv]

Note that in the last line above you substitute your own file path and name.

Arithmetic on Durations

Now you can switch to Access' GUI SQL editor. Let's say we want to multiply a certain duration by 2.5:

select
  2.5 as multiplier
, multiplier * durSeconds as s
from tblDurations
where ID = 1

Format and Display Output

The trick here is to convert a duration expressed in seconds into hours, minutes and seconds:

select
  int(sq.s / (60 * 60)) as Hours
, int((sq.s - Hours * 60 * 60) / 60) as Minutes
, sq.s - Hours * 60 * 60 - Minutes * 60 as Seconds
from (
  select
    2.5 as multiplier
  , multiplier * durSeconds as s
  from tblDurations
  where ID = 1
) as sq

Finally, you format the output hours, minutes and seconds as hh:mm:ss, which I leave as an exercise for the reader.

Upvotes: 1

Related Questions