Introducing the new Import Flat File Wizard in SSMS 17.3

Play Introducing the new Import Flat File Wizard in SSMS 17.3
Sign in to queue

Description

This week's episode of Data Exposed welcomes Alan Yu to the show! Alan Yu is a PM in the SQL Server engineering group focusing on SQL Tools, and today he is in the studio to talk about the latest SQL Server Management Studio 17.3 update.

 

Alan starts off by highlighting two new features in the latest release: Import Flat File Wizard and XEvent Profiler. Import Flat File Wizard is a new tool that helps streamline the import experience for a user who wants to import .csv or .txt files without specialized domain knowledge. The XEvent profiler is a live viewer window of extended events that can be quickly launched. For the purpose of this demo, Alan chose to focus on showcasing the Import Flat File Wizard.

 

At [03:16], he begins his demo by showing two complex files that are difficult to handle with the current import experience. This includes single cell, multi-line cells as well as duplicated header rows. It is still possible to import the two files, but it is not very intuitive.

 

At [05:36] he shows how easy it is to import the files using the new Import Flat File Wizard. He simply selects his file, autogenerates a file name, and after a few more clicks, the files are successfully imported.

 

Finally, at [09:18], he describes how the wizard works by utilizing the Program Synthesis using Examples (PROSE) SDK, which is a framework developed by Microsoft AI which learns the structure of the file such as column names, delimiters, etc. He emphasizes the exciting future applications of this framework and other intelligent frameworks in future SQL tools updates.

 

Great job with the demo, and we look forward to having Alan on the show in the future!

 

To learn more about SSMS 17.3, check out this blog post: https://blogs.technet.microsoft.com/dataplatforminsider/2017/10/10/whats-new-in-sql-server-management-studio-17-3/

 

Download the latest version of SSMS to try it yourself: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

 

To learn more about PROSE, check out: https://microsoft.github.io/prose/

Embed

Download

The Discussion

  • User profile image
    tauheedul

    I added this request to Microsoft Connect in January 2017.

    https://connect.microsoft.com/SQLServer/feedback/details/3118566/ssms-sql-server-import-export-wizard-does-not-determine-correct-field-maxlength-for-flat-file-import

    I'm really happy the SSMS team implemented it in the latest version :)

  • User profile image
    chanmm

    Ctrl-Z does not work in Modify Columns page in the Import Flat File Wizard.

  • User profile image
    Davele

    What experience should I expect when the format prediction is wrong? 

    1. Does it die with a warning or

    2. Does it silently drop fields or turn them into NULL's. (like EXCEL) 

    Example: 
    Common issue with Excel as a data source. It may sense a "FieldCode" field as a nullable INT. But maybe the 211 Millionth rows, come from some system where someone has used an alpha in the as part of the code. It is almost impossible to discover that those fields were erroneously imported as NULL's. 

    Similar issue when it maps the VARCHAR fields too small & silently truncates the few fields that happen to be much longer.

    Ideally I'd love the job to die on error. AND also give me a line number &/or copy of the row that caused the data case failures. Better yet continue to scan to the end & tell me all rows that will have an issue. Thus I can resolve it once. 
    Alternatively, give me the option to scan the entire file & not just the first N rows. Often, getting the data right is way more important than getting it quickly. Sometimes we don't care & quick is better. OR we know the data is OK

  • User profile image
    dpk1982

    how is this possible, a few days ago i was struggling to import a csv file.

  • User profile image
    Macr0Cha0tic

    Thanks for the wizard.  How do we access the PROSE API for flat-file import using T-SQL?  Sounds like a good replacement for my current "comedy-limited" bulk copy code.

Add Your 2 Cents