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
ExpExchHelpAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
the only way you keep the values in the format  14:15:32.021 is to set the field type to text.

the excel file must be converted to csv before you import it.
importing a csv gives you more flexibility, you can create an import specification which you can use for importing the .csv file using codes


DoCmd.TransferText acImportDelim, "Import Specification", "MyTableX", "c:\myCsv.csv, True
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
ExpExchHelpAuthor Commented:
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
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
ExpExchHelpAuthor Commented:
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
0
 
ExpExchHelpAuthor Commented:
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
0
 
Rey Obrero (Capricorn1)Commented:
just ask a  moderator to reopen the q

http://www.experts-exchange.com/Community_Support/General/
0
 
ExpExchHelpAuthor Commented:
Capricorn,

thanks... I put the request in to reopen the question.

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