update spatial data from excel to ms-sql server 2008

Question

I have 1000's of data with 50's of column. It is point data. The data is in MS-SQL server.Now each day I need to load 100's of data similar in structure. How can I achieve it ? I tried to append table but it was lengthy task. I need to change column type since mapinfo does not properly recognize every field.Also when I change the column it cannot convert some type. Is it possible or has anybody done this can of stuff! Is there any solution to this pain? I was thinking of doing direct update from excel but before I get started I was wondering advice from experts!!

Answer 1

I had similar problem a year or so ago. I tried with SSIS but I had big problems with geometry data type. I could not get it to work at that time. So solution for me was to program a little python script that would parse csv file that I prepared in Excel. Only thing that you need is Python and pymssql module (links refer to Windows OS) and a little programing knowledge. This is part from that script, maybe it will help you.

import pymssql
import sys

try:
    conn_mssql = pymssql.connect(host='10.100.0.108:3232', user='sa', password='pass123', database='address');
    conn_mssql.autocommit('ON');
    cur_mssql = conn_mssql.cursor()
except:
    print "I am unable to connect to the database"


myfile = open('file_to_import.csv', 'r')
for line in myfile.readlines():
    lista = line.replace("\n", "").split(';')

    cur_mssql.execute("INSERT INTO kbr(street_id,name,number,the_geom) VALUES (" + lista[0] + ",'" + lista[1] + "','" + lista[2] + "',geometry::STGeomFromText('POINT(" + lista[3] + " " + lista[4] + ")',0));")

myfile.close()
conn_mssql.close()

Answer 2

Depending on how much programming you want to do GeoTools may be able to help. It has an Excel datastore and an SQLServer datastore so you should be able to read in your Excel file and write the features to SQLServer reasonably easily. Most of the code you'll need can be found in the CSV2SHP tutorial.

Answer 3

You could have a look at automating it with SSIS packages. They can be a bit funny about Geometry and Geography datatypes, but you can get round that by executing SQL statement at the various steps.

Answer 4

Install FME Desktop Trial and it's very easy to translate MapInfo to Sql Server or Excel to Sql Server. No development !!