GeoNames Home | Postal Codes | Download / Webservice | About 

GeoNames Forum
  [Search] Search   [Recent Topics] Recent Topics   [Groups] Back to home page 
[Register] Register / 
[Login] Login 
Bulk Insert into MS SQL  XML
Forum Index -> General
Author Message
andrew



Joined: 16/06/2006 07:04:14
Messages: 3
Location: Alberta, Canada
Offline

Hi,

I'm running this in Query Analyser:

BULK INSERT geoname FROM 'c:\mystuff\downloads\GB.txt'

and I get the following error:


Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 14. Make sure the field terminator and row terminator are specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].
The statement has been terminated. 


I havn't specified FIELDTERMINATOR as the default is "tab delimited" and the default for ROWTERMINATOR is "newline"

I havn't used Bulk Insert before and I found this web page useful:
http://www.informit.com/library/content.asp?b=STY_Sql_Server_7&seqNum=109
on Bulk Inserts.

Any suggestions on this?

thanks, Andrew
marc



Joined: 08/12/2005 07:39:47
Messages: 4406
Offline

Hi Andrew

Did you try specifying the row terminator explicitly? On windows 'newline' often means '\n\r' wheras the dump files are using '\n'.

Marc

[WWW]
andrew



Joined: 16/06/2006 07:04:14
Messages: 3
Location: Alberta, Canada
Offline

Hi marc,

Seems to be a problem with "FIELDTERMINATOR", if I change it from tab deliminated to (a pipe) "|" in the text file it works....However the allCounties.txt file seems to be too big to open in "Word" to do a Search & Replace from tab to pipe plus I'd really like to be able to insert a tab delimited file.

tab delimited is the default setting for Bulk Insert ('\t'). I looked at the Bulk Insert specs at MSDN http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/sql/ts_ba-bz_4fec.htm
but still no success.

Andrew.
andrew



Joined: 16/06/2006 07:04:14
Messages: 3
Location: Alberta, Canada
Offline


Holy Smokes it worked! I copied and pasted the row terminator as displayed in "Word" (open a 'small' country file in Word to see):

BULK INSERT geonameNew FROM 'c:\mystuff\downloads\allCountries.txt' WITH (FIELDTERMINATOR='\t',ROWTERMINATOR='
' )


as you can see it is 'wrapped'.....anyway it took 1 min 56 secs to insert 6,241,128 rows.

It probably doesn't need the FIELDTerminator='\t' but I havn't tried it without.

Andrew.
marc



Joined: 08/12/2005 07:39:47
Messages: 4406
Offline

Hi Andrew

I am glad it worked. Thanks for sharing the solution with us, other MS SQL users will have it easier now and find the solution here.

Marc

[WWW]
bbqfrito



Joined: 27/08/2008 22:41:30
Messages: 5
Offline

andrew wrote:

Holy Smokes it worked! I copied and pasted the row terminator as displayed in "Word" (open a 'small' country file in Word to see):

BULK INSERT geonameNew FROM 'c:\mystuff\downloads\allCountries.txt' WITH (FIELDTERMINATOR='\t',ROWTERMINATOR='
' )


as you can see it is 'wrapped'.....anyway it took 1 min 56 secs to insert 6,241,128 rows.

It probably doesn't need the FIELDTerminator='\t' but I havn't tried it without.

Andrew. 


Alternatively you can use dynamic SQL:

DECLARE @Sql VARCHAR(MAX) =
'
BULK INSERT AllCountries
FROM ''F:\geonamesdata\allCountries\allCountries.txt''
WITH
(
ROWTERMINATOR = ''' + CHAR(10) + '''
)
'

EXEC(@Sql)

darman16



Joined: 07/10/2008 15:21:28
Messages: 1
Offline

Hi,

I have a csv file:
'|Minor'|Assigned'|'|123456789'|

what FIELDTERMINATOR should i use?

I tried FIELDTERMINATOR = ''|'
but I get errors.
I also tried
FIELDTERMINATOR = '|' but it leaves the ' in the fields.

thanks
ashish



Joined: 15/11/2010 06:44:10
Messages: 1
Offline

You can change list separator from regional settings and create CSV file. This way you can resolve issue while having ',' field inside field value like Mike,”456 2nd St, Apt 5".

More information is available on http://tinyurl.com/2v4ndco

Thanks,
Ashish
 
Forum Index -> General
Go to:   
Powered by JForum 2.1.5 © JForum Team