Database problem

2950
11
01-05-2016 03:24 PM
DanielWaktola
New Contributor II

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

11 Replies
DanPatterson_Retired
MVP Emeritus

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

DanielWaktola
New Contributor II

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

0 Kudos
DanPatterson_Retired
MVP Emeritus

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
MVP Alum

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
MVP Emeritus

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

That should just about do it....
ChrisSmith7
Frequent Contributor

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
New Contributor II

Thanks, Chris. Its a good suggestion.

0 Kudos
NeilAyres
MVP Alum

So do I....

0 Kudos
DanielWaktola
New Contributor II

Thanks, Dan. It was a useful suggestion.

0 Kudos