How to import data from CSV into a SQL Server 2008 table?

Question

I have a file which is fill of million of records and it looks like below:

20 Apr 2016 21:50:01,022      - username,,ResetGUI,Account,finished,8182819710127A590BAF3E5DFD9AE8B0.tomcat32,7

20 Apr 2016 21:50:01,516      - username,12345678,loadaccount,AccountAdmin,Starts,40A5D891F145C785CD03596AAD07209F.tomcat32,

I want to automate importing the data into a table.

I am not sure how it works?

Please advise!

The last edit let the line breaks vanish... In your initial question, the date and time was in its own line. I'd start to replace the hyphen and the line breaks with a comma. After this action there should be a clean comma separated import... — Gottfried Lesigang
– Gottfried Lesigang, Commented Sep 8, 2016 at 20:29

Rob Hesje · Accepted Answer · 2016-09-08 20:28:51Z

1

If it is a one time data load you can use the SQL Server Import Export Wizard.

Right Click your DB in Management Studio -> Tasks -> Import Data

The wizard will guide you through selecting a database to import to, and a data source (other DB or flat file).

answered Sep 8, 2016 at 20:28

Rob Hesje

826 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

S3S · Accepted Answer · 2016-09-08 21:05:31Z

1

Make sure you create csvtest table first. Make all cols as varchar(200) first to test import

If you can give me col names ill construct a create table script for u. I just need to know your table name. Col names and data types ( from source ).

If u plan to regurly import this file Process can be...

If table exists truncate it.
If table does not exist create one.
Bulk load csv into new table

Anyways heres how to import into existing table from csv

BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

edited Sep 8, 2016 at 21:05

S3S

25.2k5 gold badges29 silver badges48 bronze badges

answered Sep 8, 2016 at 20:34

Paul

366 bronze badges

Collectives™ on Stack Overflow

How to import data from CSV into a SQL Server 2008 table?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related