4

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

4 Answers 4

3

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()
2
  • Thanks you for reply :)!So you are creating point from the lat-lon column of csv file Am I right?
    – kinkajou
    Commented Nov 5, 2011 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. Commented Nov 5, 2011 at 10:35
1

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.

1
  • Thanks I read there are lot of thing to be learned.Thank you very much! I would report my solution :) .
    – kinkajou
    Commented Nov 4, 2011 at 11:47
1

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.

1
1

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

2
  • I want a script running so that any changes in the excel would update database or insert!
    – kinkajou
    Commented Nov 5, 2011 at 3:46
  • Configuration of writer : Update or Insert docs.safe.com/fme/reader_writerPDF/xls_ado.pdf
    – user3120
    Commented Nov 5, 2011 at 19:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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