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

[DanPatterson_Retired]

there are no commas, making it difficult to see whether the field names  and their number, match the data columns

[DanielWaktola]

Here is the data with a coma

ID,Date,Time,DataType,Frequency

01,7-Apr,9:00AM,A,0

01,7-Apr,9:00AM,B,8

01 7-Apr,9:00AM,C,4

[DanPatterson_Retired]

now, if you are doing the join on the first column, essentially that might be treated as text because of he preceeding 0 (01), hence, if your destination field needs to be text.  alternately, it might be treating the field as a number but not stripping the 0.  So if some are being joined, as you indicated previously, can you indicate which ones aren't being joined and check those out in the source and destination tables.  you also need to confirm that each row has exactly 5 entries and there are no missing values in each row...otherwise joins go wrong...one of the downsides of using excel

[NeilAyres]

Hmmm. Here we go again...

"specifications of the database which is stored in Excel file".

I know things are getting easier and easier, and I have seen the esri demo's of just "dragging your excel sheet on to the map" and magically all the data appears but....

Excel is an excellent bi tool and millions use it everyday. But it is not a database.

[JoeBorgione]

It's the new year and I need to do my 2015 year-end taxes;  I'll be using Excel to do them...

[ChrisSmith7]

Wouldn't it be better to create a relational db in SQL with PKs/FKs, look-up tables, etc., tuned and optimized with proper indexing and clustering? You could write some stored procedures and udfs to generate/process all of your tax info... Just kidding!

I like Excel for things like finances - works well for me, too, but it does drive me nuts when I see data in Excel that probably would live happier in a db.

[DanielWaktola]

Thanks, Chris. Its a good suggestion.

[NeilAyres]

So do I....

[DanielWaktola]

Thanks, Dan. It was a useful suggestion.