Solved

Date & Time Field

Posted on 2014-01-29
12
243 Views
Last Modified: 2014-07-25
I am trying to Import a RAW Data file coming from one my system to MS-Access. But the DATE is imported as TEXT. If I mention as DATE&TIME while importing some of the data is misinterpreted. Kindly provide me a solution to import the data and store only DD/MM/YYYY HH:MM, leaving seconds out.

Sample Data :- 28/01/2014 00:19:46



Also I am trying to save the CSV file (the raw fikle from system) and analyse it in MS-Excel, where I am facing the same issue.I was trying to see whether MS-Excel can work as a transition path for the RAW file to MS-Access DB.
DateTimeField.csv
0
Comment
Question by:sunil1982
[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
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 85
ID: 39817321
Import your data to a temporary or "staging" table, and then move it to your live table and remove the Time. So if your staging table is named "tmpDataIn", and your live table is named "tblLive", you'd import you data and then run code like this:

Currentdb.Execute "INSERT INTO tblLive(Date1, Date2, Date3) (SELECT DateValue(Date1), DateValue(Date2), DateValue(Date3) FROM tmpDataIn)"

Obviously you'd have to change Table and Field names to match those in your project.
0
 
LVL 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 375 total points
ID: 39817358
Link the file as is. The wizard pops up. Click Advanced. Specify date format to D/M/Y and force data type Date on the first column (Date3) where you have empty fields. The other two will automatically be preset to Date. Also set decimal separator to something else than comma.

Sample View/gustav
0
 
LVL 23

Expert Comment

by:Danny Child
ID: 39818531
Hmmm... when I open your Excel sheet directly in Excel 2010, all time and date values are shown correctly, and I can manipulate them properly.

Is your question really about how to strip out (or round off) the Seconds values?
Do you want them deleted as-is, or rounded up or down to nearest minute?

This page may be useful:
http://www.techrepublic.com/blog/windows-and-office/three-tips-for-rounding-excel-time-values/6479/#.

and you may also want to look at the HOUR MINUTE and SECOND functions
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:sunil1982
ID: 39826293
Fits the importing from the RAW files execeutes weel and Most of the values are treated as TEXT and stored, While some of them (Those who has AM/PM) getting DAT-TIME Treatment.

So I am facing struggle within first step itself.

Even I have tried a Excel files as a step prior to ACCESS DB. But I am having issues similiarly in EXCEL Too.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 39826317
So why those troubles? Linking the file as described works.

/gustav
0
 

Author Comment

by:sunil1982
ID: 39826452
Gustav,

How can I link the file , can you post me some tips.....
0
 
LVL 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 375 total points
ID: 39826508
(Import and Link) External Data, Text file, create link ...

/gustav
0
 

Author Comment

by:sunil1982
ID: 39828761
I have Access 2007, the advanced options are not apperaing...
0
 
LVL 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 375 total points
ID: 39828855
It's down left, first page of the wizard in A2010. It has been there since at least Access 2000 so I believe that the case too for A2007.

/gustav
0
 

Author Comment

by:sunil1982
ID: 39897419
Hi Gustav Brock,

Can you share some screenshots...please.
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 125 total points
ID: 39898410
0
 

Author Closing Comment

by:sunil1982
ID: 40219455
Thank you for directing me to good solution.
0

Featured Post

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!

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…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

726 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