Database problem

[DanielWaktola]

I'm in need of hints about the database design on a small study I'm currently conducting on.

Here are the specifications of the database which is stored in Excel file

• Number of data collection sites: 11 (from ID_1 up to ID_11)

• Number of data collection days:  4 days (April 7, April 8, April 9, April 11)

• Time of data collection in each day: 6 times (9:00 am, 12:00 pm, 3:00 pm, 6:00 pm, 9:00 pm; 12:00 am)

• Types (categories) of data collected at each time: 4 types (A, B, C, D)

What I did was, save the excel file in CSV, import it to the ArcMap 10.1; and Join it to the layer which consists the sample site locations. However, when I checked the merged Attribute table, not all the data was imported. I'm suspecting that the type of my data base is not compatible. I need your suggestions. Below, I have appended the database I created in Excel/CSV format.

ID Date Time DataType Frequency

01 7-Apr 9:00 AM A 0

01 7-Apr 9:00 AM B 8

01 7-Apr 9:00 AM C 4

etc

[TedKowal]

As mentioned earlier excel is a poor database... it has a nasty habit of reading the first few rows and setting the data type which is near impossible to over rule.  To give you some hints/recommendation (ones I use):

Even though it is extra work:

1.  I always import my excel data (database) into MS Access. 

2.  Check/correct the data types

3.  Use/Import/Link/Join as needed to ArcGIS

By using the above steps, I generally can avoid unexpected data types, missing data etc.

Hope this helps

[DanielWaktola]

Thanks, Ted. Its a good suggestion.