Solved

Importing time from CSV to MS Access

Posted on 2014-10-22
10
213 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 84
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

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 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 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 50

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 36

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 84
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sharepoint list to Access database 9 53
access query - filter field for particular number format 3 31
Convert VBA UDF to SQl SERVER UDF 4 52
Email question 12 25
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

730 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