Excel into Access import -- need to keep "milli-seconds"
I need to import data from an Excel spreadsheet into an Access db.
There's a specific format that I need to maintain during the import process:
In Excel:
- in formula bar, value reads "2:15:32 PM"
- I modified the cell format to read "14:15:32.021" (hh:mm:ss.000)
- so, it's great in Excel
In Access:
- imported the spreadsheet (with currently only 3 fields)
- Field with the Date/Time (and milliseconds) was automatically convert to Date/Time field... so it read "2:15:32 PM"
- I then deleted the record, changed the "DateTime" to "Text" in table design
- Then re-imported the same Excel record again... I thought it would now retain the milli-seconds, but it does not
How can I fix it in Access to that the format = "14:15:32.021" (hh:mm:ss.000)"?
EEH
There's a specific format that I need to maintain during the import process:
In Excel:
- in formula bar, value reads "2:15:32 PM"
- I modified the cell format to read "14:15:32.021" (hh:mm:ss.000)
- so, it's great in Excel
In Access:
- imported the spreadsheet (with currently only 3 fields)
- Field with the Date/Time (and milliseconds) was automatically convert to Date/Time field... so it read "2:15:32 PM"
- I then deleted the record, changed the "DateTime" to "Text" in table design
- Then re-imported the same Excel record again... I thought it would now retain the milli-seconds, but it does not
How can I fix it in Access to that the format = "14:15:32.021" (hh:mm:ss.000)"?
EEH
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Scott McDaniel (EE MVE )
Access only stores DateTime fields down to the Second, so if you want to keep the Milliseconds you'd need to somehow parse that out before the Import and then store them in a separate Integer field ... if you can do this in Excel before the import you'll be better off.
ASKER
Capricorn1:
thanks... first things first though. When saving the XLS into a CSV file, I'm already "losing" the millis-seconds. I keep on clicking "yes" when doing the XLS > CSV conversion. When reopening it, the file (in CSV) shows "2:15:32 PM".
So, how do I retain the proper format in CSV?
EEH
thanks... first things first though. When saving the XLS into a CSV file, I'm already "losing" the millis-seconds. I keep on clicking "yes" when doing the XLS > CSV conversion. When reopening it, the file (in CSV) shows "2:15:32 PM".
So, how do I retain the proper format in CSV?
EEH
ASKER
Capricorn,
ah... I guess it doesn't matter what it looks like in CSV format. I imported into Access and... voila... it comes out correctly.
Thanks,
EEH
ah... I guess it doesn't matter what it looks like in CSV format. I imported into Access and... voila... it comes out correctly.
Thanks,
EEH
ASKER
Oh... shoot... I accidentally accepted the wrong solution. Just a few seconds ago, I only had Capricorn's answer. Sorry, Capricorn. Would you like me to open the same questions again and assign you the points then.
EEH
EEH
ASKER
Capricorn,
thanks... I put the request in to reopen the question.
EEH
thanks... I put the request in to reopen the question.
EEH