locked
Datatype conversion during import from csv to sql server via pipeline RRS feed

  • Question

  • I am trying to load data from a csv to a sql server database using an Azure pipeline copy data operator. During the import data is converted to other types.

    in the Source preview in the pipeline i see the following

    1- the value "0044" is converted to 44

    2- the value 2020000000000000 is converted to 2E+16

    3- the value 5.2 is converted to February 5th

    4- the value 9.78 is converted to September 1978

    so far i could not find a solution for 0044,

    i the other cases here is what i did:

    for 2 i enclosed the number 2020000000000000 in "" then it worked, though for some reason i get it enclosed in four " like so: ""2020000000000000""

    for 3 and 4 i replaced the dot for a comma and then it worked.

    But i would like to be able to tell the import utility to treat everything just as string and do the conversions in the database.

    how can i achive this?

    the code shows following for one of the columns in 3 and 4:

    (

               "source":(

                      "name": "Amount"

                      "type": "String"

               )

               "sink":(

                      "name": "Amount"

                      "type": "String"

               )

    )

    Best Regards,

    Nippur de Lagash

    Wednesday, April 1, 2020 8:45 AM

All replies

  • Can you please post CSV header and some sample 2-3 rows data. Also the target create table statement. I will give it a try when I get time.

    have you tried defining SQL data types as varchar data type ? I guess, if the SQL data type is date or int, then you will get formatted date or rounded number in SQL.


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Wednesday, April 1, 2020 12:13 PM
  • Hi Nippur de Lagash,

    For further assistance, could you please share sample data as requested by @Vaibhav

    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Thursday, April 2, 2020 10:42 PM