bcp -x and "Invalid Field index."

Hey, I am using bcp to try to create my xml file formate off from a table
that has about 477 fields. I am getting the following error:
F:\f\COFS>bcp cofsETL.dbo.FNIApplication format nul -T -c -x -f
FNIApplication_X
MLFormatFile.xml
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Format File : Invalid Field index.
When I remove the -x to create the non xml file format no issues arise.
Idea's? I have not been able to find anything on web about this.
bcp Yea, you know me. (bcp Yea, you know me.@.discussions.microsoft.com)
writes:
> Hey, I am using bcp to try to create my xml file formate off from a table
> that has about 477 fields. I am getting the following error:
> F:\f\COFS>bcp cofsETL.dbo.FNIApplication format nul -T -c -x -f
> FNIApplication_X
> MLFormatFile.xml
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Format File : Invalid Field index.
>
> When I remove the -x to create the non xml file format no issues arise.
> Idea's? I have not been able to find anything on web about this.
Sounds like a bug to me. I suggest that you file one on
http://connect.microsoft.com
Personally, I have not been able to whip up any enthusiams over the XML
format file. I have not been able to find that it buys me anything that
the old format does not. (Which, true, is fairly obscure. But I know
it by now.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

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 template using quoted text qualifiers

I'm dumping data from a table via BCP and when BCPing them back in to another table, it errors out on numeric and date fields. I'd like to place quote marks on the text fields. How do I do this using BCP?I'd say the other table does not match your structure of the first table

Where's the bcp command and the DDL of the tables.|||The tables are exactly the same. They are duplicates of one another just in different databases.
When I tried to manually import the file that that was exported using BCP I got the error stating that I was trying to put a string into a numeric field. When I exported the table manually with quotes, I had no problem importing it in to the other table.
Do you know how to export a table with the quotes around the text in the file using bcp?|||You can either:
- create a view or;
- create a procedure.

Within either you can embed any character to be part of the data you're trying to export. If it's a view, - use OUT, else - QUERYOUT.|||So there is no switch with the bcp command to put in quotes?sql

BCP puts nulls in output text file

Hi,

I have been trying to output varchar fields from a table to a text file using BCP. When the field in the table is an empty string (where I have specifically set it to '') BCP places an ascii null in the output text file.

Is there a way I can tell it to just not place anything in the output for this field.

Other inportant information: I am trying to use comma as the field terminator (-t,). If I use the default tab terminator, then things seem to be ok.

Thanks for any help.
ScottWhat does your bcp command look like?

with -c it should be OK.

from
create table bcp (s varchar(10), t varchar(10) null, u varchar(10) not null, v varchar(10))
insert bcp select 'asd', 'asd', 'asd', 'asd'
insert bcp select 'asd', '', '', 'asd'
insert bcp select 'asd', 'asd', 'asd', 'asd'
exec master..xp_cmdshell 'bcp test..bcp out c:\bcpfile.txt -S(local) -T -t, -c'

I get
asd,asd,asd,asd
asd, , ,asd
asd,asd,asd,asd|||The command I used was very similar to yours and when I ran yours, I got the same results you did. However, notice that where the fields in the table are empty, bcp puts a space between the commas (, ,). What I would like to see is just (,,) with no space between.

If I allow nulls in the table, then the output file contains a null (ASCII 0) in that spot between the commas.

Now, if I use DTS to output the file, then everything comes out as I want it to. I just wanted to avoid the use of DTS for something so simple.

Thanks for your help.
Scott|||However, notice that where the fields in the table are empty, bcp puts a space between the commas (, ,). What I would like to see is just (,,) with no space between.

Actually the fields are NOT empty, they contained a zero length string. This is not the same as empty. The ASCII 0 or (, ,) is a zero length string, if you insert a NULL then your file will only contain (,,).sql

BCP Problems with ISO Latin-1 Characters past ASCII 192

I am trying to export data from a table with char and varchar fields
(Non-Unicode) that contains the ISO Latin-1 Characters. The problem exist
with the character past ASCII 192 the characters like , , , etc. My
export statement looks like this as I am using the -c flag.
bcp database.dbo.table out C:\table.txt" -c -t"|" -SMACHINE -Usa -P
But I get the following:
MXICO| |MX|0|1
The data in the table looks like this
MXICO| |MX|0|1
-n (Native) did not seem to work.
-w (Unicode character) did work, geting it out, but I can not put it back
in...
Do I have some sort of code page/collation not set correct?
Thanks
Reeves
Hi
Have you tried -C RAW?
John
"Reeves Smith" <ReevesSmith@.WillTellYouLater.com> wrote in message
news:uXjvY5UVEHA.3988@.tk2msftngp13.phx.gbl...
> I am trying to export data from a table with char and varchar fields
> (Non-Unicode) that contains the ISO Latin-1 Characters. The problem exist
> with the character past ASCII 192 the characters like , , , etc. My
> export statement looks like this as I am using the -c flag.
> bcp database.dbo.table out C:\table.txt" -c -t"|" -SMACHINE -Usa -P
> But I get the following:
> MXICO| |MX|0|1
> The data in the table looks like this
> MXICO| |MX|0|1
> -n (Native) did not seem to work.
> -w (Unicode character) did work, geting it out, but I can not put it back
> in...
> Do I have some sort of code page/collation not set correct?
> Thanks
> Reeves
>
|||Reeves,
We had a similar problem that I believe was fixed when we switched to
sing -C ACP. (RAW may do for you as well, try it out.) The problem is
that the default is -C OEM, which is basically the old DOS character set and
that is trashing some characters for you.
Russell Fields
"Reeves Smith" <ReevesSmith@.WillTellYouLater.com> wrote in message
news:uXjvY5UVEHA.3988@.tk2msftngp13.phx.gbl...
> I am trying to export data from a table with char and varchar fields
> (Non-Unicode) that contains the ISO Latin-1 Characters. The problem exist
> with the character past ASCII 192 the characters like , , , etc. My
> export statement looks like this as I am using the -c flag.
> bcp database.dbo.table out C:\table.txt" -c -t"|" -SMACHINE -Usa -P
> But I get the following:
> MXICO| |MX|0|1
> The data in the table looks like this
> MXICO| |MX|0|1
> -n (Native) did not seem to work.
> -w (Unicode character) did work, geting it out, but I can not put it back
> in...
> Do I have some sort of code page/collation not set correct?
> Thanks
> Reeves
>

bcp is inserting blank space for empty string

I'm doing a bcp out of a table to a file. Some of the fields in a
record may have an empty string.

When I bcp out to the file and examine it, the fields that have an
empty string in the database now show up in the file as having one
blank character.

Why is bcp doing this? I don't want the blank character in my output.

Thanks,
EricWhich version of MSSQL? What data type is the column? Is ANSI_PADDING
on or off for the column? If your column is char(1), then this would be
expected, as char columns are padded out with spaces; a varchar should
not be padded, though.

If this doesn't help, I suggest you post (simplified) CREATE TABLE and
INSERT statements to show exactly what your table and data look like.

Simon|||Simon Hayes wrote:
> Which version of MSSQL? What data type is the column? Is ANSI_PADDING
> on or off for the column? If your column is char(1), then this would
be
> expected, as char columns are padded out with spaces; a varchar
should
> not be padded, though.
> If this doesn't help, I suggest you post (simplified) CREATE TABLE
and
> INSERT statements to show exactly what your table and data look like.
> Simon

SQL Server 2000
Varchar 16
ANSI_PADDING is off.

Give it a shot. Create a table with three columns, all varchar. Insert
an empty string into each to get rid of any nulls. Then do a bcp out to
an output file and let me know if you get the same results.

Eric|||"epaetz" <epaetz41@.hotmail.com> wrote in message
news:1112704850.296614.123580@.o13g2000cwo.googlegr oups.com...
> Simon Hayes wrote:
>> Which version of MSSQL? What data type is the column? Is ANSI_PADDING
>> on or off for the column? If your column is char(1), then this would
> be
>> expected, as char columns are padded out with spaces; a varchar
> should
>> not be padded, though.
>>
>> If this doesn't help, I suggest you post (simplified) CREATE TABLE
> and
>> INSERT statements to show exactly what your table and data look like.
>>
>> Simon
> SQL Server 2000
> Varchar 16
> ANSI_PADDING is off.
> Give it a shot. Create a table with three columns, all varchar. Insert
> an empty string into each to get rid of any nulls. Then do a bcp out to
> an output file and let me know if you get the same results.
> Eric

I used this test script (on 8.00.760 Enterprise):

set ansi_padding off
go
create table eric (col1 varchar(16) null)
go
insert into eric select '' -- empty string
insert into eric select ' ' -- single space
go
select col1, len(col1) as 'Length', datalength(col1) as 'Datalength', col1 +
'X'
from eric
go

Then I exported the file with bcp:

bcp Development..eric out c:\temp\eric.txt -S kilkenny -c -T

When I checked eric.txt with a hex editor, it showed this:

00 0D 0A 20 0D 0A

So the empty string is an ASCII NUL character in this case, but the space is
ASCII 20. Is this the behaviour you see, or do you get something different?
Setting ANSI_PADDING ON didn't change the output (and BOL recommends it
should always be on anyway).

Simon|||I found a work around, using a Case structure in the sql query to
change empty string to a null. The BCP in turn outputs the resulting
null as an empty!

Thanks for your assistance.

Eric

BCP in csv with qouted and unqouted fields

I need to import several csv files.
each field is comma seperated but some fields use double qoutes as a text
qualifier.
The fields that have double qoutes contain values seperated by commas.
ie. john, doe, "1,5,6",1,3
in the command line I use -c -t , -r \n
when it imports into the table it splits up the qouted columens into
multiple fields and stacks the extra commas into the last field.
Is there any way I can use bulk copy or bulk insert to get to import these
files
Hi JPaine,
If all the double quote values are separated by commas and double quote is
not required in the data field, why don't you find and replace double quotes
in the csv itself? Once you remove the double quotes, you can use BCP to
import the data with commas as the separator.
Yogish
|||this is the limitation of bcp. use dts instead.

BCP in csv with qouted and unqouted fields

I need to import several csv files.
each field is comma seperated but some fields use double qoutes as a text
qualifier.
The fields that have double qoutes contain values seperated by commas.
ie. john, doe, "1,5,6",1,3
in the command line I use -c -t , -r \n
when it imports into the table it splits up the qouted columens into
multiple fields and stacks the extra commas into the last field.
Is there any way I can use bulk copy or bulk insert to get to import these
filesHi JPaine,
If all the double quote values are separated by commas and double quote is
not required in the data field, why don't you find and replace double quotes
in the csv itself? Once you remove the double quotes, you can use BCP to
import the data with commas as the separator.
--
Yogish|||this is the limitation of bcp. use dts instead.

BCP in csv with qouted and unqouted fields

I need to import several csv files.
each field is comma seperated but some fields use double qoutes as a text
qualifier.
The fields that have double qoutes contain values seperated by commas.
ie. john, doe, "1,5,6",1,3
in the command line I use -c -t , -r \n
when it imports into the table it splits up the qouted columens into
multiple fields and stacks the extra commas into the last field.
Is there any way I can use bulk copy or bulk insert to get to import these
filesHi JPaine,
If all the double quote values are separated by commas and double quote is
not required in the data field, why don't you find and replace double quotes
in the csv itself? Once you remove the double quotes, you can use BCP to
import the data with commas as the separator.
Yogish|||this is the limitation of bcp. use dts instead.

bcp importing data from fewer fields to more table columns

Hello, please help me with this urgent problem,

I am trying to migrate data from one database to another using the bcp utility. Nowhere in the net I was able to find documentation on how to perform data importation when the destination table has more columns than the fields in my data file. I have a origin table which has 5 columns, and I need to migrate this data to a table that has 20 columns. How to do this? of course I need that the remaining 15 columns of the destination table be filled with NULL data.

From what I have seen, it seems that my xml format file must always have the same amount of <COLUMN> elements than the number of columns in the destination table, is this always so?. If it is, this is a real problem, since I lack 15 <COLUMN> elements in my format file.
I really don't know what can I do about this, pelase help!!

Thanks in advance,
Diego V.

Create a VIEW with the table.columns that match the data file, and BCP into the VIEW. (Be sure the other columns allow NULL values and/or have DEFAULT values.

BCP Format File

Hi,
1) How do we specify constant values in BCP Format File?
ex
FileABC -- CONSTANT 1044035814.
2) How do we map one Value to TWO column fields in BCP Format File.
ex -- Data file Value -- "123 ABCDE"
Filed1 -- 123
--
Filed 2.. I want the value to be "AB"
Field 3.. I want the value to be "CDE"
--
Regards
Govardhan MVIf data values are not present in the source file, you need to use DEFAULT
constraints on the columns so that the desired values are inserted instead
of NULL. BCP honors DEFAULT constraints unless overridden with the '-k'
parameter.
Hope this helps.
Dan Guzman
SQL Server MVP
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:F2C48A4F-4500-415F-B439-2D9E61D1518E@.microsoft.com...
> Hi,
> 1) How do we specify constant values in BCP Format File?
> ex
> FileABC -- CONSTANT 1044035814.
> 2) How do we map one Value to TWO column fields in BCP Format File.
> ex -- Data file Value -- "123 ABCDE"
> Filed1 -- 123
> --
> Filed 2.. I want the value to be "AB"
> Field 3.. I want the value to be "CDE"
> --
> Regards
> Govardhan MV
>|||"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> schrieb im
Newsbeitrag news:F2C48A4F-4500-415F-B439-2D9E61D1518E@.microsoft.com...
> Hi,
> 1) How do we specify constant values in BCP Format File?
> ex
> FileABC -- CONSTANT 1044035814.
> 2) How do we map one Value to TWO column fields in BCP Format File.
> ex -- Data file Value -- "123 ABCDE"
> Filed1 -- 123
> --
> Filed 2.. I want the value to be "AB"
> Field 3.. I want the value to be "CDE"
> --
bcp is designed to do fast bulk copy operations. AFAIK there is no such
feature. You might be able to do this with DTS. With that you can define
quite flexible data conversions.
Alternatively preprocess the file with some scripting language tool of
your choice and then do the import with bcp.
Cheers
robert|||Can i Know if i specify the Default constraints , does the performance get
affected ,
our application the performance is very important . we will load millions of
data
at a time. or we have use update statements.
"Dan Guzman" wrote:

> If data values are not present in the source file, you need to use DEFAULT
> constraints on the columns so that the desired values are inserted instead
> of NULL. BCP honors DEFAULT constraints unless overridden with the '-k'
> parameter.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:F2C48A4F-4500-415F-B439-2D9E61D1518E@.microsoft.com...
>
>|||I doubt you will experience a noticeable performance hit with the default
constraint.
Hope this helps.
Dan Guzman
SQL Server MVP
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:AE8BEA49-4EDF-410D-B8AD-2B212044A2E6@.microsoft.com...[vbcol=seagreen]
> Can i Know if i specify the Default constraints , does the performance get
> affected ,
> our application the performance is very important . we will load millions
> of
> data
> at a time. or we have use update statements.
> "Dan Guzman" wrote:
>

BCP Format File

Hi,
1) How do we specify constant values in BCP Format File?
ex
FileABC -- CONSTANT 1044035814.
2) How do we map one Value to TWO column fields in BCP Format File.
ex -- Data file Value -- "123 ABCDE"
Filed1 -- 123
--
Filed 2.. I want the value to be "AB"
Field 3.. I want the value to be "CDE"
--
Regards
Govardhan MVIf data values are not present in the source file, you need to use DEFAULT
constraints on the columns so that the desired values are inserted instead
of NULL. BCP honors DEFAULT constraints unless overridden with the '-k'
parameter.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:F2C48A4F-4500-415F-B439-2D9E61D1518E@.microsoft.com...
> Hi,
> 1) How do we specify constant values in BCP Format File?
> ex
> FileABC -- CONSTANT 1044035814.
> 2) How do we map one Value to TWO column fields in BCP Format File.
> ex -- Data file Value -- "123 ABCDE"
> Filed1 -- 123
> --
> Filed 2.. I want the value to be "AB"
> Field 3.. I want the value to be "CDE"
> --
> Regards
> Govardhan MV
>|||"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> schrieb im
Newsbeitrag news:F2C48A4F-4500-415F-B439-2D9E61D1518E@.microsoft.com...
> Hi,
> 1) How do we specify constant values in BCP Format File?
> ex
> FileABC -- CONSTANT 1044035814.
> 2) How do we map one Value to TWO column fields in BCP Format File.
> ex -- Data file Value -- "123 ABCDE"
> Filed1 -- 123
> --
> Filed 2.. I want the value to be "AB"
> Field 3.. I want the value to be "CDE"
> --
bcp is designed to do fast bulk copy operations. AFAIK there is no such
feature. You might be able to do this with DTS. With that you can define
quite flexible data conversions.
Alternatively preprocess the file with some scripting language tool of
your choice and then do the import with bcp.
Cheers
robert|||Can i Know if i specify the Default constraints , does the performance get
affected ,
our application the performance is very important . we will load millions of
data
at a time. or we have use update statements.
"Dan Guzman" wrote:
> If data values are not present in the source file, you need to use DEFAULT
> constraints on the columns so that the desired values are inserted instead
> of NULL. BCP honors DEFAULT constraints unless overridden with the '-k'
> parameter.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:F2C48A4F-4500-415F-B439-2D9E61D1518E@.microsoft.com...
> > Hi,
> >
> > 1) How do we specify constant values in BCP Format File?
> > ex
> > FileABC -- CONSTANT 1044035814.
> >
> > 2) How do we map one Value to TWO column fields in BCP Format File.
> > ex -- Data file Value -- "123 ABCDE"
> >
> > Filed1 -- 123
> > --
> > Filed 2.. I want the value to be "AB"
> > Field 3.. I want the value to be "CDE"
> > --
> >
> > Regards
> > Govardhan MV
> >
> >
>
>|||I doubt you will experience a noticeable performance hit with the default
constraint.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:AE8BEA49-4EDF-410D-B8AD-2B212044A2E6@.microsoft.com...
> Can i Know if i specify the Default constraints , does the performance get
> affected ,
> our application the performance is very important . we will load millions
> of
> data
> at a time. or we have use update statements.
> "Dan Guzman" wrote:
>> If data values are not present in the source file, you need to use
>> DEFAULT
>> constraints on the columns so that the desired values are inserted
>> instead
>> of NULL. BCP honors DEFAULT constraints unless overridden with the '-k'
>> parameter.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
>> news:F2C48A4F-4500-415F-B439-2D9E61D1518E@.microsoft.com...
>> > Hi,
>> >
>> > 1) How do we specify constant values in BCP Format File?
>> > ex
>> > FileABC -- CONSTANT 1044035814.
>> >
>> > 2) How do we map one Value to TWO column fields in BCP Format File.
>> > ex -- Data file Value -- "123 ABCDE"
>> >
>> > Filed1 -- 123
>> > --
>> > Filed 2.. I want the value to be "AB"
>> > Field 3.. I want the value to be "CDE"
>> > --
>> >
>> > Regards
>> > Govardhan MV
>> >
>> >
>>

BCP format file

Has anyone successfully used a BCP format file to import from a data
file with fewer fields than there are columns in the table you're
importing into? I've been reading the Books Online help and trying to
manipulate this format file for hours and am getting nowhere. BCP tells
me "Starting copy..." but then "0 rows copied" and for the life of me I
can't figure out why. Is there any additional documentation (or war
stories!) anywhere?Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> Has anyone successfully used a BCP format file to import from a data
> file with fewer fields than there are columns in the table you're
> importing into? I've been reading the Books Online help and trying to
> manipulate this format file for hours and am getting nowhere. BCP tells
> me "Starting copy..." but then "0 rows copied" and for the life of me I
> can't figure out why. Is there any additional documentation (or war
> stories!) anywhere?
The format file has a couple of columns that describes the fields in the
text file.
Column 1 - Field number in file, normally they run from 1 to the number
you have on line2 in the file.
Column 2 - Data type. Always SQLCHAR or SQLNCHAR for character data.
Column 3 - Prefix length. Only used with binary files.
Column 4 - Field length. Used when you have fixed-length fields.
Column 5 - Field terminator. Note that the row terminator is really
only the field terminator for the last field.
Column 6 - Column number in the table, starting on 1. 0 means "don't
import this field". Thus importing only some columns is not very
difficult.
Column 7 - Normally used for the column name, but BCP does not look at
this data. It's the column number that counts.
Column 8 - Collation, can be set to "", unless you want some conversion.
Important to understand is that BCP always reads the file as if it was
binary. BCP reads bytes for field 1, until it finds the end of that
column, by prefix length, fixed length or terminator. Then it goes on
to field2 etc, until it starts over. For BCP newline has no particular
significance, so if it gets out of sync for some reason, there is
way for it to resync.
If the above does not help you, please post:
o CREATE TABLE statement for your table.
o The format file.
o A sample data file, preferrably in attachment to avoid damage
in news transport.
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|||Thanks. I just imported some rows with a format file, but am having
difficulty importing a date field correctly from my data file, which is
four fields delimited by tabs:
FX 2006-05-04 10:10 1103SWLSO1 XYZ
In the table I'm importing into, these columns are all CHAR except the
second which is DATETIME.
In the .FMT file field #2 is defined as SQLDATETIME with a length of 16.
Yet in the table that second field gets read in as:
0639-03-09 702:16:41.537
Help would be much appreciated.
In article <Xns97BAF3EA29E15Yazorman@.127.0.0.1>, esquel@.sommarskog.se
says...
> Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> The format file has a couple of columns that describes the fields in the
> text file.
> Column 1 - Field number in file, normally they run from 1 to the number
> you have on line2 in the file.
> Column 2 - Data type. Always SQLCHAR or SQLNCHAR for character data.
> Column 3 - Prefix length. Only used with binary files.
> Column 4 - Field length. Used when you have fixed-length fields.
> Column 5 - Field terminator. Note that the row terminator is really
> only the field terminator for the last field.
> Column 6 - Column number in the table, starting on 1. 0 means "don't
> import this field". Thus importing only some columns is not very
> difficult.
> Column 7 - Normally used for the column name, but BCP does not look at
> this data. It's the column number that counts.
> Column 8 - Collation, can be set to "", unless you want some conversion.
> Important to understand is that BCP always reads the file as if it was
> binary. BCP reads bytes for field 1, until it finds the end of that
> column, by prefix length, fixed length or terminator. Then it goes on
> to field2 etc, until it starts over. For BCP newline has no particular
> significance, so if it gets out of sync for some reason, there is
> way for it to resync.
> If the above does not help you, please post:
> o CREATE TABLE statement for your table.
> o The format file.
> o A sample data file, preferrably in attachment to avoid damage
> in news transport.
>|||Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> Thanks. I just imported some rows with a format file, but am having
> difficulty importing a date field correctly from my data file, which is
> four fields delimited by tabs:
> FX 2006-05-04 10:10 1103SWLSO1 XYZ
> In the table I'm importing into, these columns are all CHAR except the
> second which is DATETIME.
> In the .FMT file field #2 is defined as SQLDATETIME with a length of 16.
> Yet in the table that second field gets read in as:
> 0639-03-09 702:16:41.537
I said this in my previous post:
I should have clarified: the data type in the *file*. Anything else
than SQLCHAR or SQLNCHAR means that you have a binary file.
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|||Rick,
If you have SQLDATETIME and length 16 in your format file, then
you have told bcp to expect binary data, not text. This is also suggested
by the fact that 702:16:41.537 is the time that could be represented by
misconverted character data. That specific time would be displayed if
a datetime held the 0x2D3531A5 as its last 4 bytes, and this is the
ASCII representation of '-51' More likely values to come from date
strings are similar. If the ascii representation of '2006-05-04' were read
into a datetime, the time portion would correspond to 701:58:33:483.
Or in short, as Erland said, change your format file to say SQLCHAR
(or SQLNCHAR, as appropriate) instead of SQLDATETIME.
Steve Kass
Drew University
Rick Charnes wrote:
>Thanks. I just imported some rows with a format file, but am having
>difficulty importing a date field correctly from my data file, which is
>four fields delimited by tabs:
>FX 2006-05-04 10:10 1103SWLSO1 XYZ
>In the table I'm importing into, these columns are all CHAR except the
>second which is DATETIME.
>In the .FMT file field #2 is defined as SQLDATETIME with a length of 16.
>Yet in the table that second field gets read in as:
>0639-03-09 702:16:41.537
>Help would be much appreciated.
>In article <Xns97BAF3EA29E15Yazorman@.127.0.0.1>, esquel@.sommarskog.se
>says...
>

BCP Format File

Hi,
1) How do we specify constant values in BCP Format File?
ex
FileABC -- CONSTANT 1044035814.
2) How do we map one Value to TWO column fields in BCP Format File.
ex -- Data file Value -- "123 ABCDE"
Filed1 -- 123
Filed 2.. I want the value to be "AB"
Field 3.. I want the value to be "CDE"
Regards
Govardhan MV
If data values are not present in the source file, you need to use DEFAULT
constraints on the columns so that the desired values are inserted instead
of NULL. BCP honors DEFAULT constraints unless overridden with the '-k'
parameter.
Hope this helps.
Dan Guzman
SQL Server MVP
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:F2C48A4F-4500-415F-B439-2D9E61D1518E@.microsoft.com...
> Hi,
> 1) How do we specify constant values in BCP Format File?
> ex
> FileABC -- CONSTANT 1044035814.
> 2) How do we map one Value to TWO column fields in BCP Format File.
> ex -- Data file Value -- "123 ABCDE"
> Filed1 -- 123
> --
> Filed 2.. I want the value to be "AB"
> Field 3.. I want the value to be "CDE"
> --
> Regards
> Govardhan MV
>
|||"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> schrieb im
Newsbeitrag news:F2C48A4F-4500-415F-B439-2D9E61D1518E@.microsoft.com...
> Hi,
> 1) How do we specify constant values in BCP Format File?
> ex
> FileABC -- CONSTANT 1044035814.
> 2) How do we map one Value to TWO column fields in BCP Format File.
> ex -- Data file Value -- "123 ABCDE"
> Filed1 -- 123
> --
> Filed 2.. I want the value to be "AB"
> Field 3.. I want the value to be "CDE"
> --
bcp is designed to do fast bulk copy operations. AFAIK there is no such
feature. You might be able to do this with DTS. With that you can define
quite flexible data conversions.
Alternatively preprocess the file with some scripting language tool of
your choice and then do the import with bcp.
Cheers
robert
|||Can i Know if i specify the Default constraints , does the performance get
affected ,
our application the performance is very important . we will load millions of
data
at a time. or we have use update statements.
"Dan Guzman" wrote:

> If data values are not present in the source file, you need to use DEFAULT
> constraints on the columns so that the desired values are inserted instead
> of NULL. BCP honors DEFAULT constraints unless overridden with the '-k'
> parameter.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:F2C48A4F-4500-415F-B439-2D9E61D1518E@.microsoft.com...
>
>
|||I doubt you will experience a noticeable performance hit with the default
constraint.
Hope this helps.
Dan Guzman
SQL Server MVP
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:AE8BEA49-4EDF-410D-B8AD-2B212044A2E6@.microsoft.com...[vbcol=seagreen]
> Can i Know if i specify the Default constraints , does the performance get
> affected ,
> our application the performance is very important . we will load millions
> of
> data
> at a time. or we have use update statements.
> "Dan Guzman" wrote:

BCP derived fields

thanx in advance....

Pls suggest an idea to import derived fields from the data file to SQLServer2005 using the BCP utility?

eg: in the data file there are

Location UserLocId
USA 1
USA 2
IND 1
IND 2

I have to import Unique usercode like
'USA_1'
'USA_2'
'IND_1'
'IND_2'

If there is any way to upload this using BCP
( updating the usercode after loading will take more time)As you have posted a question in the articles section it is being moved to SQL Server Forum.

MODERATOR.|||

Quote:

Originally Posted by dipu vp

thanx in advance....

Pls suggest an idea to import derived fields from the data file to SQLServer2005 using the BCP utility?

eg: in the data file there are

Location UserLocId
USA 1
USA 2
IND 1
IND 2

I have to import Unique usercode like
'USA_1'
'USA_2'
'IND_1'
'IND_2'

If there is any way to upload this using BCP
( updating the usercode after loading will take more time)

i have not tried this, but here's an idea:

i think you can BCP into an existing table. so your FMT (format file) could be different from that of the existing table structure. can you try to have an format file based on Location, UserLocId structure and have an existing table with an extra calculated filed called UserCode with default value rtrim(Location) + '_' + ltrim(UserLocId )

i don't know if it will work but worth a shot.

bcp and text qualifying

I would like to output to a csv file using bcp with the fields text qualified by double quotes. Like this:

"1","John Doe","100 main st"
"2","John Smith","101 main st"
"3","John Johnson","102 main st"

Right now here's what my bcp command looks like:
bcp "select id, name, address from People" QUERYOUT d:\data.csv -S aserver -T -t"," -R

And it returns results without quotes:
1,John Doe,100 main st
2,John Smith,101 main st
3,John Johnson,102 main st

How to I get the results in the file with quotes? Thanks for the help.I believe it is

/t"\",\""

The inner double quotes have to be escaped. Hope this helps.|||I tried that, which works for every field, except the 2 outside fields. The first field doesn't have a begining quote and the last field doesnt have an ending quote.

Thanks for the reply though. I appricate the help. Same goes to anyone else who has an idea!|||Alright, so now I tried using a format file, and it worked well, I was able to get double quotes around every field except the first one. So I did some research and supposedly you can add the line:
1 SQLCHAR 0 0 "\"" 1 first_quote ""
to your format file, and it will add a " to the beginning of each record.

Upon trying this I get the error: Error = [Microsoft][ODBC SQL Server Driver]Host-file columns may be skipped only when copying into the Server

Doing research on the error I came up with nothing. Any ideas?|||Has anyone created a csv file with text qualifiers using the bcp command?|||Originally posted by WhiZa
Has anyone created a csv file with text qualifiers using the bcp command?

Did you try to format each field in the select command ?
Maybe this is not the best way but is working.|||I guess I figured it out.

I my select statement I added a field like this SELECT '', * FROM...

After doing this I was able to add 1 SQLCHAR 0 0 "\"" 1 first_quote ""
to the format file, and I was able to add a " to the begining of each record. Acctualy it adds a space and a " to each record, but it works all the same.

I hope this helps someone out, it took my slow brain a while to figure it out :)

Bcp

I need to import a CSV file (strings are in quotations) into SQL server using BCP. Some of the fields contain quotations whereas some of them do not. When i try to import of file such as this an error regarding truncation appears and I am not able to import the data.
When I try to import the same file without quotations around the fields it imports correctly. If you have help regarding this it would be greatly appreciated.

"XXX","20040326080100",1040,"121732","100203223","0040227324003"

or

XXX,20040326080100,1040,121732,100203223,004022732 4003

Thanks
DrahosCan they be tab delimited?

The quotes are a pain|||No, actually I receive those delimited files with quoted strings from somewhere.|||Let's see your bcp command?

Did you try and DTS it in?|||I am using this command:

bcp "Received_Data.dbo.XXX" in "Received_CSV.csv" -ffmt_file.fmt -T

Data I am loading is huge and I was recomended to use bcp instead of DTS.
I have tried DTS and it could load the data . There is a choise for 'Text qualifier' - you can choose 'double quoted', so it is OK.

format file is attached

Thanks again
Drahos|||format file is attached|||Is there anyone who can help me with that?|||There's a workaround; tell bcp not to import specific columns. In your case, turn the quotes into columns. The example works for me and on the first column only:

8.0
8
1 SQLCHAR 0 0 "\"" 0 dummy SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 3 "\"," 1 Col001 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 0 "\"" 0 dummy SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 25 "," 2 Col002 SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 25 "," 3 Col003 SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 25 "," 4 Col004 SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 25 "," 5 Col005 SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 25 "\r\n" 6 Col006 SQL_Latin1_General_CP1_CI_AS|||Is there anyone who can help me with that?

either use PERL/PYTHON to srip the quotes or use DTS. DTS will not be much slower than BCP but DTS will br 100 times easier to use and maintain.

Batch update and trigger

Hi All,
I have a trigger on a table tracking changes to certain fields.
However, when I do a batch update on that field, it looks like the
trigger only gets fired once. However, I do want to track changes on
every record that the update statement touches. So, is there anyway to
make it work or is there any work around? Do I have to create a cursor
and update one record a time?
Thanks a lot,
blueyep, triggers in SQL Server are fired once per statement, not once per
row. when you describe how do you need to track changes, we might be
able to help|||what I want to do is very simple. I want to track the change to a field
in one table. if the value changes, I will insert a row in the change
log table. Basically if old field value <> new field value, insert a
row in the change log.
However, in another script, I sometimes update the field for many rows
if they meet the criteria. Therefore, I have something like UPDATE
table1 SET field1= 'Y' WHERE field2>field3. When this statement is
executed, the trigger only fired once and therefore, only one row gets
inserted into the changelog table.
I just wonder if there is anyway I can make the trigger fired for each
row without abandoning the batch update and use cursor to do update for
each individual row
Thanks a lot.|||if you don't modify the PK, that's easy:
create table seq(s_id int identity, i int)
insert into seq(i) values(1)
insert into seq(i) values(2)
insert into seq(i) values(3)
insert into seq(i) values(4)
go
create table seq_audit(s_id int, old_i int, new_i int)
go
create trigger seq_upd
on seq
for update
as
insert into seq_audit
select inserted.s_id, deleted.i, inserted.i from inserted, deleted
where inserted.s_id = deleted.s_id
go
select * from seq
s_id i
-- --
1 1
2 2
3 3
4 4
go
update seq set i=i+1 where s_id > 1
go
select * from seq_audit
s_id old_i new_i
-- -- --
2 2 3
3 3 4
4 4 5
go
drop table seq
drop table seq_audit

Batch insert 10000 rows at a time and commit

I want to Insert 10000 rows at a time and commit in sql server , Is
there a way to do it if the source tables have no id fields ?
What would be the most efficient method?

Thanks

AjayHi

Bulk Insert/BCP have batch size parameters which will do this, although the
expect imput from data files. If the source is a table then you could set
rowcount and loop (if you can differentiate the records (there may be
another natural key) or possibly use a cursor to break the sizes down. There
are issues with rowcount and inserting from remote tables (see BOL (Topic:
ROWCOUNT) for more information)

John

"Ajay Garg" <ajayz90@.hotmail.com> wrote in message
news:d9477327.0411200529.4cfc0386@.posting.google.c om...
>I want to Insert 10000 rows at a time and commit in sql server , Is
> there a way to do it if the source tables have no id fields ?
> What would be the most efficient method?
> Thanks
>
> Ajay

basic sql query needed

Ok, I'm a beginner so forgive me for my ignorance. Could someone help
me with this?

tbl_x has two fields xid and xlist
xlist being a list of numbers....1, 5, 6, 8

i want to create a new table from tbl_x that converts each number in
that xlist into a row in tbl_new

so for example
tbl_x has 3 rows
xid xlist
1 1, 4, 5
2 2, 3, 7
3 2, 1, 7

i need a query or sql script that will convert that table with a list
into the following...

tbl_new
id xid xlid
1 1 1
2 1 4
3 1 5
4 2 2
5 2 3
6 2 7
7 3 2
8 3 1
9 3 7is xlist always 7 char width? or will it have some thing like 123,1,333

rekcah@.freeshell.org (G Hopper) wrote in message news:<15b4c9ac.0407271246.5cd91a6b@.posting.google.com>...
> Ok, I'm a beginner so forgive me for my ignorance. Could someone help
> me with this?
> tbl_x has two fields xid and xlist
> xlist being a list of numbers....1, 5, 6, 8
> i want to create a new table from tbl_x that converts each number in
> that xlist into a row in tbl_new
> so for example
> tbl_x has 3 rows
> xid xlist
> 1 1, 4, 5
> 2 2, 3, 7
> 3 2, 1, 7
> i need a query or sql script that will convert that table with a list
> into the following...
> tbl_new
> id xid xlid
> 1 1 1
> 2 1 4
> 3 1 5
> 4 2 2
> 5 2 3
> 6 2 7
> 7 3 2
> 8 3 1
> 9 3 7|||rekcah@.freeshell.org (G Hopper) wrote in message news:<15b4c9ac.0407271246.5cd91a6b@.posting.google.com>...
> Ok, I'm a beginner so forgive me for my ignorance. Could someone help
> me with this?
> tbl_x has two fields xid and xlist
> xlist being a list of numbers....1, 5, 6, 8
> i want to create a new table from tbl_x that converts each number in
> that xlist into a row in tbl_new
> so for example
> tbl_x has 3 rows
> xid xlist
> 1 1, 4, 5
> 2 2, 3, 7
> 3 2, 1, 7
> i need a query or sql script that will convert that table with a list
> into the following...
> tbl_new
> id xid xlid
> 1 1 1
> 2 1 4
> 3 1 5
> 4 2 2
> 5 2 3
> 6 2 7
> 7 3 2
> 8 3 1
> 9 3 7

It's not pretty but, this code works. I normally avoid cursors, but
couldn't get the function to work as a subquery in the from clause.
It should get you started.

create table t_ids
(xid int, xlist varchar(25))
go

create table t_ids2
(xid int, xl int)
go

insert into t_ids
select 1 as xid, '1, 4, 5'
union select 2, '2, 3, 7'
union select 3, '2, 1, 7'

GO

CREATE FUNCTION f_split(@.id int, @.str varchar(255), @.sep char(1))
RETURNS @.vals table (id int, val varchar(25))
as
begin
declare @.start int,
@.found int
set @.start = 1
set @.found = 0
while (@.start > 0) begin
set @.found = charindex(@.sep, @.str, @.start)
if @.found = 0 begin
if @.start > 1
insert into @.vals values(@.id, ltrim(rtrim(substring(@.str, @.start,
len(@.str) - @.start + 1))))
break
end
else begin
insert into @.vals values(@.id, ltrim(rtrim(substring(@.str, @.start,
@.found - @.start))))
set @.start = @.found + 1
end
end

return
end
GO

DECLARE @.id as int, @.xl as varchar(25)

DECLARE c_xl CURSOR FAST_FORWARD
FOR SELECT xid, xlist FROM t_ids
open c_xl
FETCH c_xl into @.id, @.xl
WHILE @.@.Fetch_Status = 0 BEGIN
insert into t_ids2
select * from dbo.f_split(@.id, @.xl, ',')
FETCH c_xl into @.id, @.xl

END
CLOSE c_xl

select * from t_ids2