Solved

Importing time from CSV to MS Access

Posted on 2014-10-22
10
200 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
  • 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
 
LVL 49

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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 49

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 34

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now