Solved

Date & Time Field

Posted on 2014-01-29
12
238 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: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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

830 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