TransferDatabase

[jporter]

So this topic is more indirectly related to Map 3D than it is actually Map 3D.  I have a user who is attempting to import a database into Map 3D. . .that method we know very well.  The problem is that we took the database (originally a .dbf file) and converted it to Micrsoft Excel for editing.  Now we need to convert back to .dbf format and have been running into all sorts of issues. We were told by Microsoft that this can be don in Access 2016 and they sent us this link: https://support.office.com/en-US/article/transferdatabase-macro-action-f098d760-733b-45ed-b1bf-b4e2e... However, the link doesn't provide any steps on how to do this and all my research has only lead me to dead ends.  I'm hoping someone out there much smarter than myself has done this before. 

Any input is greatly appreciated, 

[Murph_Map]

What happened to the orginal database? When you converted it to excel you should have the orginal backed up. What edits did you have to do in excel that you couldn't do in the database or map3d? What is linked to the geometry? 

[jporter]

Murph,

Thanks for the response.  

The user was provided the database along with an Excel spreadsheet.  The database is a list of pipelines in a city (over 4,000).  The spreadsheet is a list of he same pipelines that are coded with a letter in order to determine whether the pipeline needs to be inspected, it's damaged, or has been repaired.  We were able to export the .dbf file out to Excel so that we can add the needed column to the file.  Now we need to get the new Excel file and convert it back to .dbf format.  The original file is still intact.  We just don't have access to ArcGIS (nor do we know how to use it) or any knowledge on how to add data to databases.  

[O_Eckmann]

Hi,

I'm not sure to well undestand your problem, but LibreOffice Calc is able to open an XLS file and export to DBF.

Olivier

[Anonymous]

Hi Jason,

at Micorsoft website it says: "The TransferDatabase action is similar to the commands on the External Data tab, under Import or Export." - have you tried to just export your data from Access to DBF?

I don't know what you are trying to do - can you epxlain a bit more? How do you import the DBF file into Map?
Is the DBF file part of a SHP file?
If that is the case - modifying the DBF file on its own is dangerous. You might break the link between the features and the attributes.

If the DBF is not part of a SHP file - what is the purpose of importing into Map? How do you link the DBF data to pipes in your drawing?

The orginal DBF file and the Excel sheet with the additional code - do they share an ID column? Could they be linked? How did the user add the additinal code to the DBF table?

In case that you have a SHP and an Excel sheet and a suitable ID field - this might work:
- connect to SHP via FDO
- in Excel sheet add two columns x,y
- fill columns x,y with 1 (fake coordinates)
- connect to Excel sheet via FDO
- join SHP and Excel in Map

Rob

[jporter]

Olivier, thanks for the response.  I'll give that a try as well. 

Robert, the DBF file is what the SHP file reads as object data assigned to whatever objects you're importing into Map 3D.  So when you import the SHP file it automatically detectst he DBF file associated with the SHP file. 

My client has a map of an entire city's storm pipe network all in a SHP file.  Someone has manually gone out and inspected each pipe and created their own Excel spreadsheet with the pipe number and a code (D, E, or F, where D = a damaged pipe, E = a good pipe, F = needs further inspection).  All the city wants is a color coded map right now.  In the past they would import the SHP file and manually go to each pipe and move it to a layer.  Historically, this process would take weeks work.  I showed them how that Map 3D can create the map for them and, if we could just add that D,E, F data to the DBF file then we should be able to code it in no time flat. 

We figured out how to export the original DBF file out to Excel and, of course, add the column.  We then saw how to export the Excel spreadsheet to DBF but it must be done in Access 2010.  Access 2013 doesn't have the ability to do it. Access 2016 states that it does, but when I go to the "External Data tab, under Import or Export" I see no way of getting it to work. 

- - - -UPDATE- - - -

Last night the user had his IT department downgrade his MS Office to 2010 and now has Access 2010.  IT WORKS LIKE A CHARM!!!! 

But we cannot hold onto Access 2010 forever so we evetually need to figure out how to do this in Access 2016.  I'm placing a call to Microsoft later today.  If I get any information I'll update the discussion. 

Thanks for all your input,

[Alfred.NESWADBA]

Hi,

>> So when you import the SHP file it automatically detectst he DBF file associated with the SHP file

That process is not safe, converting the DBF to any other format and then back to DBF might result in conflicts ... geometry might not find the correct corresponding record in the DBF!

What I would suggest is to export ID's from the SHP to any fileformat, then create a database (could be SQLite, SQL-Server, even MS-Access if it's really necessary), import the table with the ID values, add the columns and fill the data into that columns.

Now you can use Map 3D to connect to that database and create a join between the SHP dataset (via ID) to the new database. At this moment there is no need to import or export anything any more.

Good luck, - alfred -

[jporter]

Alfred,

I didn't think about that.  I'll give it a try.  Thanks!  

Currently, the user is having no issues with the method we found though, but yours appears to be a more trustworthy method.  

[luc_vanlinden]

Hi Jason

As stated earlier, you should never edit a dbf file which is "part of" a shape file using Excell/Access.

As far as I remember, looking into the format description almost 20 years ago, the correlation between SHP/SHX & DBF file (records) is based on the order of the (binary) records in those files. As such there is no "hardcoded" common id. Just first record in the SHP file corresponds as such to the first record in the DBF file.

Loading a DBF file only in any other tool (non shp file supported tool) to edit its data, potentially will do so without respecting that particular order of the records when saving back to the DBF file, hence loosing the correlation.

There is a big risk of "implicit corrupting" your shapefile while doing so.

In that case you will depend on geometrical attributes (length, area, xy) stored in the DBF file to find a way to set back the correlation, assuming these are unique somehow.

Good luck!

Luc Van Linden

[jporter]

Luc,

Agreed and I'm discussing this with the client right now. 

Thanks, 

[Murph_Map]

Jason, 

you didn't say it was part of a ESRI Shp file inthe orginal post, could saved you some time. What I would do is a maspimport in a blank dwg, creating Object Data table. After the import edit (modify) the OD table adding the new column/field then export back to a shp format. About a 10 min job. Then do a data connect and add the values to the new field while seeing the pipelines. 

As large as ASTI is I can't believe there is no Map3D specialist to do all this. 

[O_Eckmann]

Hi,

@Anonymous : problem with mapimport is that OD knows only int, real and string. When MAPEXPORT you lose all other type of field (DATE, BOOL, STRING with less than 255 caracter...) Alfred as given a nice tool DBFCommander which is able to remove, or add field without destroying field type. I've read Luc's answer not to use external program to manage DBF file, but if you don't touch to order or data, I think it's secrure.

@Anonymous : I've test your solution to keep only ID in DBF and transfer all data in SQLite to make a join. But joining a big quantity of data is really slow. Yesterday, I've tested with 700000 parcels (30kms around Paris => 180Mo SHP and original 450Mo DBF, reduced to 7Mo DBF after removing all infos except ID and SQLite 80Mo). I've tried to join SQLite to my SHP. After 1 hour, i went sleeping and this morning it was done, but I don't know really how much time it tooks. Directly connecting original SHP and DBF takes only 15 seconds.

Olivier

[Alfred.NESWADBA]

Hi,

>> Yesterday, I've tested with 700000 parcels

To be honest, I would not run a SHP then, I would have geometry and tables and views (including join relations) on a server like MS-SQL or Oracle.

I have not tried that dataamount, but I guess the relation from SHP to DBF and then to an external database might be slow, because:

• DBF itself is not really an indexed format
• The database content is not indexed?

(just 2 guesses to hopefully make the performance better)

- alfred -

[O_Eckmann]

Hi,

When reading your solution to dispatch info into as light as possible DBF and a join with external DB, I'm asking a question about what appen if I add a new geometry, is it possible to add the new corresponding record in the external DB? So I try to test and I use my biggest SHP file to see performance of junction.

Now I know it wasn't a good idea to test with so important amout of data.

I agree with you, for production workflow these data should be transfered into a PostGis server.

Olivier

[jporter]

Murph, 

Apologies, I assumed (major mistake, never assume) that by stating a DBF it was understood that it was connected to a SHP file.  

And we do have Map 3D specialists on board.  The reason for the solution I seek is because we want to push the liablility of this portion of the project back on the people who are originally providing it to out client.  This is reoccurring type of work and there are a lot of politics involved.  We want to eventually point out to them this is how the data needs to be provided to us and he's one method should they not have a true GIS software.  We want them to do the checks and balances.  

Thanks, 

[Murph_Map]

---

@Anonymous wrote:

  The reason for the solution I seek is because we want to push the liablility of this portion of the project back on the people who are originally providing it to out client.  This is reoccurring type of work and there are a lot of politics involved.  We want to eventually point out to them this is how the data needs to be provided to us and he's one method should they not have a true GIS software.  We want them to do the checks and balances.  

Thanks, 

---

So your customer has a client that they are doing this for, and you think having your customer tell the client they need to provide the data in such and such way that the client is going to listen and not tell your customer, "Work with what we give you or we will go else where." 

There are a few free full versions GIS programs out there that work with the shp/dbf files if you can't get MAP3D to do it the way you want. 

[jporter]

Actually, Murph,

Yes that is what we expect.  My client has been given the go ahead to find the most cost feasible solution for this and future projects. The person they accquire the data from is also a sub for the city.  The city basically told them to work with directly with my client and adhere to their requirements.  

We'll explore the free GIS programs if we need to, but right now this is the direction they want to pursue.  

Thank you,

[scheel]

Hi,

Alfred is right. ESRI uses a special conection between SHP/SHX and the DBF-file and also they don't really use full DBF-compatibility. The best way is connecting to the original SHP-file using fdo and connecting to the excel or MS Access file using ODBC and ODBc-fdo connection. The odbc connection just connect, it doesn't need a grafical representation. Now create a connection from the shp-file table inside Map 3D to the odbc-connection and select up to 3 properties to make a match between original shp records and odbc records.

After that, you can use bulk copy and select the original properties and those new properties you need and you will get a new shp file with the schema and the data you want.

After that you can work with this also in ArcGIS if you want.

Thorsten