| Author |
 Topic  |
|
robvolk
Most Valuable Yak
USA
15557 Posts |
 Posted - 06/09/2005 : 14:21:30
|
| 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. |
 |
|
|
Frank_Galvin
Starting Member
3 Posts |
Posted - 06/10/2005 : 09:51:54
|
| I'm going to give it a shot. Thanks for replying! |
|
|
|
jdjoy
Starting Member
USA
2 Posts |
Posted - 08/19/2005 : 11:32:15
|
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
|
|
|
|
jdjoy
Starting Member
USA
2 Posts |
Posted - 08/19/2005 : 11:40:48
|
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
|
|
|
|
debrucer
Starting Member
1 Posts |
Posted - 10/26/2005 : 16:09:13
|
>> "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.
|
|
|
|
jhawley
Starting Member
3 Posts |
Posted - 02/12/2007 : 17:55:05
|
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. |
|
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 02/12/2007 : 18:00:41
|
do you realize you just responded to a 2yr old thread? 
www.elsasoft.org |
Edited by - jezemine on 02/12/2007 18:01:12 |
|
|
|
jhawley
Starting Member
3 Posts |
Posted - 02/12/2007 : 18:01:52
|
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. |
|
|
|
jhawley
Starting Member
3 Posts |
Posted - 02/12/2007 : 18:03:03
|
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  |
|
|
|
robvolk
Most Valuable Yak
USA
15557 Posts |
Posted - 02/20/2007 : 07:45:25
|
| 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. |
|
|
|
nicksql2005
Starting Member
1 Posts |
Posted - 08/23/2007 : 12:39:25
|
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. |
|
|
|
pnh110
Starting Member
2 Posts |
Posted - 09/15/2008 : 12:19:44
|
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. |
|
|
|
manishkaushik
Starting Member
14 Posts |
Posted - 02/20/2009 : 02:18:37
|
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 |
|
|
|
manishkaushik
Starting Member
14 Posts |
Posted - 02/20/2009 : 02:20:49
|
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 |
|
|
|
chopo
Starting Member
Mexico
2 Posts |
Posted - 09/09/2009 : 12:25:43
|
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.
|
|
|
|
chopo
Starting Member
Mexico
2 Posts |
Posted - 09/09/2009 : 12:32:30
|
| Hi there, anybody knows how can I do the flat file "source file" an example of this? |
|
|
|
ashishccet
Starting Member
India
2 Posts |
Posted - 01/20/2010 : 08:15:32
|
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, |
|
|
|
manishkaushik
Starting Member
14 Posts |
Posted - 01/22/2010 : 02:15:43
|
Thanks you so much Ashish
Thanks
Manish
Thanks
Manish |
|
|
|
ashishccet
Starting Member
India
2 Posts |
Posted - 11/15/2010 : 01:05:35
|
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 |
|
|
|
bosxz
Starting Member
3 Posts |
Posted - 04/25/2011 : 04:13:23
|
| unspammed |
|
|
|
Topic |
|
|
|
Article: Using BULK INSERT to Load a Text File
