BCP to comma separated Quote surround text file.

I have a need to export (regularly) a large table. I need to export to a
comma delimited file with quote surrounds around text fields (or all fields
for all that matters).
I can export the data with no problem, I can't figure out how to get the
quote surrounds however.
Here is what my bcp statement looks like:
bcp SURVEY.dbo.vw_898002_results out e:\export\survey.dat -U ***** -P
***** -S VNET-SQL /c /t , > e:\export\out.txt
I used the bol and it looked like I should be able to do something with
the -t switch but that seems to be having no impact what-so-ever.
thanks.Quote surrounds can be made by specifying in a format file the delimiters
for each field, rather than using the -t operator. Read about format files,
they are not really that hard but many people choke on them too quickly. If
I remember correctly, you can define a column 0 that terminates with " if
you need a quote on the first column.
Think of it as a regular expression problem.
RLF
PS - Of course, it leaves me wondering what you are getting with /t. (FWIW,
I don't think it matters, but you are using /t and the doc is for -t.)
"Roger Twomey" <rogerdev@.vnet.on.ca> wrote in message
news:OJLZc7CQFHA.248@.TK2MSFTNGP15.phx.gbl...
>I have a need to export (regularly) a large table. I need to export to a
>comma delimited file with quote surrounds around text fields (or all fields
>for all that matters).
> I can export the data with no problem, I can't figure out how to get the
> quote surrounds however.
> Here is what my bcp statement looks like:
> bcp SURVEY.dbo.vw_898002_results out e:\export\survey.dat -U ***** -P
> ***** -S VNET-SQL /c /t , > e:\export\out.txt
> I used the bol and it looked like I should be able to do something with
> the -t switch but that seems to be having no impact what-so-ever.
> thanks.
>|||the /t is just one of the many versions I was trying out, I think it was
meant to be: -t \t (tab delimited)
in the end I managed to get the -t to work except for the very first record
on the very first row.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:ubZrw8DQFHA.2000@.TK2MSFTNGP15.phx.gbl...
> Quote surrounds can be made by specifying in a format file the delimiters
> for each field, rather than using the -t operator. Read about format
> files, they are not really that hard but many people choke on them too
> quickly. If I remember correctly, you can define a column 0 that
> terminates with " if you need a quote on the first column.
> Think of it as a regular expression problem.
> RLF
> PS - Of course, it leaves me wondering what you are getting with /t.
> (FWIW, I don't think it matters, but you are using /t and the doc is
> for -t.)
>
> "Roger Twomey" <rogerdev@.vnet.on.ca> wrote in message
> news:OJLZc7CQFHA.248@.TK2MSFTNGP15.phx.gbl...
>

BCP out, extended chars when field is null (sometimes)

I want to "BCP out" some tables. The format is to be delimited with "\r\n"
row terminator.
Now for whatever reason, when I look at the exported text file some rows
have extended characters. The extended characters appear in fields that
happen to be null. The kicker is for the same column some rows with nulls
come through just fine, and a few rows (same column) come in with the
extended characters.
I am aware of the issue of there being no way to represent a null in the bcp
generated text data file. It is ok, if the source is a null and then it
comes back in as an empy string. I am ok with this.
In a sense, it sort of sounds like data corruption, but the field value is
null. The extended chars appear between the delimiters. So how does NULL
turn into extended characters.
Thanks.
I just ran into this too.
I created a little C# console guy that opens the text file and strips out
the null chars that were in my file.
you run it from a command line passing in the source file name and an output
file name.
FileScrubber.exe IckyFile.txt Cleanfile.txt
I've attached the cs file for the Class if you can use it great.
(i'm no C# Guru so please be kind with the review)
Greg Jackson
PDX, Oregon
begin 666 Class1.cs
M=7-I;F<@.4WES=&5M.PT*=7-I;F<@.4WES=&5M+DE/.PT*#0IN86UE<W!A8V4@.
M1FEL95-C<G5B8F5R#0I[#0H)+R\O(#QS=6UM87)Y/@.T*"2\O+R!3=6UM87)Y
M(&1E<V-R:7!T:6]N(&9O<B!#;&%S<S$N#0H)+R\O(#PO<W5M;6%R>3X-"@.EC
M;&%S<R!&:6QE4V-R=6)B97(-"@.E[#0H)"2\O+R \<W5M;6%R>3X-"@.D)+R\O
M(%1H92!M86EN(&5N=')Y('!O:6YT(&9O<B!T:&4@.87!P;&EC8 71I;VXN#0H)
M"2\O+R \+W-U;6UA<GD^#0H)"5M35$%4:')E861=#0H)"7-T871I8R!V;VED
M($UA:6XH<W1R:6YG6UT@.87)G<RD-"@.D)>PT*"0D)<W1R:6YG('-);G!U=$9I
M;&4@./2!A<F=S6S!=.PT*"0D)<W1R:6YG('-/=71P=71&:6QE(#T@.87)G<ULQ
M73L-"@.D)"6EN="!I;G1">71E.PT*"0D)8GET92!B=$)Y=&4[#0 H-"@.D)"49I
M;&53=')E86T@.<W1R;4]U='!U=" ](&YU;&P[#0H)"0E&:6QE4W1R96%M('-T
M<FU);G!U=" ](&YU;&P[#0H-"@.D)"71R>0T*"0D)>PT*"0D)"49I;&5);F9O
M(&]B:DEN1FEL92 ](&YE=R!&:6QE26YF;RAS26YP=71&:6QE*3L-"@.D)"0E&
M:6QE26YF;R!O8FI/=71&:6QE(#T@.;F5W($9I;&5);F9O*'-/=71P=71&:6QE
M*3L-"@.T*"0D)"7-T<FU);G!U=" ](&]B:DEN1FEL92Y/<&5N4F5A9"@.I.PT*
M"0D)"7-T<FU/=71P=70@./2!O8FI/=71&:6QE+D]P96Y7<FET92@.I.PT*#0H)
M"0D)9F]R*&EN="!I(#T@.,#MI/'-T<FU);G!U="Y,96YG=&@.[:2LK*0T*"0D)
M"7L-"@.D)"0D):6YT0GET92 ]('-T<FU);G!U="Y296%D0GET92@.I.PT*"0D)
M"0EB=$)Y=&4@./2 H8GET92EI;G1">71E.PT*#0H)"0D)"2\O:68@.:70@.:7,@.
M82!N=6QL(&-H87)A8W1E<BP@.=V4@.9V]T=&$@.<VMI<"!I="XN+BY'04H-"@.D)
M"0D):68H8G1">71E(#X@.,"D-"@.D)"0D)>PT*"0D)"0D)<W1R;4]U='!U="Y7
M<FET94)Y=&4H8G1">71E*3L-"@.D)"0D)?0T*"0D)"7T-"@.D)"7T-"@.D)"6-A
M=&-H*%-Y<W1E;2Y%>&-E<'1I;VX@.97AP*0T*"0D)>PT*"0D)"4-O;G-O;&4N
M5W)I=&5,:6YE*")%<G(@.(B K(&5X<"Y-97-S86=E*3L-"@.D)"7T-"@.D)"69I
M;F%L;'D-"@.D)"7L-"@.D)"0ES=')M3W5T<'5T+D-L;W-E*"D[#0H)"0D)<W1R
M;4EN<'5T+D-L;W-E*"D[#0H)"0E]#0H-"@.D)"4-O;G-O;&4N5W)I=&5,:6YE
M*")0<F]C97-S960@.26YP=70Z("(@.*R!S26YP=71&:6QE*3L-"@.D)"4-O;G-O
M;&4N5W)I=&5,:6YE*")'96YE<F%T960@.3W5T<'5T.B B("L@.<T]U='!U=$9I
M;&4I.PT*"0D)0V]N<V]L92Y7<FET94QI;F4H(B(I.PT*"0D)0V]N<V]L92Y7
D<FET94QI;F4H(D=I9&1Y(%5P(2(I.PT*"0E]#0H)?0T*?0T*
`
end

BCP data import

Anyone knows what is the syntax for BCP import for a comma delimited and
double quote text qualifier text file ? I have tried almost all the options
but still can't figure it out. I keep getting different error messages.
T.I.A
Hi
You can use BCP with the format option to get a format file that you can
change. Alternatively look at using DTS/SSIS see
http://www.sqldts.com/default.aspx?246
John
"DXC" wrote:
[vbcol=seagreen]
> Format file for 221 text files !!!!!
> "John Bell" wrote:
|||Format file for 221 text files !!!!!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> See Erlands post http://tinyurl.com/yf6z2u
> John
> "DXC" wrote:
|||Hi
See Erlands post http://tinyurl.com/yf6z2u
John
"DXC" wrote:

> Anyone knows what is the syntax for BCP import for a comma delimited and
> double quote text qualifier text file ? I have tried almost all the options
> but still can't figure it out. I keep getting different error messages.
> T.I.A

BCP data import

Anyone knows what is the syntax for BCP import for a comma delimited and
double quote text qualifier text file ? I have tried almost all the options
but still can't figure it out. I keep getting different error messages.
T.I.AHi
You can use BCP with the format option to get a format file that you can
change. Alternatively look at using DTS/SSIS see
http://www.sqldts.com/default.aspx?246
John
"DXC" wrote:
[vbcol=seagreen]
> Format file for 221 text files !!!!!
> "John Bell" wrote:
>|||Format file for 221 text files !!!!!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> See Erlands post http://tinyurl.com/yf6z2u
> John
> "DXC" wrote:
>|||Hi
See Erlands post http://tinyurl.com/yf6z2u
John
"DXC" wrote:

> Anyone knows what is the syntax for BCP import for a comma delimited and
> double quote text qualifier text file ? I have tried almost all the option
s
> but still can't figure it out. I keep getting different error messages.
> T.I.A

BCP copies only Integer data

I'm using BCP to copy data from a fixed length delimited file into a
SQL table, i'm using a format file, here is the format file:
8.0
14
1 SQLINT 0 4 "" 1 numero_llamada ""
2 SQLNCHAR 0 1 "" 2 operario
SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 1 "" 3 cabina
SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 21 "" 4 numero_marcado
SQL_Latin1_General_CP1_CI_AS
5 SQLNCHAR 0 11 "" 5 destino
SQL_Latin1_General_CP1_CI_AS
6 SQLSMALLINT 0 2 "" 6 tarifa_aplicada
""
7 SQLNCHAR 0 1 "" 7 minuto
SQL_Latin1_General_CP1_CI_AS
8 SQLNCHAR 0 1 "" 8 hora
SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 0 1 "" 9 centesima_segundo
SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 0 1 "" 10 segundos
SQL_Latin1_General_CP1_CI_AS
11 SQLINT 0 4 "" 11 duracion_segundos
""
12 SQLINT 0 4 "" 12 costo_centavos
""
13 SQLNCHAR 0 1"" 13 Indicador_llamada_borrada
SQL_Latin1_General_CP1_CI_AS
14 SQLNCHAR 0 3 "" 14 fin
SQL_Latin1_General_CP1_CI_AS
now my problem is that the INT and SMALLINT get into the table where
they should be, but the character fields just show "NULL" and some
fields just show squares and the last character on that string, any
ideas?
Thank you(kibagami23@.gmail.com) writes:
> I'm using BCP to copy data from a fixed length delimited file into a
> SQL table, i'm using a format file, here is the format file:
> 8.0
> 14
> 1 SQLINT 0 4 "" 1 numero_llamada ""
...
> now my problem is that the INT and SMALLINT get into the table where
> they should be, but the character fields just show "NULL" and some
> fields just show squares and the last character on that string, any
> ideas?
Is the file a binary file or a text file? Since the integers make it,
I assume that it is a binary file.
Really what a fixed-length delimitted file is I don't know. Judging
from your format file, your file is fixed-width only.
One possibility that the character column has length-specifiers. In
such case you should specify the length of these specifiers in the
third column in the BCP file.
For more substantial input, and less speculation, please post CREATE TABLE
statement for the table, and a sample input file. (preferably enclosed
in a zip archive as attachment.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you for the reply, yes the file is Fixed width, and i am putting
the length of the specifiers in the third column of the format file,
the file is a binary file created from a thrid party software developed
in Borland C Builder , i'll post what you ask for later today, any
other ideas in the meantime?
Thank you|||I suggest a row terminator on your last column (column 14). Rather than "",
specify "\r\n".
Is bcp importing all the rows? (i.e. Input file has 1000 records and your
table has 1000 rows.)
Hope that helps,
Joe
"kibagami23@.gmail.com" wrote:

> I'm using BCP to copy data from a fixed length delimited file into a
> SQL table, i'm using a format file, here is the format file:
> 8.0
> 14
> 1 SQLINT 0 4 "" 1 numero_llamada ""
> 2 SQLNCHAR 0 1 "" 2 operario
> SQL_Latin1_General_CP1_CI_AS
> 3 SQLNCHAR 0 1 "" 3 cabina
> SQL_Latin1_General_CP1_CI_AS
> 4 SQLNCHAR 0 21 "" 4 numero_marcado
> SQL_Latin1_General_CP1_CI_AS
> 5 SQLNCHAR 0 11 "" 5 destino
> SQL_Latin1_General_CP1_CI_AS
> 6 SQLSMALLINT 0 2 "" 6 tarifa_aplicada
> ""
> 7 SQLNCHAR 0 1 "" 7 minuto
> SQL_Latin1_General_CP1_CI_AS
> 8 SQLNCHAR 0 1 "" 8 hora
> SQL_Latin1_General_CP1_CI_AS
> 9 SQLNCHAR 0 1 "" 9 centesima_segundo
> SQL_Latin1_General_CP1_CI_AS
> 10 SQLNCHAR 0 1 "" 10 segundos
> SQL_Latin1_General_CP1_CI_AS
> 11 SQLINT 0 4 "" 11 duracion_segundos
> ""
> 12 SQLINT 0 4 "" 12 costo_centavos
> ""
> 13 SQLNCHAR 0 1"" 13 Indicador_llamada_borrada
> SQL_Latin1_General_CP1_CI_AS
> 14 SQLNCHAR 0 3 "" 14 fin
> SQL_Latin1_General_CP1_CI_AS
> now my problem is that the INT and SMALLINT get into the table where
> they should be, but the character fields just show "NULL" and some
> fields just show squares and the last character on that string, any
> ideas?
> Thank you
>|||SSd2ZSBhbHJlYWR5IHRyaWVkIHB1dHRpbmcgdGhl
IHJvdyB0ZXJtaW5hdG9yIGFuZCBpdCBrZWVw
cyBkb2luZyB0aGUKc2FtZSwgYW5kIHllcyB0aGUg
YmNwIGltcG9ydHMgYWxsIG9mIHRoZSByb3dz
LCBidXQgaW4gdGhlIHNhbWUgd2F5IG9ubHkKdGhl
IGludGVnZXIgZmllbGRzIHNob3cgb24gdGhl
IHNxbCB0YWJsZSB0aGUgY2hhcmFjdGVyIGZpZWxk
cyBzaG93cwpOVUxMIGFuZCBzb21lIG90aGVy
cyBzaG93IHNxdWFyZXMgd2l0aCB0aGUgbGFzdCBj
aGFyYWN0ZXIgb2YgdGhhdApmaWVsZHMgc2hv
d2luZywgbGlrZSB0aGlzOgoKNDQ0NDIgTlVMTCBO
VUxMCuOQsOOYtOOQtuOkseOcs+OMsDUJ5JWD
5ZWM5IWMUgk0CU5VTEwJTlVMTAlOVUxMCU5VTEwJ
ODQJNzAwCU5VTEwJTlVMTAo0NDQ0MwlOVUxM
CU5VTEwJ44C246C545i1MQnkvYzkhYNMCTUJTlVM
TAlOVUxMCU5VTEwJTlVMTAkzNAkyMDAJTlVM
TAlOVUxMCjQ0NDQ0CU5VTEwJTlVMTAnjgLbjkLnj
hLEzCeS9jOSFg0wJNQlOVUxMCU5VTEwJTlVM
TAlOVUxMCTE0MTE3CTUxMjAwCU5VTEwJPwo0NDQ0
NQlOVUxMCU5VTEwJ46C245Sw45C3MgnkvYzk
hYNMCTUJTlVMTAlOVUxMCU5VTEwJTlVMTAkyNgky
MDAJTlVMTAlOVUxMCjQ0NDQ2CU5VTEwJTlVM
TAnjoLbjpLbjgLY1CeS9jOSFg0wJNQlOVUxMCU5V
TEwJTlVMTAlOVUxMCTI0NTg2CTc2ODAwCU5V
TEwJPwo0NDQ0NwlOVUxMCU5VTEwJ46C245S246Sw
MwnkvYzkhYNMCTUJTlVMTAlOVUxMCU5VTEwJ
TlVMTAk4NjI4NgkxNzkyMDAJTlVMTAk/ CjQ0NDQ4CU5VTEwJTlVMTAnjiLbjpLLjhLM5CeS9
jOSF
g0wJNQlOVUxMCU5VTEwJTlVMTAlOVUxMCTg1MDcJ
NTEyMDAJTlVMTAk/CjQ0NDQ5CU5VTEwJTlVM
TAnjoLbjoLLjkLkxCeS9jOSFg0wJNQlOVUxMCU5V
TEwJTlVMTAlOVUxMCTcyMDQJNTEyMDAJTlVM
TAk/ CjQ0NDUwCU5VTEwJTlVMTAnjkLDjmLTjkLbjoLLj
pLPjnLM5CeSVg+WVjOSFjFIJNAlOVUxM
CU5VTEwJTlVMTAlOVUxMCTU4OTYJMTAyNDAwCU5V
TEwJPwoKVGhhbmsgeW91Cg==|||I forgot to say that the last sample is the select from the destination
sql table|||For some reason I can't see your entire thread, but my first suggestion is
for you to view the file with a hex editor to see whether the file contains
what you expect it to.
A very odd thing is that your format file indicates that you have
1-character
SQLNCHAR columns called minuto, hora, segundos, and centesima_segundos.
How can these be represented with a single character? It seems much
more likely to me that these would be represented as unsigned 1-byte
integers
(SQLTINYINT) or other numeric data type.
Things to check with the hex editor are: is the string data actually stored
as Unicode? Is the string data terminated with char(0), or does it
contain non-printable characters (<= 001F Unicode)? (This could
cause problems, I think.)
Posting the CREATE TABLE statement for the destination table would
help.
Steve Kass
Drew University
Kibagami23 wrote:

>I forgot to say that the last sample is the select from the destination
>sql table
>
>

bcp and exporting

Hello,
I'm exporting data from text file which contains 15 lakh records.
Each field is delimited by "," and record is delimited by "$".
Only 163200 records were inserted.
I'm using this option
bcp tablename in
"datafile.txt" -c -t"," -r"$" -S"MyServer" -U"sa" -P"pwd123"
Any workarounds?Hi
Was there any error message?
John
"Raj" wrote:

> Hello,
> I'm exporting data from text file which contains 15 lakh records.
> Each field is delimited by "," and record is delimited by "$".
> Only 163200 records were inserted.
> I'm using this option
> bcp tablename in
> "datafile.txt" -c -t"," -r"$" -S"MyServer" -U"sa" -P"pwd123"
> Any workarounds?
>
>

bcp and exporting

Hello,
I'm exporting data from text file which contains 15 lakh records.
Each field is delimited by "," and record is delimited by "$".
Only 163200 records were inserted.
I'm using this option
bcp tablename in
"datafile.txt" -c -t"," -r"$" -S"MyServer" -U"sa" -P"pwd123"
Any workarounds?Hi
Was there any error message?
John
"Raj" wrote:
> Hello,
> I'm exporting data from text file which contains 15 lakh records.
> Each field is delimited by "," and record is delimited by "$".
> Only 163200 records were inserted.
> I'm using this option
> bcp tablename in
> "datafile.txt" -c -t"," -r"$" -S"MyServer" -U"sa" -P"pwd123"
> Any workarounds?
>
>

BCP and Column Names

Hi -
I would like to export the column names and data from a table to a tab delimited file. I understand that when using BCP, one would first extract the column names and then concatenate the data. I am looking for a means to dump the column names. I am
an extremely novice user, so an example would be greatly appreciated.
Thanks!
Lily
Actually it's very easy, open query analyzer, go to options, select
Results tab, then select Results to File in Default results target drop
down box, then select the delimiter in the drop down box below, make
sure Print column headers box is checked, then click OK
then type in the follow:
set nocount on
select * from [your table]
run the query and it will ask you for the filename
Eric Li
SQL DBA
MCDBA
Lily wrote:
> Hi -
> I would like to export the column names and data from a table to a tab delimited file. I understand that when using BCP, one would first extract the column names and then concatenate the data. I am looking for a means to dump the column names. I a
m an extremely novice user, so an example would be greatly appreciated.
> Thanks!
> Lily
|||BCP will not export the column heading. You can use OSQL to do this. Look
at Books on LIne for the arguments to pass to get it to be formated as you
want.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Hi,
Easy way is to use the Import and Export Utility from SQL server program
groups. Select the source as sql server and destination as Text file.
In the "destination file format" Screen in the wizard check the option
"First row has column names".
This will give you the text file out put with comma seperated with column
headings.
Thanks
Hari
MCDBA
"Lily" <anonymous@.discussions.microsoft.com> wrote in message
news:88A999B6-73A3-4612-A6CE-93F9403039D1@.microsoft.com...
> Hi -
> I would like to export the column names and data from a table to a tab
delimited file. I understand that when using BCP, one would first extract
the column names and then concatenate the data. I am looking for a means
to dump the column names. I am an extremely novice user, so an example
would be greatly appreciated.
> Thanks!
> Lily
|||Everyone always suggests using the EM or QA tools - but they are not on every workstation - nor should they be.
How about going into EXCEL - DATA>IMPORT EXTERNAL DATA>IMPORT DATA>NEW SQL SERVER CONNECTION...
Follow that through - open the table you want and you get data in EXCEL - with headings.
EXCEL can save as TDF, CSV, XLS - whatever you want.
Once you create a NEW SQL SERVER CONNECTION, it remains in the list for future use.

BCP and Column Names

Hi -
I would like to export the column names and data from a table to a tab delim
ited file. I understand that when using BCP, one would first extract the c
olumn names and then concatenate the data. I am looking for a means to dum
p the column names. I am
an extremely novice user, so an example would be greatly appreciated.
Thanks!
LilyActually it's very easy, open query analyzer, go to options, select
Results tab, then select Results to File in Default results target drop
down box, then select the delimiter in the drop down box below, make
sure Print column headers box is checked, then click OK
then type in the follow:
set nocount on
select * from [your table]
run the query and it will ask you for the filename
Eric Li
SQL DBA
MCDBA
Lily wrote:
> Hi -
> I would like to export the column names and data from a table to a tab delimited f
ile. I understand that when using BCP, one would first extract the column names an
d then concatenate the data. I am looking for a means to dump the column names.
I a
m an extremely novice user, so an example would be greatly appreciated.
> Thanks!
> Lily|||BCP will not export the column heading. You can use OSQL to do this. Look
at Books on LIne for the arguments to pass to get it to be formated as you
want.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi,
Easy way is to use the Import and Export Utility from SQL server program
groups. Select the source as sql server and destination as Text file.
In the "destination file format" Screen in the wizard check the option
"First row has column names".
This will give you the text file out put with comma seperated with column
headings.
Thanks
Hari
MCDBA
"Lily" <anonymous@.discussions.microsoft.com> wrote in message
news:88A999B6-73A3-4612-A6CE-93F9403039D1@.microsoft.com...
> Hi -
> I would like to export the column names and data from a table to a tab
delimited file. I understand that when using BCP, one would first extract
the column names and then concatenate the data. I am looking for a means
to dump the column names. I am an extremely novice user, so an example
would be greatly appreciated.
> Thanks!
> Lily|||Everyone always suggests using the EM or QA tools - but they are not on ever
y workstation - nor should they be.
How about going into EXCEL - DATA>IMPORT EXTERNAL DATA>IMPORT DATA>NEW SQL S
ERVER CONNECTION...
Follow that through - open the table you want and you get data in EXCEL - wi
th headings.
EXCEL can save as TDF, CSV, XLS - whatever you want.
Once you create a NEW SQL SERVER CONNECTION, it remains in the list for futu
re use.

Bcp and Bulk Insert not working

Hello,

I have been trying to load a delimited data file to SQL Server. I
have tried both of the options that are available: each time, I get
different errors. This is on an eval version of SQL Server 2K, with
SP 3a on a Windows XP box.

First, I tried to load the data with Bulk Insert. This didn't go
through as it requires sysadmin/bulkadmin privileges. I am the only
person using the SQL Server, and I wanted to grant myself those
privileges. But I cannot find them using the Enterprise Manager. All
I see is privileges like datareader, datawriter, etc.

Then I tried to use bcp. This doesn't seem to work either as it gives
the following error:

ERROR: DB Code: (CR001): SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does
not exist or access denied.
SQLState = 01000, NativeError = 53
Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
(Connect()).
child process exited abnormally

Anybody with a solution to make this work? For reference, I am using
the following bcp command. I can login to the database using the
server/user/password combination with no problem:

C:/Program Files/Microsoft SQL Server/80/Tools/Binn/bcp.exe
testUser.products
IN
"C:/Documents and Settings/testUser/products.txt"
-f "C:/Documents and Settings/testUser/prodformat.txt"
-t "-" -r "\r\n"
-S"sqlserver_eval" -U"testUser" -P"password" -R -k -h TABLOCK"php newbie" <newtophp2000@.yahoo.com> wrote in message
news:124f428e.0406150753.42e65c9b@.posting.google.c om...
> Hello,
> I have been trying to load a delimited data file to SQL Server. I
> have tried both of the options that are available: each time, I get
> different errors. This is on an eval version of SQL Server 2K, with
> SP 3a on a Windows XP box.
> First, I tried to load the data with Bulk Insert. This didn't go
> through as it requires sysadmin/bulkadmin privileges. I am the only
> person using the SQL Server, and I wanted to grant myself those
> privileges. But I cannot find them using the Enterprise Manager. All
> I see is privileges like datareader, datawriter, etc.
> Then I tried to use bcp. This doesn't seem to work either as it gives
> the following error:
> ERROR: DB Code: (CR001): SQLState = 08001, NativeError = 17
> Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does
> not exist or access denied.
> SQLState = 01000, NativeError = 53
> Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
> (Connect()).
> child process exited abnormally
>
> Anybody with a solution to make this work? For reference, I am using
> the following bcp command. I can login to the database using the
> server/user/password combination with no problem:
> C:/Program Files/Microsoft SQL Server/80/Tools/Binn/bcp.exe
> testUser.products
> IN
> "C:/Documents and Settings/testUser/products.txt"
> -f "C:/Documents and Settings/testUser/prodformat.txt"
> -t "-" -r "\r\n"
> -S"sqlserver_eval" -U"testUser" -P"password" -R -k -h TABLOCK

Regarding the bulkadmin role, it sounds like you may be looking at database
roles, not server roles - bulkadmin is in EM under Security, Server Roles.
If you can't access it there, then you're not connected as a sysadmin, so
you should connect as a sysadmin and add testUser to that role.

As for the connection issue, there are a few possible reasons - see "Client-
or Application-Related Causes" in this article:

http://support.microsoft.com/defaul...KB;EN-US;328306

If the article doesn't help to resolve your issue, I suggest you post again
with some more information, in particular if your XP box is on a network or
not, which protocols you configured the server to listen on (see Server
Network Utility), and which tools you have successfully connected to the
server with as testUser (eg. Query Analyzer, osql.exe).

Simon|||another thing to try is to create a DTS package to insert the data for
you.
1.) connect to the server with a login, pw
2.) select file(source)
3.) highlight both, left click onthe server icon, and pick "transform
data".
4.) double click on teh blue line
5.) verify tab one is pointing to the text file. hit preview to verify
that it will insert as desired. if not, go to properties of text file
icon.
6.) create table by "create table from source"
7.) make sure each column is represented and has an arrow pointing to
each other.
click on go.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

bcp

Hello World,
I have to load table from a a delimited file. The delimited char is (";").
Bcp is used with a format file (*.fmt).
First step :
The table contain a date field.
In the import file, the date format is : DD/MM/YYYY.
When the import runs, I obtain the message like : right truncating, so the
import stops.
Second step :
The struct table is modified with a varchar (10) for the date field.
The format file is modified.
The import runs well.
My question is :
Can we import directly date field from a file with bcp ?
If it is possible, how can we do it ?
Thank's in advance.
MLTry reformatting the source file so that the datatime values are in an
unambiguous format "yyyymmdd", or (which is my favourite) connect to the fil
e
using sp_addlinkedserver and describe it's format using the schema.ini file,
which enables you to query the file as if it were a regular SQL table.
Read more here:
http://msdn.microsoft.com/library/d... />
a_8gqa.asp
(Example H.)
...and here:
http://msdn.microsoft.com/library/d...ma_ini_file.asp
ML|||Hi
I would expect that you would get a message about the date being invalid if
dates were being interpretted as MM/DD/YYYY, therefore it may not be your
date field where this error message is being generated!!!
You may want to post your format file, command and a small section of
example data that re-creates this problem.
John
"Michel" wrote:

> Hello World,
> I have to load table from a a delimited file. The delimited char is (";").
> Bcp is used with a format file (*.fmt).
> First step :
> The table contain a date field.
> In the import file, the date format is : DD/MM/YYYY.
> When the import runs, I obtain the message like : right truncating, so the
> import stops.
> Second step :
> The struct table is modified with a varchar (10) for the date field.
> The format file is modified.
> The import runs well.
> My question is :
> Can we import directly date field from a file with bcp ?
> If it is possible, how can we do it ?
>
> Thank's in advance.
> ML
>
>|||Michel wrote:
> Hello World,
> I have to load table from a a delimited file. The delimited char is
> (";").
> Bcp is used with a format file (*.fmt).
> First step :
> The table contain a date field.
> In the import file, the date format is : DD/MM/YYYY.
> When the import runs, I obtain the message like : right truncating,
> so the import stops.
> Second step :
> The struct table is modified with a varchar (10) for the date field.
> The format file is modified.
> The import runs well.
> My question is :
> Can we import directly date field from a file with bcp ?
> If it is possible, how can we do it ?
You can use DTS and define a transformation for this column.
You could also try to not use bcp but do it from QA: change the session's
date format and use BULK INSERT (untested)
http://support.microsoft.com/defaul...kb;en-us;173907
http://msdn.microsoft.com/library/d...br />
4fec.asp
Kind regards
robert