sunil1982
asked on
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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.
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.
So why those troubles? Linking the file as described works.
/gustav
/gustav
ASKER
Gustav,
How can I link the file , can you post me some tips.....
How can I link the file , can you post me some tips.....
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have Access 2007, the advanced options are not apperaing...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Gustav Brock,
Can you share some screenshots...please.
Can you share some screenshots...please.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for directing me to good solution.
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.