SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Using BULK INSERT to Load a Text File
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 4

robvolk
Most Valuable Yak

USA
15663 Posts

Posted - 06/09/2005 :  14:21:30  Show Profile  Visit robvolk's Homepage  Reply with Quote
If the Oracle system is running on Unix, then your file has a Unix line feed (LF) instead of a DOS line feed (CR + LF). For BULK INSERT, specify WITH (ROWTERMINATOR='\r') or WITH (ROWTERMINATOR=CHAR(10)) and see if that fixes it.
Go to Top of Page

Frank_Galvin
Starting Member

3 Posts

Posted - 06/10/2005 :  09:51:54  Show Profile  Reply with Quote
I'm going to give it a shot. Thanks for replying!
Go to Top of Page

jdjoy
Starting Member

USA
2 Posts

Posted - 08/19/2005 :  11:32:15  Show Profile  Reply with Quote
quote:
Originally posted by ppynrde

Bulk insert

What is the best way of importing using the Bulk insert command if there is a comma between the text delimiters

eg. if one line is like:

"Edwards, Rich",1978,22

This is really bugging me so any help would be appreciated

Go to Top of Page

jdjoy
Starting Member

USA
2 Posts

Posted - 08/19/2005 :  11:40:48  Show Profile  Reply with Quote
The only way I have discovered is to use a format file. You can dictate the delimiter following each field as follows:
text field followed by text field: "\",\"" (quote-comma-quote)
text field followed by non-text field: "\"," (quote-comma)
non-text field followed by text field: ",\"" (comma-quote)
non-text followed by non-text field: "," (comma only)
I've encountered two issues with this method: 1) If the first field is text, it will load with a leading quote (") and you will have to strip it afterward. 2) If there is a problem with some of the data, I don't get the detailed (record/field list) error messages as when bulk inserting without a format file. This can be an issue if your file is prone to contain bad data, harder to trouble-shoot. So, if anyone knows how to get the record/field detail on errors when using a format file, don't be shy, let me know.

quote:
Originally posted by ppynrde

Bulk insert

What is the best way of importing using the Bulk insert command if there is a comma between the text delimiters

eg. if one line is like:

"Edwards, Rich",1978,22

This is really bugging me so any help would be appreciated

Go to Top of Page

debrucer
Starting Member

1 Posts

Posted - 10/26/2005 :  16:09:13  Show Profile  Reply with Quote

>> "I could have used DELETE to accomplish this, but TRUNCATE TABLE has less of an impact on the transaction log."

correct, as in it is not a logged transaction, therefore, invalidates your backup(s). hopefully by now you have rethought the use of truncate in a production environment... as others who read this advice should also.
Go to Top of Page

jhawley
Starting Member

3 Posts

Posted - 02/12/2007 :  17:55:05  Show Profile  Reply with Quote
quote:
Originally posted by debrucer


>> "I could have used DELETE to accomplish this, but TRUNCATE TABLE has less of an impact on the transaction log."

correct, as in it is not a logged transaction, therefore, invalidates your backup(s). hopefully by now you have rethought the use of truncate in a production environment... as others who read this advice should also.




Actually if you look, he is truncating temporary data. It would not be backed up anyways.
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 02/12/2007 :  18:00:41  Show Profile  Visit jezemine's Homepage  Reply with Quote
do you realize you just responded to a 2yr old thread?


www.elsasoft.org

Edited by - jezemine on 02/12/2007 18:01:12
Go to Top of Page

jhawley
Starting Member

3 Posts

Posted - 02/12/2007 :  18:01:52  Show Profile  Reply with Quote
I have an interesting situation...

I am given a text file(raw export from a progress database) every day that occasionally has some data in it that will not cast properly when moving from the temp table to the live table. So i get an error like "Error converting varchar to numeric". Even though in progress it says the field is of type decimal, it will give me something like "61:5;6>1".

Is there any way to make insert ignore rows that do not work?

I was able to verify that the bad rows do not contain valid data. It is related to the poorly programmed application that runs on progress.
I know this breaks ACID rules to a certain extent, but it does not seem unreasonable in this situation.
Go to Top of Page

jhawley
Starting Member

3 Posts

Posted - 02/12/2007 :  18:03:03  Show Profile  Reply with Quote
quote:
Originally posted by jezemine

do you realize you just responded to a 2yr old thread?


www.elsasoft.org



yep. and clearly people still look at it
Go to Top of Page

robvolk
Most Valuable Yak

USA
15663 Posts

Posted - 02/20/2007 :  07:45:25  Show Profile  Visit robvolk's Homepage  Reply with Quote
BULK INSERT has a MAXERRORS keyword, make sure to set that to a value greater than the number of bum rows in your text file. If you're using bcp then it's the -m parameter.
Go to Top of Page

nicksql2005
Starting Member

1 Posts

Posted - 08/23/2007 :  12:39:25  Show Profile  Reply with Quote
this thread has been useful, especially with SQL2005. We don't like using the SSIS Packages and have opted for the BULK INSERT options from raw T-SQL. We, too, have MF extracts that we have absolutely no control over how they are created, and they are comma-delimited, double-quote text qualified. We had been using a scrub routine to cleanse the quotes out of the varchar columns, however this approach is much more graceful (ie easier to read, easier for our clients to work with - and MUCH easier to promote thorugh several enviroments (ie sandbox, text, UAT, production) than SSIS - at least our experience has been.
So - thank you for responding to this thread after 2 years...some things never go out of style.
Go to Top of Page

pnh110
Starting Member

2 Posts

Posted - 09/15/2008 :  12:19:44  Show Profile  Reply with Quote
Thank you for the codes. I really like the sample on the Stored-Procedure. It works really well on my Query Analyzer; however, I have to load my text file locally on the C:\ drive of my SQL server. Can you show me how to translate the following SQL command into VB codes? (EXEC ps_StudentList_Import 'C:\TxtFile2.txt',1,2)

I would like to call (or execute) the stored-proceude from my VB app. and my "text" file is located on the client, not the server. Thank you for your time.

Regards,
PNH.
Go to Top of Page

manishkaushik
Starting Member

14 Posts

Posted - 02/20/2009 :  02:18:37  Show Profile  Reply with Quote
1. I created a table as.....

Create table DemoBulk(Name varchar(10),Age numeric,dob datetime)

2. Content of my file bul.txt, which is stored in C:
'Manish',1,'2009-02-20 10:45:06.483'
'anish',2,'2009-02-20 10:45:06.483'
'nish',3,'2009-02-20 10:45:06.483'
'ish',4,'2009-02-20 10:45:06.483'

3. I tried

BULK INSERT DemoBulk FROM 'c:\bulk.txt'
WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )

4. I got this Error

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid
character for the specified codepage) for row 1, column 3 (dob).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid
character for the specified codepage) for row 2, column 3 (dob).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid
character for the specified codepage) for row 3, column 3 (dob).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid
character for the specified codepage) for row 4, column 3 (dob).





Please advice, where I am wrong.

Thanks
Manish
Go to Top of Page

manishkaushik
Starting Member

14 Posts

Posted - 02/20/2009 :  02:20:49  Show Profile  Reply with Quote
1. I created a table as.....

Create table DemoBulk(Name varchar(10),Age numeric,dob datetime)

2. Content of my file bulk.txt, which is stored in C:
'Manish',1,'2009-02-20 10:45:06.483'
'anish',2,'2009-02-20 10:45:06.483'
'nish',3,'2009-02-20 10:45:06.483'
'ish',4,'2009-02-20 10:45:06.483'

3. I tried

BULK INSERT DemoBulk FROM 'c:\bulk.txt'
WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )

4. I got this Error

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid
character for the specified codepage) for row 1, column 3 (dob).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid
character for the specified codepage) for row 2, column 3 (dob).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid
character for the specified codepage) for row 3, column 3 (dob).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid
character for the specified codepage) for row 4, column 3 (dob).





Please advice, where I am wrong.

Thanks
Manish

Thanks
Manish
Go to Top of Page

chopo
Starting Member

Mexico
2 Posts

Posted - 09/09/2009 :  12:25:43  Show Profile  Reply with Quote
quote:
Originally posted by cs

Timeout <P>Hi there.

I've tried the bulkcopy feature as you've written. It works perfectly !

The only problem is that when I try to bulkcopy more than 150000 rows (like 200000 rows (6 columns)) I get an timeout, like this:

Microsoft OLE DB Provider for ODBC Drivers error '80040e31'

[Microsoft][ODBC SQL Server Driver]Timeout expired

I use an asp script to active the stored procedure I've made. And I use an MS SQL 7.0 server.

Go to Top of Page

chopo
Starting Member

Mexico
2 Posts

Posted - 09/09/2009 :  12:32:30  Show Profile  Reply with Quote
Hi there, anybody knows how can I do the flat file "source file" an example of this?
Go to Top of Page

ashishccet
Starting Member

India
2 Posts

Posted - 01/20/2010 :  08:15:32  Show Profile  Reply with Quote
quote:
Originally posted by manishkaushik

1. I created a table as.....

Create table DemoBulk(Name varchar(10),Age numeric,dob datetime)

2. Content of my file bulk.txt, which is stored in C:
'Manish',1,'2009-02-20 10:45:06.483'
'anish',2,'2009-02-20 10:45:06.483'
'nish',3,'2009-02-20 10:45:06.483'
'ish',4,'2009-02-20 10:45:06.483'

3. I tried

BULK INSERT DemoBulk FROM 'c:\bulk.txt'
WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )

4. I got this Error

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid
character for the specified codepage) for row 1, column 3 (dob).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid
character for the specified codepage) for row 2, column 3 (dob).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid
character for the specified codepage) for row 3, column 3 (dob).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid
character for the specified codepage) for row 4, column 3 (dob).





Please advice, where I am wrong.

Thanks
Manish

Thanks
Manish





Just replace your data in following format

'Manish',1,20090220
'anish',2,20090220
'nish',3,20090220
'ish',4,20090220

Hope it helps,
Go to Top of Page

manishkaushik
Starting Member

14 Posts

Posted - 01/22/2010 :  02:15:43  Show Profile  Reply with Quote
Thanks you so much Ashish

Thanks
Manish



Thanks
Manish
Go to Top of Page

ashishccet
Starting Member

India
2 Posts

Posted - 11/15/2010 :  01:05:35  Show Profile  Reply with Quote
If you come across situation where you have ',' as field value you can try replacing list separator value from regional setting and specify that value inside bulk insert command.

More information @ http://tinyurl.com/2v4ndco

Thanks,
Ashish
Go to Top of Page

bosxz
Starting Member

3 Posts

Posted - 04/25/2011 :  04:13:23  Show Profile  Visit bosxz's Homepage  Reply with Quote
unspammed
Go to Top of Page
Page: of 4 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000