[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel into Access import -- need to keep "milli-seconds"

Posted on 2007-07-24
9
Medium Priority
?
506 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:ExpExchHelp
  • 4
  • 2
7 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 19558583
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
 
LVL 85
ID: 19558599
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
 

Author Comment

by:ExpExchHelp
ID: 19558631
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:ExpExchHelp
ID: 19558687
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
 

Author Comment

by:ExpExchHelp
ID: 19558702
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19558745
just ask a  moderator to reopen the q

http://www.experts-exchange.com/Community_Support/General/
0
 

Author Comment

by:ExpExchHelp
ID: 19561396
Capricorn,

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

EEH
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

830 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