Loading data from txt file to database tbl

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Loading data from txt file to database tbl

Author

Topic

ramecepa
Starting Member

43 Posts

Posted - 2010-11-02 : 04:54:58

Dear All,
When i try to load data from txt file to database tbl,
some records are missing
i have 6.5Million records in txt file..around 20k to 40k records are missed

Thanks,
Pathipati

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-02 : 20:30:27

maybe extranneous line breaks in the text file?

ramecepa
Starting Member

43 Posts

Posted - 2010-11-02 : 21:05:53

Can u explain clearly??

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-02 : 21:16:17

using bcp? DTS? BULK INSERT? SSIS? INSERTS? Magic?

File Format? Fixed width, delimited? Native?

Show us the code for the "load"

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

ramecepa
Starting Member

43 Posts

Posted - 2010-11-02 : 21:35:14

Thanks for response..
I am using DTS with fixedwidth length text file
i didn't write any code to loading data
i just pump from txt file to table

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-02 : 21:39:43

check the text file for line breaks where they don't belong.

also, for extranneous column delimiters

ramecepa
Starting Member

43 Posts

Posted - 2010-11-02 : 21:58:17

I checked both,both are correct

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-02 : 22:07:08

so if it's fixed width, that means you are using a format file...correct?

My only other suggestion is to load it to a 1 column table, and then perform audits

Have you ever used bcp?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

ramecepa
Starting Member

43 Posts

Posted - 2010-11-02 : 22:37:21

I didn't use bcp before,
i have taken as 1 column,after taken as a 1 column also getting same problem

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-02 : 22:48:59

wait

did you create a table

CREATE TABLE myTable99(Col1 varchar(8000))
GO

BULK INSERT ....

what is the source data file? Just a text file?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

ramecepa
Starting Member

43 Posts

Posted - 2010-11-02 : 23:04:16

just txt file only

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources