Art Exhibitions            Index           Links           Home             Feedback

Last                                                                   Next

Community Tute 18 - Format using Excel for import into participant tracker

1. Start Access and open the database we created in the last tute, select the 'Tables' tab and the 'Main Table' object then hit the 'Open' button. If you did not compete the last tute you can download the database click here to download.

This is the source data that i copied off the browser on my web site, notice that there is no clear line to separate the data, only spaces in the data. This is often what happens when data is imported from a text format, the next couple of steps we will switch to excel to show you how to format this data (or any other similar) so that it can be imported into access without errors.

2. First thing is to even up the text document by adding some extra spaces next to the first three rows of data, this will prevent problems later on.


3. Save changes to the text document.


4. Now switch to Excel, and choose 'Open' form the 'File' menu.


5. Change the 'Files of type' to 'Text Files (*.prn; *.txt; *.csv) and select and open the 'importdata.txt' file.


6. This is the dialog box you should see, select 'Delimited' and press the next button.


7. Select the 'Tab' check box and the 'Space' check box, watch the data preview change, press the next key.


8. Set the format of the date column by selecting it in the 'Data preview' and choosing data format with MDY setting (Month, Day, Year) and press the 'Finish' key.


9. The excel sheet should look like the image below, delete the empty data column, make sure the cursor is in the first column.


10. Choose 'Entire Column'.


11. Insert a row for the field names, when we import the table into access the first row can be used to set the field names of the access table (when importing to a new table).


12. Add the field names as displayed below, the last two are 'StartDate' & 'StartTime'. The next step is not really required in this example but you may need it if you import was not as 'clean' as our example. The next step converts one column of data into two.


13. Select 'Fixed width' and press the next key.


14. Click in the data preview window to choose where to split the data, press the 'Finish' key.


15. The column of data is split in two as you can see in the image below, this can be very useful.


8. Close the file, don't save changes as i have another file that we will use for the actual import. If you were doing this with real data you would save the file in excel format then import that, as we will do in the next tutorial.



Copyright © 1999 R&D Robotics Pty Ltd, All rights reserved.
Any copyright subsisting in the above code and or literary works including source files vests with R&D Robotics Pty Ltd. ACN 086 345 137


Last                                                                   Next