question

GlenLepeska-8646 avatar image
0 Votes"
GlenLepeska-8646 asked pituach commented

Truncate Lat/Long data

Hello,

I am trying to truncate lat/long data. Here's my scenario:

These are numeric (10,8) data types first of all.

if my lat is 27.41317320 then I need to drop the zero to only return 27.4131732

Now, if my lat is this then I need to return the entire lat: 27.41317302

And if my lat is 27.41317300 then return 27.413173

I tried this:

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
1 Vote"
Viorel-1 answered Viorel-1 edited

If you want to display the data without the trailing zeroes, then try something like this:

select format(MyColumn, '0.########') as lat from MyTable


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GlenLepeska-8646 avatar image
0 Votes"
GlenLepeska-8646 answered Viorel-1 commented

Thank you Viorel-1. This does work, But I have some funkiness. I have a SQL code in an SSIS dataflow and it out puts it to a csv file. When opening the csv file in Excel, the trailing zeros are not there. But... when opening the csv file, say using notepad the zeros ARE there. I have to upload the csv file to a vendor and they tell me that the Lat/Long don't match to what they have. Frustrating to say the least.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Maybe give vendor a hint: 27.41317300 and 27.413173 represent the same latitude.

0 Votes 0 ·
pituach avatar image
0 Votes"
pituach answered

Good day,

When opening the csv file in Excel, the trailing zeros are not there. But... when opening the csv file, say using notepad the zeros ARE there.

You confuse a displaying format with actual data format. When you open the file in Excel then the Excel see it as decimal type and therefore it does not display the trailing zero since 0.10 is actually 0.1 when speaking about numbers. If you change the display type in the Excel to text instead of general then it will probably show you the trailing zero.

I have to upload the csv file to a vendor and they tell me that the Lat/Long don't match to what they have.

It could help if you provide more information about the vendor requests of the format since in theory trailing zero are allowed in most languages when dealing with geography data type

In any case, if the csv uses a format that does not fit your vendor and if it is up to you to provide a file without the trailing zero then you will need to create a new file.

If you will explain how you created this file then maybe we can help you create a file that fit the recruitment from the start.

If you want to fix this file after it was created then this is not a job for SQL Server. You should use PowerShell, C# or other technologies and languages that support parsing text better.

In general from the SQL Server perspective, you can use the function FORMAT as @Viorel-1 already explain. This is something that can help you when you create the file. Don't save the trailing zero but create a file without them.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GlenLepeska-8646 avatar image
0 Votes"
GlenLepeska-8646 answered pituach commented

pituach, thanks for the info.

I changed the column to text in the csv file I send to the vendor and it did not display the trailing zero.

As far as the vendor is concerned, they are importing the csv file in to an Oracle database. I have a job that generates a csv file daily. The vendor's requirement for Lat/Lon is to have 8 digits to right of the decimal. Ok, no problem. So I send them this Lat:

27.41317320

The vendor sends back a csv response file, basically comparing what we send and what they have. And in their response file they say records don't match

E.G What I send 27.41317320 and what they send back 27.4131732

This causes the records to be out of "sync". Like I said before, I have a SSIS package that works great. I went this route because I have to upload the csv file to their sFTP site daily. I have a SQL job agent that runs the package around 9am daily Monday - Friday. And yes, granted I could use another technology to perform this task such as C# but truly, the SSIS was easiest, fastest to get the job done.

I hope this all makes sense!

Thanks!


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@GlenLepeska-8646,

The best technology for data feeds is XML files enforced by the XSDs.
XSD plays a role of data contract between system of origin and destination.

SSIS has an XML Task, operation Validation for that.

All dependencies on UI, Excel, etc. is superficies.

0 Votes 0 ·

Yes, this is perfectly clear now, but I see that you already solve the issue :-)

I am glad to hear.

0 Votes 0 ·
GlenLepeska-8646 avatar image
1 Vote"
GlenLepeska-8646 answered

I got it to work. Added a Data Conversion transform from the source OLEDB to csv flat file destination. Problem solved.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @GlenLepeska-8646

Welcome to the microsoft TSQL Q&A forum!

I am glad that your problem has been resolved.Please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

In addition, if you have a question about ssis, I suggest you post it on the ssis forum(Integration Services (SSIS)), where people will provide you with more professional help!


If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.