Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

bcp "0 rows copied"

Posted on 2006-07-13
7
Medium Priority
?
3,816 Views
Last Modified: 2008-02-20
i am trying to load a text file into a table.
no errors are reported but nothing loads.

the SQL engine is running on the same machine from
which i am trying to bcp.

--- START ---
C:\Temp> bcp trading.dbo.tradeEvents in C:\Temp\data.txt -f C:\Temp\bcp.fmt -T
Starting copy...

0 rows copied.
Network packet size (bytes): 4096
ClockTime (ms.) Total        : 1
C:\Temp>
--- END ---

this is a sample row in "data.txt":
--- START ---
IBM;2006-06-21 00:00:01.0000;77.98;3689400;0.0024;0.0028;0.0102;
--- END ---

this is "bcp.fmt":
--- START ---
9.0
7
1    SQLCHAR           2     50     ";"     1     symb               SQL_Latin1_General_CP1_CI_AS
2    SQLDATETIME    0     8       ";"     2     day                 ""
3    SQLMONEY4       0     4       ";"     3     adjCls             ""
4    SQLINT              0     4       ";"     4     volume            ""
5    SQLFLT8            0     8       ";"     5     intradayPPC     ""
6    SQLFLT8            0     8       ";"     6     dailyPPC          ""
7    SQLFLT8            0     8       ";"     7     nightlyPPC       ""
--- END --- END ---

i also tried "bcp .... -U dwilkins -P roppongi" instread of "bcp .... -T"
i think/know "dwilkins" has write permission on "tradeEvents".
it made no difference.
0
Comment
Question by:dwilkins123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17106675
What if you add

-e C:\Err.TXT


to that command line (to create an error file)


Then check if there is any more info in Err.TXT
0
 

Author Comment

by:dwilkins123
ID: 17107591
note the "-e C:\Temp\err.text" addition
--- START ---
C:\Temp> bcp trading.dbo.tradeEvents in C:\Temp\data.txt -f C:\Temp\bcp.fmt -T -e C:\Temp\err.txt
Starting copy...

0 rows copied.
Network packet size (bytes): 4096
ClockTime (ms.) Total        : 1
C:\Temp>
--- END ---

and C:\Temp\err.txt is empty.
0
 

Author Comment

by:dwilkins123
ID: 17112717
i really need to get this data loaded asap.

i've spend hours and messed around with "BULK INSERT"

the most common errors i get:
(1) "unexpected end of file"
(2) "the column is too long in the data file"

(3) i can correctly load data, however
it will only load the first row in "data.txt".
and all the columns must be typed as "text".


what are so things to look at to solve any one
of those problems?

of course, the ideal solution is to use "bcp".

thnx.
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17116887
Those error messages imply that there are not a fixed number of columns in the file.

I suggest as an initial troubleshooting step, that you try loading just the first line in.

Then keep adding ten lines till it stops working.

Then you can identify any lies with issues.



That sample line that you provided has an extra ; at the end Is that a typo? If not, your format file should look something like this:



9.0
7
1    SQLCHAR           2     50     ";"     1     symb               SQL_Latin1_General_CP1_CI_AS
2    SQLDATETIME    0     8       ";"     2     day                 ""
3    SQLMONEY4       0     4       ";"     3     adjCls             ""
4    SQLINT              0     4       ";"     4     volume            ""
5    SQLFLT8            0     8       ";"     5     intradayPPC     ""
6    SQLFLT8            0     8       ";"     6     dailyPPC          ""
7    SQLFLT8            0     8       ";\r\n"     7     nightlyPPC       ""



Note that field sevens terminator is a ; followed by carriage return/linefeed in this example.

0
 

Author Comment

by:dwilkins123
ID: 17117833
";\r\n\" as the final column terminator produces the same result of:
---> no columns inserted; no errors <---

i experimented with how i can intentionally generate errors.
all of these actions generate errors:

(1) deleting a column out of "bcp.fmt" fails, as well as changing their type
generates errors:
bcp knows about the column structure of the table. so, this proves that if
there was a table/"bcp.fmt" formatting error, i'd get an error.

is there an ENV variable i need set in the shell?

the version of SQL Server i use is: [SQL-Server 2005 [Developer Edition] v. 9.00]
0
 

Author Comment

by:dwilkins123
ID: 17118193
I DID IT!!! YES!!! IT WORKS :::: case closed

stating with this simplest file format and working
fwd was the beginning of finding the solution.

the trick is.....
when bcp-ing a text fmt file,  you gotta enter, no matter the true format:

file storage type [int]: ---> char
prefix length [2]: ---> 0
length of field [4]: ---> 50
field terminator [none]: ---> ;

bcp wants to view everything as a fixed-width CHAR with a delimitor.
even if its really an INT, MONEY, DATETIME, FLOAT ........

(1) "type" the columns as you really want then in Serv. Mngr.
(2) load everything as [CHAR(50)] in bcp.

and i think why? what is the meaning? this makes no sense.
in MySQL, i loaded my tables in 5-minutes.
as i learn more, SQL Server better be worth this insanity.
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 600 total points
ID: 17118568
Yes I've always found format files very painful.

Usually you can get away without them though. Using BULK INSERT, this should also have worked (if the number of fields in destination table match the number of fields in the source):



BULK INSERT YourTable
FROM 'YourFile.TXT'
WITH (
FIELDTERMINATOR = ';'
)
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question