Importing CSV files into SQL Server Express

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

Importing CSV files into SQL Server Express

Author

Topic

JezLisle
Posting Yak Master

132 Posts

Posted - 2010-10-19 : 06:01:48

I am using SQL Server 2005 Express, as no other SQL Server is available to us. We also dont have SSIS and therefore here is my question.

How can I import a flat file CSV into SQL Express, the Import Data option isnt available either. I was thinking of a Stored Procedure, but unsure if you can tell it to pick up from a location on a drive? the other issue is that there will be unknown number of rows each time I import data.

Is this possible?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2010-10-19 : 09:10:02

do you have bcp.exe? usually it's located somewhere like this:

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe

you can use bcp for this kind of thing - in fact I much perfer it over SSIS or any GUI.

elsasoft.org

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-19 : 14:26:53

or if distributed query is enabled you can use OPENROWSET also

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JezLisle
Posting Yak Master

132 Posts

Posted - 2010-10-21 : 04:16:40

Excellent, thanks for that. I've googled it and have found details all about it, so will give it a go and see how I get on.

Thanks ;-)

JezLisle
Posting Yak Master

132 Posts

Posted - 2010-10-21 : 06:19:32

I have looked at BCP and have an issue when importing data... I keep getting this error message when running BCP through the command prompt

E:\>bcp MyDatabaseName.dbo.MyTableName in E:\MyCSVFile.csv /U /P /SMyServerName\SQLEXPRESS /c /t,

SQLState = 08001, NativeError = 53
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a con
nection to SQL Server [53].
SQLState = 08001, NativeError = 53
Error = [Microsoft][SQL Native Client]An error has occurred while establishing a
connection to the server. When connecting to SQL Server 2005, this failure may
be caused by the fact that under the default settings SQL Server does not allow
remote connections.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired

Where have I gone wrong?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2010-10-21 : 07:52:40

did you really pass /U /P or did you blank out the arguments just for posting? you need to pass a user/pass if using /U and /P. For example /UsomeUser /PsomePassword

Else you should pass /T or -T which indicates using windows auth instead of sql auth.

also what's your server name? It's not really MyServerName is it? Same question for all the other values starting with My. like MyTableName etc.

elsasoft.org

JezLisle
Posting Yak Master

132 Posts

Posted - 2010-10-21 : 10:05:31

I had missed off the Username&Password, I've included the Windows Auth

No the My... are what I have used for posting

From your reply I have amended the command which now looks like this below and get a different error. As I previously used a memory stick to hold the csv files, I have created a folder on the C:\ drive to hold these, seeing if this was an issue which it wasnt.

C:\>bcp MyDatabaseName.dbo.MyTableName in C:\BCP\MyCSVFile.csv /T /SMyServerName\SQLEXPRESS /c /t,

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1

I also looked at running it through an SQL Query within SQL Server and this is both my query and the result

DECLARE @SQL VARCHAR(8000)
SELECT @SQL = 'bcp LincsPCTReport.dbo.tblLincsPCTAppts in C:\BCP\LincsPCTAppts.csv -c -t, -T -S'+ @@SERVERNAME
EXEC master..xp_cmdshell @SQL

NULL
Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file
NULL
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1
NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources