| 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 |
|
|
|
|
Importing CSV files into SQL Server Express