Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook
Devcon 2020 Goes Virtual! LEARN MORE >

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

CSV import of Geography field

Collapse
X
Collapse
 
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
Previous template Next
    #1

    CSV import of Geography field

    I am feeling my way here, trying to do a Bulk Import in sqlserver of a CSV file that includes a geography field.

    In SSMS this query correctly inserts the coordinates in the location field.
    Code:
    update person
Set location = Geography::STGeomFromText('Point(-94.8232471 31.438561)',4326)
where personid = 1025
    However, in the CSV file that same value generates the following error
    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 16 (Location).

    Any rows that do not have a Location value import correctly.

    Still searching...

    Bill.
    Tags: None
    #2
    Re: CSV import of Geography field

    Try formatting your data as...

    Code:
    point(-94.8232471 31.438561 4326)
    This seems to import (via CSV) without error into a Geography field.

    Comment

      #3
      Re: CSV import of Geography field

      WooHooo!! Works great. I was searching 'bulk import geography'. Search 'geography point' and I got to the right docs. Like the Alpha docs, sometimes you need to know the answer in order to search for it. Looks like it works either with or without comma separators.

      I also see that point(lat, long, srid) wants coordinates opposite from STGeomFromText, which is long, lat.

      Bill.
      Last edited by Bill Parker; 07-28-2018, 10:58 PM.

      Comment

        #4
        Re: CSV import of Geography field

        Glad that's working for you. The inclusion of the commas probably depends a bit on the import used. Using Navicat, into SQL Server 2016, using the Import Wizard, commas are not allowed.

        Adding data directly to the field, within Navicat, commas are not allowed.

        I'm also not sure about lat long being reversed. When I run this...

        Code:
        select geoLocation.Long from tblCustomers_GeoLocation
        I get the first number in the set. I see where an MS Doc I reviewed on POINT report lat long... but I'm seeing long lat in Navicat. I don't use this stuff enough to be definitive, however.

        Which number in the set returns for you when you do a SELECT fieldName.Lat ?

        Comment

          #5
          Re: CSV import of Geography field

          Well, it is NOT working with Bulk Insert!? I "swear" it was, but trying all permutations I can't get it to work again. I must have been tripping, or accidently hit the Update query I had coded.

          Some documentation mentions static methods can't be used with Bulk Insert. I'm guessing point() qualifies? But Bulk Insert does have a FIRE_TRIGGERS parameter. I have not done these, but the problem is likely it would slow the insert too much. A better solution would be if there was a way to convert to and store the well known binary in the dbf table where the source data exists, assuming I could then bulk import it to a geography field.

          We have a production dbf system that is moving to sql/web, but will happen over many months. During that time the sql tables will be totally refreshed daily with multiple daily updates to move updates during the day. With 500K records being imported daily, I am trying to use Bulk Insert.

          Comment

            #6
            Re: CSV import of Geography field

            Agreed about Bulk Insert... I can't get it to work either. After lots of reading about Bulk Insert it seems that it gives people lots of problems. So here's the best advise I can give... don't use SSMS and Bulk Insert. Sounds like you have a lot of SQL work to do. Get Navicat. It is one of the best tools I've ever purchased. It's brilliant. Using Navicat I can use the Import Wizard without issue for a CSV table which include Geography data. I very, very rarely need to drop back into SSMS... only ever for table auto-key issues... or as only Microsoft likes to put it Identity. I never have to drop into MySQL Workbench for anything. Navicat supports pretty much all databases... the majors included... and also SQLite and MongoDB.

            Don't waste any more of your time on SSMS and Bulk Insert.

            Comment

              #7
              Re: CSV import of Geography field

              Thanks David for all the testing. I had looked at Navicat, particularly if I was going to end up using MariaDB. Decided on mssql in large part due to the reported fast speed of Bulk Insert. :-) As I look at the Navicat feature matrix it does show automation features in enterprise edition. Is that needed to run import as a scheduled task? I was going to have an xbasic script create the CSVs from backend DBF tables and apply to the web database hourly through a windows scheduled task. That is what is done currently with our old DBF based web portal. The backend will ultimately get moved to the new web database, but it will take months.

              If I need 2 enterprise licenses (1 for server automation and 1 for my development box), it is getting pricey.

              Bill.

              Comment

                #8
                Re: CSV import of Geography field

                If you're only using MSSQL then it looks like the Navicat Standard Edition may work for you as well. Paying for good tools, like Navicat, that work... always pay for themselves.

                I read another post indicating that MSSQL 2017 may handle Bulk Insert of Geography data better. It's all conjecture until you try it. If it took them this long to get it to work, then it probably doesn't in the real world.

                Comment

                  #9
                  Re: CSV import of Geography field

                  I guess I got my Premium and Enterprise mixed up.

                  I am using mssql 2017. From what I read WKB (binary) format may be the best shot, but have not figured out how to do that in xbasic.

                  Comment

                  Previous template Next
                  Working...
                  X