How do I load text or csv file data into SQL Server?


 

How do I load text or csv file data into SQL Server?

If you need to load data into SQL Server (e.g. from log files, csv files, chat transcripts etc), then chances are, you’re going to be making good friends with the BULK INSERT command. 

The command to bulk insert comma-delimite data would be: 

BULK INSERT OrdersBulk
    FROM ‘c:\file.csv’
    WITH
    (
        FIELDTERMINATOR = ‘,’,
        ROWTERMINATOR = ‘\n’
    )

If the csv file has a header row, try this:

BULK INSERT OrdersBulk
    FROM ‘c:\file.csv’
    WITH
    (
FIRSTROW = 2,
        FIELDTERMINATOR = ‘,’,
        ROWTERMINATOR = ‘\n’
    )

Finally, you can also specify how many errors you want to allow before considering that the BULK INSERT failed.

BULK INSERT OrdersBulk
    FROM ‘c:\file.csv’
    WITH
    (
        FIRSTROW = 2,
MAXERRORS = 0,
        FIELDTERMINATOR = ‘,’,
        ROWTERMINATOR = ‘\n’
    )

Vai How do I load text or csv file data into SQL Server?

Advertisements

3 thoughts on “How do I load text or csv file data into SQL Server?

  1. Hi ,
    I did the same think, it worked .Thanks a lot
    But if the CSV file doesnt have a value in the rows like 1,tim,doyle, ,60789. Then while loading into the table. it inserts “NULL”. Is there are so that i can have empty value for that field in the table column instead of NULL.

    Thanks a lot

  2. I don’t understand this code.

    -Do you execute it as a SQL statement?
    -Which table does the data go in to?
    -Which column is the data inserted to?

    Please help!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s