SQL DBA Blog

Tag: Data Types

  • The SSMS Import Flat File Feature

    The SSMS Import Flat File Feature

    The Import Flat File feature within SQL Server Management Studio (SSMS) was introduced in December 2017 (14.0.17213.0). This is a streamlined version of the Import Data feature and it minimises the user intervention during a file import.

    Unlike the Import data feature, you don’t have the option to save the import as a .DTS package. This makes it a good tool for quick imports of individual files to SQL, but not so much if it’s a repetitive task you want to automate.

    Below is a guide on using the Import Flat File feature, with a (realistic) twist.

    How to Import Flat File SQL Server

    The steps below are an example of a CSV file import to a SQL Server 2019 database.

    1. Create a database.

    2. Right-click your destination database, select Tasks and Import Flat File as shown below.

    3. Click Next to continue.

    4. Browse to the file, enter the desired table name and amend the schema name accordingly. Have a look here for posts on exporting data if useful.

    5. Preview the data on the next window and click Next when happy.

    6. Review Column Names/Primary Keys/NULLs, and take as much time as you need when selecting Data types!

    7. Hit Next & Finish when ready.

    8. Oh wait. I wasn’t expecting this…

    This is something that has to be included as it happens to us all. You won’t know if there are exceptions within the data, so data type conversion errors are a frequent thing in the business of importing & exporting data. We have to use any useful information we can find within the error.

    9. If you don’t know the column name, search the .csv file for ‘101.8936’ as quoted within the error message.

    10. Going back within the wizard to Modify Columns, there’s only one Decimal Data Type, so it would have been easy to see which column needs changed.

    Increasing Decimal Max Precision.

    11. Now finish the wizard and hope for the best!

    12. Let’s have a look at the new table.

  • Copying Dates into Excel

    Copying Dates into Excel

    There’s often a need to quickly copy/paste a database query output into an Excel sheet, rather than exporting as a .csv file. When you do this for a DATETIME column though, it’ll show up in Excel looking something like this.

    Copy SQL Date tp Excel

    I don’t think Excel likes the milliseconds.

    Copy Data to Excel Format

    This is the data I tried to copy over.

    Copy SQL Data Highlight

    To resolve, just convert the DATETIME columns to SMALLDATETIME, then go ahead with the copy/paste to Excel using the default Cell Format.

    Copy SQL Data to Excel Smalldatetime
    Copy SQL Smalldatetime to Excel

    I’d be interested to know if there’s an easier way to sort this on the Excel side. For now, the above will do the trick for me!