nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Losing Data When an Excel File is Imported in ArcGIS Desktop 10.1

Dr__JasrajGramopadhye

Hi All,

I have an excel file which I am trying to join to a shapefile. The field on which the join will be based is Census Block ID - a 15 digit number.

When I import this excel file in ArcGIS Desktop 10.1, ArcMap automatically converts the 15 digit number into a 13 digit number (losing the 14th and 15th digit). This results in an incorrect join.

In order to have 15 digit numbers back, I tried the following with no success -

1. Change the numeric format in ArcMap. Although this produces more digits, it replaces last two digits (14th & 15th) with zeros.

2. Tried importing the file in Text and CSV format.

3. Exported the Shapefile attributes to a table. Combined my Excel file with that table. And, imported it back to ArcMap as a CSV.

The issue of losing last two digits still persists.

Does anyone have any clue? I would appreciate any suggestions you might have.

Thanks,

Jasraj

Trouver plus de publications taguées avec

Accepted answers

JorgeOrellana

I imported the table into Arc and didn't have to modify it and it worked, I saw no issues with any of the formatting.

In Excel, if you change the GEOID10 colum to "Text" you will see the scientific notation 3.714590e+014" but if you leave it as a number should go back to the full Block ID number (even if the first row has he "abcd" dummy entry)

Also to connect to Access there is no need to create an OLEDB connection, if you are working with an *.mdb you can just add it by Adding Data (like you would with any other shp file, raster, etc).

Finally I attached an exported CSV. I haven't done anything fancy but I know it too worked on my machine.

CB_and_Hunits.csv.zip

All comments

JorgeOrellana

When you import the excel table, what type is the type that it assigns to the field that you are working with? My guess is that it is being set to a Long type and is cutting off the digits. You may want to put your Excel table into access and then import it back Arc.

Another work around is to treat the block numbers as text in both our shp file and excel table (i.e. in your first row of excel, make the block code a letter and then the rest of the entries will be treated as text) and in the shp file just add a new text attribute and calculate it with the original block number

Dr__JasrajGramopadhye

Thanks, Jorge for your reply but, the issue is not yet resolved. Here is what I get -

1. When I import my Excel file in ArcMap it converts the field on which the join will be based into "Double" although it is "Text" in Excel.

2. I tried making the block IDs letters in the 1st row of the excel sheet as you suggested. When I make the field "Text" in Excel, the block IDs turn into numbers like this: "3.714590e+014". I can see all 15 digits only if the field type in Excel is made Numbers.

3. After having done this, when change the numeric format in Arcmap to "custom", the block IDs become something like "371459000000000.000000000000000". This should actually be " 371459201001001 ".

4. For some reason, importing the data from Access has not yet worked for me. Arcmap won't allow me to set up the OLEDB connection required to connect to the Access DB.

I have attached my shapefile and Excel sheet, if that would be of any help. Please let me know your suggestions.

Thanks,

Jasraj

Files.zip

JorgeOrellana

I imported the table into Arc and didn't have to modify it and it worked, I saw no issues with any of the formatting.

Also to connect to Access there is no need to create an OLEDB connection, if you are working with an *.mdb you can just add it by Adding Data (like you would with any other shp file, raster, etc).

Finally I attached an exported CSV. I haven't done anything fancy but I know it too worked on my machine.

CB_and_Hunits.csv.zip

Dr__JasrajGramopadhye

Thanks, Jorge.

It worked.

Rejoindre la conversation

Les membres connectés peuvent publier, suivre les mises à jour, et plus encore. Nouveau ici ? Inscrivez-vous gratuitement.

Liens rapides

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.