Importing excel to access with single quote problem

[everest]

Hi,

I asked a question a few weeks/days ago about removing single quotes. I've tried the advice given to me but with no success.

I've imported a spreadsheet from Access to Excel. In the Excel spreadsheet, there are single quotes at the beginning of every cell. At the time, the single quotes didn't affect me at all for what I had to do so I didn't bother with it.

Now, I'm trying to import this Excel spreadsheet back into Access, but Access recognizes every field as a text field because of the single quotes. I have a time field that needs to come over into Access, but can't because of the text field issue.

Does anyone have any advice on either removing the single quotes or finding a way to import the time field into Access? Thanks.

 

[coco86]

Okay, I don't know the background on this but is there any reason why you can't just import the file and then go in and change the datatype of the field to date/time after it's been imported?

 

[everest]

I tend to leave out some important details in my posts.

In the time field for the Excel file, there are some fields that don't have single quotes in front of them. I'm thinking that Access doesn't know what to do when it sees some with quotes and some without. It just sends these fields to a import_error table and will not import into Access at all.

Thanks for your suggestion though.

 

[coco86]

Ok, I see your problem.

How about saving the spreadsheet as a text file and importing the text file?

 

[everest]

That works. Thanks. I actually saved it as a .CSV file and that got rid of the single quotes. Just did a cut and paste back into the .XLS file. Thanks again for your help.