topic Re: I have 250 fields(comlumns) in excel. Can I create a shapefile? What is the work around? in Data Management Questions

I have 250 fields(comlumns) in excel. Can I create a shapefile? What is the work around?

OzKhan — Fri, 28 Oct 2016 12:42:20 GMT

I have 252 fields as a .CSV file. I would like to create a shapefile but I get the following error...

What is the work around for this?

Thanks,

Re: I have 250 fields(comlumns) in excel. Can I create a shapefile? What is the work around?

JoeBorgione — Fri, 28 Oct 2016 13:19:18 GMT

Shapefile or feature class? 250 columns for any database table is poor design IMHO.

Re: I have 250 fields(comlumns) in excel. Can I create a shapefile? What is the work around?

JoshuaBixby — Fri, 28 Oct 2016 14:00:46 GMT

I agree with Joe, that many columns typically represents a poor data design, especially considering your sample data has roughly 200 fields unpopulated. That said, what tool are you using?

Re: I have 250 fields(comlumns) in excel. Can I create a shapefile? What is the work around?

JoeBorgione — Fri, 28 Oct 2016 14:24:04 GMT

....especially considering .... 200 fields unpopulated

I didn't even open it. Good catch!

Re: I have 250 fields(comlumns) in excel. Can I create a shapefile? What is the work around?

DanPatterson_Retired — Fri, 28 Oct 2016 14:25:53 GMT

Not to mention, that you haven't indicated how many rows and whether all those rows are fully populated or whether the fields are of a unique data type. Clean it up, get rid of the extra pages... lean and clean, no blank rows, no spaces in column headers, unique data type per column... you might have a good chance.

Re: I have 250 fields(comlumns) in excel. Can I create a shapefile? What is the work around?

TedKowal — Fri, 28 Oct 2016 15:07:04 GMT

I agree with all that was stated in the chain --- bad design -- however I feel pain sometime your stuck dealing with issues like this.... I can give you a technique I use when dealing with large amount of columns.....

Export in multiple passes with smaller number of columns...

First create a numeric column in your spread sheet and fill it with sequential numbers ending with your last row this will be you "own" uniqueId -- call it MYID now export MyID, col 1-25 as a table in access -- export MyID, col 26-50 -- and so on as a table in access.

Now you can manipulate your data within a real database.... In GIS you can join the tables based upon "MYID' and recreate the 250+ columns of data

Re: I have 250 fields(comlumns) in excel. Can I create a shapefile? What is the work around?

TedKowal — Fri, 28 Oct 2016 15:34:59 GMT

I have attached a mdx and personal database that has 75 of your columns seperately exported and re-attached in GIS in various of ways:

1. with joins

2. as an exported feature class containing all of the columns

This was using the same technique as a described earlier.

Re: I have 250 fields(comlumns) in excel. Can I create a shapefile? What is the work around?

OzKhan — Mon, 31 Oct 2016 11:37:57 GMT

HI fellow GIS users and experts,

Firstly I would like to apologize for not describing the project. I do realize that this is a poor design but I couldn't think of anything better. I attached a sample with 5 rows but it will have 480 rows. So let me discuss what I am looking at...

I am going to have 480 rows in total. The data contains field inventory of pedestrian ramps across 480 intersections in one county. Each intersections may have one to twenty pedestrian curb ramps (pedestrian ramps in sidewalks, roundabouts, medians, porkchops etc). We have about 10 elements for each curb ramps and each needs its own column. So at the extreme, the database could have as many as 10 x 20 = 200 columns with all of the evaluation criteria for up to 20 curb ramps at one intersection.We don't have the budget to individually create points for each ramps at an intersection. Also, that would cluster too many data for a single location if displayed on a high level. Note that not all the fields may be populated for a single intersection.

Can you suggest me what could be perhaps a good option to design a fair database?

Re: I have 250 fields(comlumns) in excel. Can I create a shapefile? What is the work around?

JoeBorgione — Mon, 31 Oct 2016 13:27:01 GMT

480 points isn't that tough to manage. However you might want consider related tables of 'pedestrian ramps in sidewalks, roundabouts, medians, porkchops etc'. Sounds like not all intersections have all these various types associated with them, so for my money, relationship classes are the way to go.

Re: I have 250 fields(comlumns) in excel. Can I create a shapefile? What is the work around?

VinceAngelo — Mon, 31 Oct 2016 14:08:41 GMT

While 250+ fields is an ugly database design, the error message is actually based on the 4000 byte record width limitation of dBase, which can be reached in as few as sixteen 254-character string fields. The dBase III specification also has a 100 field limit, which ArcGIS ignores, but it does honor the hard limit of 255 fields (field count is a single byte in the dBase header).

File geodatabase does not have the 4K buffer limitation, and supports 64K columns (which is far greater than the database limitations of 256 or 512, depending on ArcGIS version).

So your options are to slice the the table width to something that dBase can support or to use a format other than shapefile.

- V

Re: I have 250 fields(comlumns) in excel. Can I create a shapefile? What is the work around?

LanceShipman — Mon, 31 Oct 2016 18:59:00 GMT

There is also a limit of 10 for field name widths. You have several fields that are 11 wide. Also we don't support field names that start with a number, which you have several of.

CORRECTION: I reviewed our support for leading numbers in field names and I'm incorrect. We have no problem with leading numbers in shapefile field names and handle it when copy/pasting or exporting into another datasource that does not support leading numbers in field names.

Re: I have 250 fields(comlumns) in excel. Can I create a shapefile? What is the work around?

TedKowal — Tue, 01 Nov 2016 14:20:13 GMT

Here are some publications with lots of information and contacts to assist you in designing a better schema for your data....

Best Practices in Geographic Information Systems-based Transportation Asset Management | Federal Highway Administration …

Proposed features/sidewalk schema - OpenStreetMap Wiki

Re: I have 250 fields(comlumns) in excel. Can I create a shapefile? What is the work around?

RayCrew — Fri, 04 Nov 2016 13:35:07 GMT

Just asking for a clarification on the original question: Was the error box you show was encountered in Excel?

Re: I have 250 fields(comlumns) in excel. Can I create a shapefile? What is the work around?

OzKhan — Fri, 04 Nov 2016 13:40:02 GMT

In ArcGIS

Re: I have 250 fields(comlumns) in excel. Can I create a shapefile? What is the work around?

DanPatterson_Retired — Fri, 04 Nov 2016 13:47:33 GMT

What of the suggestions listed in the above have you tried?

If you need to retain a shapefile output, then you have to overcome the limitations.

Have you tried the geodatabase option?

Re: I have 250 fields(comlumns) in excel. Can I create a shapefile? What is the work around?

RayCrew — Fri, 04 Nov 2016 13:56:32 GMT

OK, I was confused by your title of "I have 250 .... in excel" as meaning you were working in Excel. Are you just using the term excel in a generic sense to mean any table not associated with a current spatial data layer?

Re: I have 250 fields(comlumns) in excel. Can I create a shapefile? What is the work around?

OzKhan — Fri, 04 Nov 2016 13:57:02 GMT

Hi Dan,
I personally prefer feature class in file geodatabase instead of shapefile for this. It works but the clients want shapefile as they don't understand GIS very well and gets lost when I tell them the similarities and differences between feature class and shapefile. I think I will shrink the number of fields to 140-150 field and see if that could help to create a shapefile.

Re: I have 250 fields(comlumns) in excel. Can I create a shapefile? What is the work around?

DanPatterson_Retired — Fri, 04 Nov 2016 14:02:50 GMT

I would gently guide them by setting things up by perhaps delivering a package of some form, so that they can open a project and it miraculously opens with the table ready for viewing and the dataframe fully set up.

It would be worthwhile to point out to them that having even 150 fields is going to be difficult to view, organize and comprehend.

A push in the right direction would certainly make your task less onerous and it will head off those...

"on second thought can you... " type requests which will inevitably follow.

Good luck

Re: I have 250 fields(comlumns) in excel. Can I create a shapefile? What is the work around?

OzKhan — Fri, 04 Nov 2016 14:04:11 GMT

I initially created intersection points in ArcMap, added X-Y coordinates to those points and exported the table to .csv. Now I am manually entering data in the excel table. Once I am done I intend to import the table in GIS and create a shapefile using the X-Y coordinates.

I included a sample of 5 rows of my table. The final table will have anywhere between 470 and 500 rows.

Re: I have 250 fields(comlumns) in excel. Can I create a shapefile? What is the work around?

VinceAngelo — Fri, 04 Nov 2016 14:05:40 GMT

Don't forget about the 4000 byte record limit. You could store all the fields if the mean field width is less than 16.

Other products may enforce the 100 field limit of dBase, in which case the "140-150" field count would still fail (just later in the conversion process).

- V