none
Doing Bulk Upload of Files other than CSV Format into AZ SQL RRS feed

  • Question

  • HI All,

    Does Anybody know how to do bulk Insert  of a tab or pipe delimited  file into Azure sql database from BLOB storage

    The example and stuffs available on documentation is all around  CSV.

    I saw an option called FormatFile however , not sure how to generate a format file for a az sql table.

    Appreciate any quick help.


    Viswa

    Tuesday, July 16, 2019 3:37 PM

All replies

  • Hello,

    If you don’t know how to deal with a format file then you can do just the same using Azure Data Factory and schedule automated execution using Azure Automation.



    Hope this helps.



    Regards,



    Alberto Morillo
    SQLCoffee.com


    Tuesday, July 16, 2019 9:17 PM
  • Thanks Alberto, 

    I initially thought of using ADF to do and i can do it easily with that .

    But it is more of trying out the BLK copy in AZ SQL which i am looking for .


    Viswa

    Wednesday, July 17, 2019 8:59 AM
  • Hi Viswa,

    Because the data is hosted in an Azure storage account, ADF is the most efficient means for accomplishing this task. If the data resides on a local host (outside of Azure) you have multiple options available to you.

    See the following: Import and export data from SQL Server and Azure SQL Database (link)

    With a hosted solution, you will need to create an Azure VM, mount the storage account as a drive letter, and then bea able to leverage the methods identified in the linked document. With Azure Data Factory this is automagically done for you. 

    PLease let us know if you have additional questions or need more specific information. Thank you!

    Thursday, July 18, 2019 11:21 PM
    Moderator
  • Thanks Mike for your reply .

    Yes  in given conditions i would get the implementations done using ADF  and as i write here, i  already did that.

    However, Specifically speaking i was  looking to implement the same in a different ways where i would create a SP in AZ SQL  and call bullk insert in SP  with a specific file name as a parameter to be loaded and then call the sp from within  the ADF.

    While i agree that this is easily done  using ADF copy activity  , i  was able to implement the aforementioned bulk loading using a CSV file from Blob and it works like a charm . The only issue is now i have a file which is not Comma separated rather a pipe delimited.

    What i am looking here is an answer from MSFT or users that can the bulk insert in azure sql support anything other than CSV or Not.

    If yes can somebody , give a code snippet for that and if no , then i believe the Bulk Insert is not yet there as product ready(this is more of a feedback rather than being critic)

    Cheers

    Viswa


    Viswa

    Friday, July 19, 2019 2:23 PM
  • Hi Viswa,

    There is a manual means in Excel to change the delimiter of a .csv file. You can use this process to flip the delimiter back and forth, as desired:

    Export or Save Excel Files with Pipe (or Other) Delimiters Instead of Commas (link)

    If you are looking for an automated process, there are some Linux tools that will do the same. Please take a look at this Stack Oveflow forum thread (link). Specifically, there is the csvtool (link).

    Thanks,

    Mike

    Tuesday, July 23, 2019 8:59 PM
    Moderator