?
Solved

Importing time from CSV to MS Access

Posted on 2014-10-22
10
Medium Priority
?
219 Views
Last Modified: 2014-10-22
Hi Experts,

I have a number of csv files from external systems. When I try to import this to MS Access, they are all converted to
30/12/1899 05:57 or 30/12/1899 10:36 etc.

How can I import data, so I will just see the time, and not a date as well?

best regards

Jørgen
0
Comment
Question by:Jorgen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 12

Expert Comment

by:James Elliott
ID: 40396508
How are the dates formatted in the CSV?
How are you importing your CSV to Access?
What settings have you applied to the date field(s) when importing?
0
 
LVL 4

Author Comment

by:Jorgen
ID: 40396515
Hi James
in the CSV it is stated as hh:mm
Actually (when thinking of what I am actually doing), I did import the csv to Excel and then import the Excelfile to Access, as I could not find the possibility to import a time field directly to Access.
This also answers the last question about settings, as I did not find the correct setting.

Preferably this should be imported directly from the csv to access - so if you can tell me how to import the csv, so I could state this as a timefield. I think that will Work for me. But I only get to select between different date formats not an hour format.

regards

jørgen
0
 
LVL 85
ID: 40396584
You should use an Import Specification when doing this. You'd have to first create that spec, then refer to it when you import the CSV. In this manner, you could import the Time field. Note you may end up having to import it as a Text field, and then convert it to the correct format after the import.

Capricorn1 shows how to do that here: http://www.experts-exchange.com/Database/MS_Access/Q_28532814.html

I'd suggest you import to a temporary or "staging" table, then use standard VBA/SQL to move the data over to your live tables. This gives you a chance to validate the data prior to it landing in your live tables.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40396648
Import the field as text, then have a select query to finish the data.
In this, use TimeValue([YourTextTimeField]) to convert the time only to data type Date.

/gustav
0
 
LVL 4

Author Comment

by:Jorgen
ID: 40396711
Hi both,

The issue here is that the import has to be automated in the end.

We do have 9 different systems, where we need to get data into one database and then into a PowerPivot that generates a dashboard for the management. Therefore we will have no time to validate data before uploads on a daily basis in the future, but need the uploads to Work.

If I need an extra table to get data correct, the access table might be huge, which is what I try to avoid.

regards

Jørgen
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 40396738
You can automate these processes in the end. You need to create the Import Spec one time, and you can then use it in the TransferText method.

When you import, is the Time portion of the imported date correct? For example, in your original posting you indicate the data comes in as "30/12/1899 05:57 or 30/12/1899 10:36". In those two examples, are the 5:57 and 10:36 correct? If so, then you can just import and ignore the "date" portion of that. You could use TimeValue(YourDateField) to return only the date portion.
Therefore we will have no time to validate data before uploads
That's what I call a recipe for disaster. Data must be validated, unless you're 100% confident it will always be correct (and that is rare in the extreme). The data validation would be code-based, of course. You would not expect a user to review the data to insure it's correct.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40396758
I see no issue. Run the imports, run the queries to clean up the data. That's it.

/gustav
0
 
LVL 4

Author Comment

by:Jorgen
ID: 40396856
Hi Scott

I actually did the Timevalue that you suggested, and it seems to Work fine. I just woried that I might get into situations, where this could generate issues on grouping data.

I agree with you that data needs codebased validation - but in this case data is so messed up, so that is not the biggest concern right now - Clean up is :-). But we need data until cleanup is done
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40397116
The Date data type ALWAYS includes both date and time and the date data type is actually a double precision number with the integer portion representing the date and the decimal portion representing the time.   If your input only contains time, then date will be 0.  If your input only contains a date, then time will be .0.

30/12/1899  is the origin date (the 0 date).  Earlier dates are shown as negative numbers.  So 29/12/1899 would be -1.  Later dates are positive numbers so 31/12/1899 would be 1.  Today - October 22, 2014 is day 41834.  The time is approximately 11:02 AM.  So the complete value of the date field is  41934.4599421296.

To affect what is displayed, simply change the format of the control on the form.  Do NOT set formats at the table level.  Setting a format does NOT alter what data is saved.  It only affects what is displayed.  So for example if you set the format at the table level to dd/mm/yyyy, only the date would show but if you saved Now() - which includes the time to the field, you would actually have  41934.4599421296 NOT just 41934 and that would impact sorting and comparisons.  So the bottom line is NEVER format EXCEPT for display and you will stay out of trouble with dates.  Besides, formatting (the Format() function specifically) turns a date into a string that that makes it act like a string rather than like a date.  So 01/01/15 would sort BEFORE 22/10/14 because 01 is less than 22 and strings are processed left to right, character by character.
0
 
LVL 85
ID: 40397329
I just woried that I might get into situations, where this could generate issues on grouping data.
I suppose that would depend on exactly what sort of issues you envision, but the TimeValue function will reliably return the Time from your DateTime field.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question