Solved

Date & Time Field

Posted on 2014-01-29
12
237 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

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…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

777 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