Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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!!

share|improve this question
add comment

4 Answers

up vote 3 down vote accepted

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()
share|improve this answer
    
Thanks you for reply :)!So you are creating point from the lat-lon column of csv file Am I right? –  kinkajou Nov 5 '11 at 3:44
1  
No problem. I'm doing some coordinate systems conversion with proj4 module before inserting, but you can insert lat-lon without any problem. Ideally should be if you insert as geography::STGeomFromText instead of geometry::STGeomFromText if you are using log-lat but it can be a problem for some applications because most of them only read geometry data type. –  Mario Miler Nov 5 '11 at 10:35
add comment

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.

share|improve this answer
    
Thanks I read there are lot of thing to be learned.Thank you very much! I would report my solution :) . –  kinkajou Nov 4 '11 at 11:47
add comment

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.

share|improve this answer
    
Thanks but gis.stackexchange.com/questions/16518/… says it has some issues –  kinkajou Nov 5 '11 at 3:47
add comment

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

share|improve this answer
    
I want a script running so that any changes in the excel would update database or insert! –  kinkajou Nov 5 '11 at 3:46
    
Configuration of writer : Update or Insert docs.safe.com/fme/reader_writerPDF/xls_ado.pdf –  twisig Nov 5 '11 at 19:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.