| Author |
Topic |
|
chicks2001
Starting Member
15 Posts |
 Posted - 2003-04-07 : 15:37:39
|
| I have a text file called le3.txt and I want to import it in a table on my SQL server. What is the proper syntax for this and conversely what is the syntax use for sending a table from SQL to Excel?The file is a flat file and is comma delimited. I read the articles you have posted here but they failed to clear away the mud. Please adviseThanks,CJ |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-07 : 16:20:35
|
| You can not use bcp to put data into Excel. You will need to use DTS to do that. You can use bcp to import your text file. There are a lot of different ways to do it, but here is an example (to be run at the cmd window):bcp DBname.dbo.TableName in C:\temp\le3.txt -Usa -P -Sserver1 -c -r\r\nJust type in bcp /? at the cmd prompt and you'll see all of the different switches.Tara |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-04-07 : 16:21:34
|
| exec master..xp_cmdshell 'bcp mydb.dbo.mytbl in c:\myfile.txt -Uusername -Ppwd -Sservername -c -t,'for export to excel probably best to use dts or you can do the reverse of the above to create a csv file which can be read.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
chicks2001
Starting Member
15 Posts |
Posted - 2003-04-07 : 16:39:44
|
| Thanks for you prompt response both of you. This may sound odd but I have never used an import function with SQL before and I would like to know do I need to create the table on my server first? I know this sound elementary but never having worked with it it is hard for me to understand. I know when I add data to a table that I have to use the INSERT INTO command so I am just curious. Thanks for you help |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-07 : 16:57:49
|
| If you are going to use bcp, then you will need to create the table first. If you are going to use DTS, then the table can exist but it doesn't have to.Tara |
|
|
|
chicks2001
Starting Member
15 Posts |
Posted - 2003-04-09 : 00:44:37
|
| I have tried both ways the one you suggested Tara and the one you sggested NR with no luck.This is what I put inbcp she_art-Studios.dbo.Artist in c:\my_documents\le3_flat_file.txt -Uusername -PPassword -Sservername -c -tand several different versions of that. All I keep getting is error messages. I will cut and paste if I canbcp she_art-Studios.dbo.Artist in c:\my_documents\le3_flat_file.txt -Uusername -PPassword -Sservername -c -tI couldn't paste the error meesages I got but it native error 17 and native error53. Can you help me. I am trying to practice and am getting no where. I am running SQL server 2000 on my local machine so I can practice and am getting no where fast. Any help would be fine thank you |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-04-09 : 04:35:27
|
| For a comma delimitted file you need "-t," -c gives tab delimitted.Create a tablecreate table ##a (i int, j int)create a text file e:\bcptst.txtin the text file put1,12,23,3then exec master..xp_cmdshell 'bcp ##a in e:\bcptst.txt -c -t,'select * from ##aIt will default the server, user, password - you may have to include these from the previous post if you get a connection failure error.The global temp table saves having to specify a database.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2003-04-09 : 11:11:24
|
| We have a similar app that uses bcp from xml document.I learn the user has to have SA or DDLADMIN privileges to do so.But not so if you are using DTS.I am not sure if SP3 solved this issue. |
|
|
|
chicks2001
Starting Member
15 Posts |
Posted - 2003-04-10 : 09:17:26
|
I was finally able to solve this outside of the one error message I get even though the file is transfered I get an Unexpected EOF encountered in BCP Data File. It has been suggested to me that I use \n at the end of my file. Do you have any other suggestion? While I am asking questions and thanking everyone for their help I would like to know something regarding permissions. If I wanted a user just to be able to import and export what type of permissions would I give them? Do they have to have Admin permissions? Thanks  |
|
|
|
chicks2001
Starting Member
15 Posts |
Posted - 2003-04-10 : 22:30:45
|
| I got it thanks for all your help. I do have another question. Let's say i was going to create a user defined role for a person to just do import export duties. How would I do that with the enterprise manager? Would I need to create a user defined role? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-04-10 : 22:33:48
|
| SQL Server 2000 has a Bulk Insert server role for just this purpose, but if you're using an older version of SQL Server I'm not sure a user role can be set up to provide only the permissions for bulk loading (in other words, they'll have the keys to the store) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
chicks2001
Starting Member
15 Posts |
Posted - 2003-04-11 : 08:47:01
|
Thanks, I am running SQL 2000 but I don't remeber seeing a bulk insert server role but I will look again. Sorry for the cross post. |
|
|
|
chicks2001
Starting Member
15 Posts |
Posted - 2003-04-11 : 09:50:05
|
| Thanks again for your advise. It caused me to go on a search and this is what I came up with.. If you assign the fixed server role buladmin permissions you will get an error message. According to Microsoft Support this is a bug in SQL 2000. To work around the problem, make sure that the user is also part of either the db_ddladmin or the db_owner database role in addition to the bulkadmin server role. Another workaround is to make the user the owner of the database. |
|
|
|
|
BCP and Import/ Export