Date & Time Field

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
sunil1982Asked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
Gustav BrockConnect With a Mentor CIOCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Danny ChildIT ManagerCommented:
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
 
sunil1982Author Commented:
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
 
Gustav BrockCIOCommented:
So why those troubles? Linking the file as described works.

/gustav
0
 
sunil1982Author Commented:
Gustav,

How can I link the file , can you post me some tips.....
0
 
Gustav BrockConnect With a Mentor CIOCommented:
(Import and Link) External Data, Text file, create link ...

/gustav
0
 
sunil1982Author Commented:
I have Access 2007, the advanced options are not apperaing...
0
 
Gustav BrockConnect With a Mentor CIOCommented:
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
 
sunil1982Author Commented:
Hi Gustav Brock,

Can you share some screenshots...please.
0
 
sunil1982Author Commented:
Thank you for directing me to good solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.