import csv into db

[bigz60]

Hello.

I am working with VB2005 and Sql Server 2005, both express versions.

I have a simple table that contains four columns.

Table name is "mytable"
Column names are: "ssn","Last_Name","First_name", and "hire_date"

I would like to do a mass import of all my 600 employees into this table. I have a csv file that contains all of the data that I need, and nothing extra in the csv.

Also, should my csv file have column names, and if so, do they have to match the db column names.

Thanks in advance.

 

[mrdenny]

Column names in the csv are optional.

You have a couple of different options.

BCP, SSIS, or the import/export wizzard.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jacktek]

We have a significant investment in using BCP in SQL Server 2000 installations. We need to continue use of that code in SQL Server 2005 installations. I am trying to get our BCP code to work on SQL Server 2005 Express. Below is an example:

exec master.dbo.xp_cmdshell 'bcp "SELECT * FROM RS2001_B2..Product" queryout c:\Validata\bcp_TextFiles\Product.txt -c -T'

The above generates the below error:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

After several revolutions on the SQL Server 2005 online help "merry-go-round", I went to the internet, and found the below solution.

exec sp_configure 'xp_cmdshell', '1'

This generates the following error:

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.

The SQL Server 2005 Management Studio Express menu doesn't have any options leading to the "Surface Area Configuration" (for enabling xp_cmdshell). Also -I need to be able to do w/scripts, to be able to possibly temp turn xp_cmdshell on to use, then turn back off w/scripts.

So the answer is probably simple, but I have invested most of the morning, and seem to be at a dead end. Anyone have a solution or teck tip for me ? :^)

Thanks

 

[ESquared]

jack, you need to start a new thread for your question instead of hijacking this one...

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]