loading dbf files into SQL Server using ogr2ogr

[Barrett Higman]

Hi all,

 

Not a QGIS question per se but I figured this would be the right forum to ask the question. I am trying to load a DBF file (no associated shape file) into SQL Server using ogr2ogr without success.

 

The command line I am using is (after a number of unsuccessful edits):

 

ogr2ogr -f "MSSQLSpatial" "MSSQL:Server=MyServer;Database=MyDatabase;trusted_connection=yes" "D:\PN_PLANNING_SCHEME.dbf "

 

The error message I get back is:

 

 

Any thoughts?

 

Cheers,

Barrett

 

 

Barrett Higman

GIS Officer

Alpine Shire Council

Great Alpine Road

PO Box 139

Bright Victoria 3741

Telephone: (03) 5755 0552

Mobile: 0439 368 438

Facsimile: (03) 5755 1811

Web: www.alpineshire.vic.gov.au

 

[Simon Nitz]

Hi Barrett

try creating an empty table called "PN_PLANNING_SCHEME" in SQL Server first (it only needs the first column defined from memory).

Cheers

Simon

[LawrieG]

You will need "Create Table" permsions for the SQL database your are accessing

If the table already exists you may get an alternate error and need to use the -overwrite option as per below

Also its worth considering telling ogr the table name you want through -nln 

ogr2ogr -overwrite -f "MSSQLSpatial" "MSSQL:Server=MyServer;Database=MyDatabase;trusted_connection=yes" "D:\PN_PLANNING_SCHEME.dbf " -nln "PN_PLANNING_SCHEME"

Finally when its all working for you here is a batch example I use. It allows me to drag and drop the file onto the batch script and ogr2ogr loads the data in SQL spatial

REM----------------START-------------

Setlocal DisableDelayedExpansion

echo off

SET PATH=%PATH%;"C:\Program Files\Quantum GIS Wroclaw\bin\";"C:\OSGeo4W\bin\"

SET MSSERVER="MSSQL:server=.\SQLEXPRESS;database=myDatabase;trusted_connection=yes;"

SET TABLE=%~n1

echo "Cleaning table name"

Rem remove spaces

SET TABLE=%TABLE: =_%

:_GetName

Set folder=%TABLE%

Setlocal EnableDelayedExpansion

For %%I In (^- ^| ^& ^< ^> ^^ + ^( ^) \ / . @ # $ { } [ ] ' : ` ^%% ^") Do Set folder=!folder:%%I=!

:: Now remove any !

SetLocal DisableDelayedExpansion

Set folder="%folder:!=%"

EndLocal&Set folder=%folder:~1,-1%

:_parse

Set _Flag1=

For /F "Tokens=1* Delims=~=*;,?" %%J In ('Echo !folder!') Do (

Set folder=%%J%%K

Set _Flag1=%%J

Set _Flag2=%%K

)

If NOT "%_Flag2%"=="" Goto _parse

If Not Defined _Flag1 Echo None of the characters you entered are valid. Please try again&Goto _GetName

:: Now revert to previous settings. The Set is needed to pass the

:: Folder variable back to the previous environment

EndLocal&Set folder=%folder%

REM Echo You said your folder name will be %folder%.

SET TABLE=%folder%

echo %TABLE%

REM check the table name before continuing

pause

REM if no table exists uncomment the following line and comment out the update section

REM ogr2ogr.exe -overwrite -f MSSQLSpatial %MSSERVER% -nln %TABLE% %1% 

REM update table

ogrinfo.exe -sql "delete from %TABLE%" %MSSERVER%

ogr2ogr.exe -append -update -preserve_fid -f MSSQLSpatial %MSSERVER% -nln %TABLE% %1% 

REM rebuild the spatial index

ogrinfo.exe -sql "drop spatial index on %TABLE%" %MSSERVER%

ogrinfo.exe -sql "create spatial index on %TABLE%" %MSSERVER%

pause

REM----------------END-------------

[Barrett Higman]

Hi Lawrie/Simon,

 

Thanks for your replies.

 

I had already tried both the -overwrite and -nln commands without success and I have full permissions on the database as I have successfully loaded many other types of formats into SQL Server. Simon , I will give your suggestion a go for future DBF files as this might be a solution. This time around I ended up using FME as they are not files that will change with any regularity so I don't need to set up a routine that can be repeated.

 

Thank you Lawrie for the code, it looks really interesting and I can see a number of applications where it might be useful to me.

 

Cheers,

Barrett

 

Barrett Higman

GIS Officer

Alpine Shire Council

Great Alpine Road

PO Box 139

Bright Victoria 3741

Telephone: (03) 5755 0552

Mobile: 0439 368 438

Facsimile: (03) 5755 1811

Web: www.alpineshire.vic.gov.au

 

--
You received this message because you are subscribed to the Google Groups "Australian QGIS User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to australian-qgis-use...@googlegroups.com.
To post to this group, send an email to australian-qg...@googlegroups.com.
Visit this group at http://groups.google.com/group/australian-qgis-user-group?hl=en-GB.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Luke]

Hi Barrett

 

I have used Simon's method when loading CSVs into SQL Server. Have not tried it with dbf files but just wanted to chime in and say it works with CSV.

I created a dummy table which with a single field and OGR will load and rebuild the table as per the dataset.

 

Cheers,

Luke

--

Luke Bassett
Melbourne Australia

[Barrett Higman]

That's good to know. Thanks for that Luke.

Any thoughts?