SQL SERVER – Importing CSV File Into Database – SQL in Sixty Seconds #018 – Video

SQL SERVER - Importing CSV File Into Database - SQL in Sixty Seconds #018 - Video 60 Importing data into database is one of the most important tasks. I often receive questions regarding what is the quickest way to insert CSV data or how to import CSV Data into SQL Server Table. Honestly the process is very simple and the script is even simpler. In today’s SQL in Sixty Seconds Video we will learn how quickly we can insert CSV data into SQL Server.

The steps to import CSV are very simple.

Solarwinds
  • Create Table
  • Use Bulk Insert to import the data
  • Verify the data
Done! Absolutely it is that simple.

More on Importing CSV Data:

I encourage you to submit your ideas for SQL in Sixty Seconds. We will try to accommodate as many as we can.

If we like your idea we promise to share with you educational material.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
SQL SERVER – Solution – User Not Able to See Any User Created Object in Tables – Security and Permissions Issue
Next Post
SQL SERVER – ColumnStore Index – Batch Mode vs Row Mode

Related Posts

21 Comments. Leave new

  • It may be simple here, but how often is it this simple in the real world with large datasets where data is missing, implicit casts fail etc.?

    Reply
  • I have arround 16 crores of records in 16 csv files each having 1 crores of records. It is all about TRAI registred Mobile number and some other information.
    I am using Mysql and Sql server also.

    I am using bulk insert but it is too slow.

    How efficiently I can upload and also Update new date for each week.(update twice in a week)

    Please advice.

    Reply
  • Brad has a point there. Microsoft’s parser is quite error-prone. Asu, I would advice that you write a CSV parser in whatever platform you’re developing. It’s quite easy. For a .Net example, see:

    Reply
  • How about a quick video on simplest way to convert a user defined function into a script for testing purposes?

    Reply
  • What would be really useful is a script that created the table and imported the data to that table. This seems quite tedious. For example, why would I go through all of the trouble creating the table, then creating a script identifying the table headers then importing all of the data.

    What I would like to see here is a REAL USEFUL import CSV file, taking the first line as the header (Don’t forget to add the record Numbers Incremental) and then adding the data to those headers. Can this be done?

    Reply
  • I am getting below error.

    The OLE DB provider “BULK” for linked server “(null)” reported an error. The provider did not give any information about the error.

    Reply
  • $value) $data[$key] = addslashes($data[$key]);
    $con = mysql_connect($server,$DBusername,$DBpassword);
    mysql_select_db($database, $con);
    //mysql_connect($server, $DBusername, $DBpassword) or die (“$DatabaseError 54”);
    echo $query = mysql_query(“INSERT INTO $tablename (

    `name`,
    `text`
    )
    VALUES(‘$data[0]’, ‘$data[1]’)”);
    $result = mysql_query($query);

    }

    }

    ?>

    Reply
  • thanks! that was very helpful!

    Reply
  • I am using:
    BULK INSERT mdb_cleanse.dbo.[@repository employee list]
    FROM ‘C:\Users\fa01jjm\Documents\SQL Server Management Studio\Projects\Run Queries\Employee_List.csv’
    WITH
    (
    FIELDTERMINATOR = ‘,’,
    ROWTERMINATOR = ‘\n’
    )

    I get an error saying the OS cannot find the path specified

    Reply
  • Alexandru Metzak
    March 7, 2013 6:13 pm

    it’s the file on the same server as the db?

    Reply
  • Great post. How can I load a file if I don’t have the name just the directory it suppose to be in?

    Reply
  • Cannot bulk load because the file “C:\CSVData\Schools.csv” could not be opened. Operating system error code 3(The system cannot find the path specified.).
    Hi i am getting this error, can you please help me in resolving?

    Reply
  • The csv file must be onthe same server as the db

    Reply
  • Is there any way to programmatically import a csv’s contents into a table in SQL Server 2012 without putting the file on the same server as the DB? Permissions aren’t an issue, but I can’t create new files on a prod environment without going through a whole verification process–which violates the “programmatically” part of my question.

    Reply
  • Muzammil Rizwi
    August 5, 2015 3:03 pm

    how to insert bulk data consist of arabic and english values

    Reply
  • Muzammil Rizwi
    August 5, 2015 3:17 pm

    getting garbage values on inserting arabic data/values

    Reply
  • Is there a way to read the csv files without their headlines? (many CSV files have text in the first row). Thank you

    Reply
  • how to load csv format data in sql server table

    Reply
  • Getting this error ?

    Msg 4866, Level 16, State 1, Line 16
    The bulk load failed. The column is too long in the data file for row 1, column 12. Verify that the field terminator and row terminator are specified correctly.
    Msg 7399, Level 16, State 1, Line 16
    The OLE DB provider “BULK” for linked server “(null)” reported an error. The provider did not give any information about the error.
    Msg 7330, Level 16, State 2, Line 16
    Cannot fetch a row from OLE DB provider “BULK” for linked server “(null)”.

    Reply

Leave a Reply

Menu