Solved

Date & Time Field

Posted on 2014-01-29
12
235 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
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 84
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 49

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:DanCh99
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
 

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 49

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 49

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 49

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 84

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

758 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

27 Experts available now in Live!

Get 1:1 Help Now