Sql-server – Importing a CSV file without headers into SQL 2008

csvheaderimportsql server

I want to import a CSV with 4,8M records into a SQL 2008 table. I'm trying to do it with the Management Studio wizard but it keeps trying to recognize a header row which the CSV doesnt have. I don't find any option to skip this and although I specify the columns myself, the wizard still tries to find a header row and doesnt import anything without it.

The structure of the CSV is

"818180","25529","Dario","Pereyra","Rosario","SF","2010-09-02"

I've also tried alternatives like BULK INSERT but then I find out that with BULK INSERT I can't import files with a text qualifier.

Best Answer

The easiest way for a one time import would definitely be the "Import Data" function in SQL Server Management Studio. This will launch a wizard and will allow you to define where you want to import your data from - pick "Flat File Source". The next dialog allows you to browse for the file you want to import, and you can specify all sorts of things on that dialog (like the encoding of the file, what the text qualifier is - if any - and so on.

You can also select to skip any number of rows (e.g. "skip the first 5 rows"), or you can select that the first row has column names.

alt text

If your file does not have the column names in the first row, uncheck that option.

If you need to do this import over and over again, you can save all the information about the import as a Integration Services package in SQL Server (or in an external SSIS file), and you can then run that import again and again from the SQL Server Agent "Jobs" menu (enable SQL Server Agent, if you haven't already, and find the "Jobs" sub-item - you should see all your jobs under there and you can launch them again from that menu).

And if you want to, you can also launch these SSIS packages from your C# or VB.NET code - check out this CodeProject article or see Michael Entin's blog post on the topic.