Recommended Method to Import a .Csv File into Microsoft SQL Server 2008 R2

Recommended method to import a .csv file into Microsoft SQL Server 2008 R2?

Using BULK INSERT in a T-SQL script seems to be a good solution.

http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

You can get the list of files in your directory with xp_cmdshell and the dir command (with a bit of cleanup). In the past, I tried to do something like this with sp_OAMethod and VBScript functions and had to use the dir method because I had trouble getting the list of files with the FSO object.

http://www.sqlusa.com/bestpractices2008/list-files-in-directory/

Best way to query csv or excel file via SQL 2008 R2 SSMS 64 bit?

Figured it out:

Approach A: bcp:

1. Ensure that xp_cmdshell is on. (If you are on production environment for an enterprise company this MAY not be an option for security reasons. You may always use a BETA or QA environment to put the data to first and hopefully have linked servers set up).

   exec sp_configure 'xp_cmdshell', 1
   reconfigure

2. Create a csv file for testing purposes. I did a three column csv flat file as such:

A, Brett, 1
B, John, 2
C, Brian, 3

1. Create a temp or permanent table matching the data types shown here:

   create table Test ( value varchar(3), Name varchar(16), ID int)

2. Run bcp from the command shell ensuring you do not have the file open.

   EXEC xp_cmdshell 'bcp Test..Test in "C:\test\Test.txt" -c -t , -r \n -T'
   GO

if you are curious on switches with bcp, use the help file: EXEC xp_cmdshell 'bcp /?'
! For myself I had this method work in SQL 2005 and FAIL in SQL 2008 and 2008 R2 with this exception: Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file. I looked and looked and looked but it appears that my file in standard UTF format with the standard comma delimeter and the standard \n line feed SQL bcp does not like. However my next solution worked right away.

Approach B: Bulk Insert:

1. Follow steps 2 and 3 above the same

2. Run Bulk Insert as such:

   BULK INSERT dbo.Test
   FROM 'C:\test\Test.csv'
   WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

As long as your table data types match the import, you should be fine. Ulimtately a simple table creation and a three line statement to me is pretty quick compared to having to run a wizard constantly or open up Business Intelligence Development Studio at which point I might as well just a write a custom C# app.

Importing csv file to mssql db with bulk insert changes special characters æøå to some unknown encoding

I think there are two ways:

1. Specify CODEPAGE = RAW in your BULK INSERT command (see MSDN).
2. Create a format file and specify a collation for each column.

How to generate .csv file in SQL Server 2008R2 for data of tables

Import and export wizard of the sql server is your answer, better answer is to make a backup of you localhost database and restore it on server database, fire some script to transfer the data from the restored database to your server database. Drop the restored database.

Microsoft sql server. How to setup and import special chars like ø

BULK INSERT needs
CODEPAGE = 'ACP'

So this worked:

BULK

INSERT doorvaartenTest

FROM "c:..."

WITH

(
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n',

CODEPAGE = 'ACP'

)

Also resaved files as ANSI instead of UTF-8

How do you import UTF-8 flat files into SQL Server 2008 R2?

Not true, you simply need to choose code page 65001

Export query result to .csv file in SQL Server 2008

1. Open SQL Server Management Studio
2. Go to Tools > Options > Query
   Results > SQL Server > Results To Text
3. On the far right, there is a drop
   down box called Output Format
4. Choose Comma Delimited and click OK

Here's a full screen version of that image, below

This will show your query results as comma-delimited text.

To save the results of a query to a file: Ctrl + Shift + F

Errors in SQL Server while importing CSV file despite varchar(MAX) being used for each column

In SQL Server Import and Export Wizard you can adjust the source data types in the Advanced tab (these become the data types of the output if creating a new table, but otherwise are just used for handling the source data).

The data types are annoyingly different than those in MS SQL, instead of VARCHAR(255) it's DT_STR and the output column width can be set to 255. For VARCHAR(MAX) it's DT_TEXT.

So, on the Data Source selection, in the Advanced tab, change the data type of any offending columns from DT_STR to DT_TEXT (You can select multiple columns and change them all at once).

---

Related Topics