topic Re: Database problem in Data Management Questions

Database problem

DanielWaktola — Tue, 05 Jan 2016 23:24:23 GMT

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

Re: Database problem

DanPatterson_Retired — Tue, 05 Jan 2016 23:42:53 GMT

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

Re: Database problem

DanielWaktola — Wed, 06 Jan 2016 01:01:10 GMT

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

Re: Database problem

DanPatterson_Retired — Wed, 06 Jan 2016 01:15:57 GMT

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

Re: Database problem

NeilAyres — Wed, 06 Jan 2016 07:22:24 GMT

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.

Re: Database problem

JoeBorgione — Wed, 06 Jan 2016 14:52:50 GMT

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

Re: Database problem

TedKowal — Wed, 06 Jan 2016 15:32:39 GMT

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

Re: Database problem

ChrisSmith7 — Wed, 06 Jan 2016 15:43:18 GMT

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.

Re: Database problem

DanielWaktola — Thu, 07 Jan 2016 01:06:01 GMT

Thanks, Chris. Its a good suggestion.

Re: Database problem

DanielWaktola — Thu, 07 Jan 2016 01:07:00 GMT

Thanks, Ted. Its a good suggestion.

Re: Database problem

DanielWaktola — Thu, 07 Jan 2016 01:11:40 GMT

Thanks, Dan. It was a useful suggestion.

Re: Database problem

NeilAyres — Thu, 07 Jan 2016 07:17:40 GMT

So do I....