Converting data from Excel to Access

[rbrule]

Hello,

I am in the process of converting data from an excel sheet into Access 2000. I know the import procedure, however some of the fields in the Excel sheet have multiple data.

Is there a way to sort out this problem, short of cutting and pasting or re-entering the data? For example one field has the following: Mon, Jan 10,2006, 9AM to 11AM. I want to eliminate this field and create a field each for the day, date, start time and end time.

Thank you

 

[llkhoutx]

Change the complex cells in Excel. However, that Excel column is imported as a string, you can unpack it in Access.

 

[rbrule]

Do I have to make the changes manually, one at a time, or is there a way for the application to make the changes in mass?

Thanks

 

[Sergeant]

I would recommend that you import the data as-is to Access. Then, you can learn a lot about update queries while you are formatting the data how you want it. Yes, update queries will work en-mass. You will need to use built-in functions in the queries, like IIf, Left, Right, Mid and Instr. You can read all about these in Access Help...with a db open, hit Alt+F11 to see the VB Editor...use the help field in the menu area at the right.
Basically:
-Import spreadsheet
-Add target columns to the new table
-Run an update query for each target column, using a calculated field.
i.e...you can start by throwing out the 3-letter day, the comma, and the space. Make sure they are all 3-letter days, all have a comma, and all have the space (5 characters total)...then use a calculated field like this in your update query:

=Right([MyImportedField],Len([MyImportedField])-5)

Put it in the UpdateTo part of the update query.

 

[rbrule]

Thank you very much. I'll try that.