build sql script

Saturday, February 25, 2012

Bulk Insert - Problem with characters

Hi,
I am using bulk insert to load data from a # delimited text file into SQL
Server database table. There are some characters in the file like
"FLüSSIGDüNGER BAYER 20L" is getting converted to "FL├￡SSIGD├￡NGER B
AYER
20L". I need to load the data as it is in the file. I am using the following
code from a stored procedure.
SELECT @.SSQL='BULK INSERT ' +@.tblName+
' FROM ''' + @.in_file_name + '''' +
' WITH ' + '(' + 'FIELDTERMINATOR = ''' + @.ColumnTer + ''', ROWTERMINATOR =
''' + @.RowTer + ''', CODEPAGE = ''850'')'
EXECUTE(@.SSQL)
I am using collate SQL_Latin1_General_CP1_CI_AS for the column that will
hold the above value. Its nvarchar(255).
Could you please help me out in this. This is very urgent. Appreciate your
timely help.
Many Thanks.Add "DATAFILETYPE='widechar'" to your statement.
-oj
"SQL Novice" <SQL Novice@.discussions.microsoft.com> wrote in message
news:FB54343D-22C7-4441-9DC1-21CE9DEC5990@.microsoft.com...
> Hi,
> I am using bulk insert to load data from a # delimited text file into SQL
> Server database table. There are some characters in the file like
> "FLSSIGDNGER BAYER 20L" is getting converted to "FL?SSIGD?NGER BAYER
> 20L". I need to load the data as it is in the file. I am using the
> following
> code from a stored procedure.
> SELECT @.SSQL='BULK INSERT ' +@.tblName+
> ' FROM ''' + @.in_file_name + '''' +
> ' WITH ' + '(' + 'FIELDTERMINATOR = ''' + @.ColumnTer + ''', ROWTERMINATOR
> =
> ''' + @.RowTer + ''', CODEPAGE = ''850'')'
> EXECUTE(@.SSQL)
> I am using collate SQL_Latin1_General_CP1_CI_AS for the column that will
> hold the above value. Its nvarchar(255).
> Could you please help me out in this. This is very urgent. Appreciate your
> timely help.
> Many Thanks.|||I have tried "DATAFILETYPE='widechar'" but it doesn't help.
"oj" wrote:

> Add "DATAFILETYPE='widechar'" to your statement.
> --
> -oj
>
> "SQL Novice" <SQL Novice@.discussions.microsoft.com> wrote in message
> news:FB54343D-22C7-4441-9DC1-21CE9DEC5990@.microsoft.com...
>
>|||This quick example is working fine here. I will need more info/data in order
to help.
use Northwind
go
create table test(i int, j nvarchar(255) collate
SQL_Latin1_General_CP1_CI_AS)
go
insert test select 1,N'FLSSIGDNGER BAYER 20L'
go
declare @.sql nvarchar(1000)
set @.sql= N'bcp "Northwind..test" out "c:\test.out" -S"'+@.@.servername
+'" -T -w -t"|"'
print @.sql
exec master..xp_cmdshell @.sql
truncate table test
set @.sql='bulk insert test
from ''c:\test.out''
with(fieldterminator=''|'',datafiletype=
''widechar'')'
print @.sql
exec(@.sql)
select * from test
go
drop table test
go
-oj
"SQL Novice" <SQL Novice@.discussions.microsoft.com> wrote in message
news:E327200F-D93E-4289-9767-8B800B5BB1A8@.microsoft.com...
>I have tried "DATAFILETYPE='widechar'" but it doesn't help.
> "oj" wrote:
>|||The file is created by a third-party program, which among other columns
contains the description column with these characters. We are reading from
the file (as received) using bulk insert. Here is a sample record. Could
there be a problem with how the text file has been created. following is one
of the records in the file.
CH#CH030#0030#5105949253#0001#20050113#0
00000000000020379#FLüSSIGDüNGER
BAYER 20L
CHF#0020379#0301#BI-WDI- CH#BI_LOAD##0000309132#DE#AGLUKON#Düsse
ldorf
11#40549##Postfach 190 343#0000309132#DE#AGLUKON#Düsseldorf
11#40549##Postfach 190 343# 3840#L# 0000.00#EUR# 0000.04#CHF#627#S.
DERUNGS#20050401#050040# 000.00# 000#CHF
"oj" wrote:

> This quick example is working fine here. I will need more info/data in ord
er
> to help.
> use Northwind
> go
> create table test(i int, j nvarchar(255) collate
> SQL_Latin1_General_CP1_CI_AS)
> go
> insert test select 1,N'FLüSSIGDüNGER BAYER 20L'
> go
> declare @.sql nvarchar(1000)
> set @.sql= N'bcp "Northwind..test" out "c:\test.out" -S"'+@.@.servername
> +'" -T -w -t"|"'
> print @.sql
> exec master..xp_cmdshell @.sql
> truncate table test
> set @.sql='bulk insert test
> from ''c:\test.out''
> with(fieldterminator=''|'',datafiletype=
''widechar'')'
> print @.sql
> exec(@.sql)
> select * from test
> go
> drop table test
> go
> --
> -oj
>
> "SQL Novice" <SQL Novice@.discussions.microsoft.com> wrote in message
> news:E327200F-D93E-4289-9767-8B800B5BB1A8@.microsoft.com...
>
>|||Codepage = 65001 solves my problem.
"SQL Novice" wrote:

> Hi,
> I am using bulk insert to load data from a # delimited text file into SQL
> Server database table. There are some characters in the file like
> "FLüSSIGDüNGER BAYER 20L" is getting converted to "FL├￡SSIGD├￡NGER
BAYER
> 20L". I need to load the data as it is in the file. I am using the followi
ng
> code from a stored procedure.
> SELECT @.SSQL='BULK INSERT ' +@.tblName+
> ' FROM ''' + @.in_file_name + '''' +
> ' WITH ' + '(' + 'FIELDTERMINATOR = ''' + @.ColumnTer + ''', ROWTERMINATOR
=
> ''' + @.RowTer + ''', CODEPAGE = ''850'')'
> EXECUTE(@.SSQL)
> I am using collate SQL_Latin1_General_CP1_CI_AS for the column that will
> hold the above value. Its nvarchar(255).
> Could you please help me out in this. This is very urgent. Appreciate your
> timely help.
> Many Thanks.|||Thanks for the update.
-oj
"SQL Novice" <SQLNovice@.discussions.microsoft.com> wrote in message
news:7C34DF98-97A9-46B5-B7A3-208CB5B1347B@.microsoft.com...
> Codepage = 65001 solves my problem.
> "SQL Novice" wrote:
>

BULK INSERT - MISSING ROWS

Hello

Iam using:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)

I am using BULK INSERT to import some pipe-delimited flat files into a database.
I am firstly converting the file using VB.NET, to ensure each line of the file has a carriage return (by using streamwriter.writeline), and I am also ensuring there is no blank line at the end of the file (by using streamwriter.write).
Once I have done this, my BULK INSERT command appears to work OK. This is how I am using the statement:
BULK INSERT
tempHISTORY
FROM 'C:\TEMPHISTORY.TXT'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\r'
)

NB: The first row in the file is a header row.

This appears to work OK, however, I have found that certain files seem to miss the final line of the file! I have analysed these files incase they have an inconsistant number of columns but they don't.
I have also found that if I knock off the last column of the tempHISTORY table, the correct number of rows are imported. However, of course, I can't just discard one of the columns from the file, I need to import the entire file.
I cannot understand why BULK INSERT is choosing to miss the final line in the file, when the schema of the destination table matches the structure of the file.

I have just made a very odd discovery regarding this data:

The final value of the final column of the file does not have a value (blank).

eg
0|0|0|0|0|0|0|1 <- PENALTIMATE LINE
0|0|0|0|0|0|0| <- FINAL LINE

If I give the final line's final column a value, it will import. If the value of that final column in the final line is blank, it discards the line, despite the fact that the destination column allows nulls!

Please help!

|||I have found that if a place a blank space on the end of the last line of each file, the import will always work.

This is not an ideal solution but it works.

BULK INSERT - Inserting txt file with columns in different order than define in table

How can I use bulk insert to insert a text file where the columns in the text file is in different order than the columns in the table?

I have a ZIP table with Zip_Code, Zip_City, Zip_State and the text file has the fields in Zip_City, Zip_State, Zip_Code. The instructions were to keep the order as defined in the Entity Definition which would be the first order.

My code for the bulk insert is usually

Code: ( text )

BULK INSERT DB2914.dbo.[ZIP] FROM 'C:\Documents and Settings\Jthep\My Documents\SQL Server Management Studio\Projects\S2914-HW3\ZIP_data.txt'WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')
Is there a way to format the columns so I can actually set any column in the text file to any column in the table using Bulk Insert?You can insert into a temp table first and map as you wish...

Good Luck.|||Thanks, I figured I couldnt do it using bulk insert unless I created a temp. However, I'm now allowed to create the table with attributes of the same order as the data in the text file as many of my classmates were asking the professor about it. =D

BULK INSERT - insert sequence

Hello,

I am importing some tab-delimited text files into a Table. I would like to import the rows in the same sequence as displayed in the text file. BULK INSERT will do this sometimes and not other times.

Here are my test commands:

Code Snippet

deletefrom SampleReport

BULKINSERT SampleReport FROM'C:\Sample.tab'

Select*from SampleReport

As I run these commands over-and-over I notice that the insert is the same as the source file on an alternating basis.

Is this normal? Is there a way to have it Insert in the same sequence every time?

Thanks

Tom

Your query doesn't use any order by cluase. So you can't ensure wheater it is ordered properly are not.

The order sequence may change when the clustered indexed used in your table.

To ensure the order you can use the Identity column.

Here the sample,

Code Snippet

Create table #Data

(

Id int primary Key,

OrderSeq Int Identity(1,1)

)

Insert Into #Data Values (3)

Insert Into #Data Values (1)

Insert Into #Data Values (2)

Insert Into #Data Values (5)

Insert Into #Data Values (4)

--In Your Case here it is BULK INSERT

Select* from #Data Order By OrderSeq --Your Expected Order

Select* from #Data --Unexpected Order, using the Clustered Index

Drop Table #Data

|||

A table, by definition, is an unordered set of rows. There is no such thing as inserting and storing the rows in any given order. The only way you can get rows back in the order you desire is by using an ORDER BY clause on your select statement.

It may seem at times that you "always" get rows back in order of a clustered index when you do a select statement without an ORDER BY clause. however, it is never guaranteed so you should not depend on it.

|||

Thank you for taking time to answer. Too bad we cannot just add rows and get them back in the same order without adding a key.

|||

If you have a clustered index on your destination table and it's the same ordering as the input data, then you can add the ORDER hint to BULK INSERT to possibly improve performance on the load.

FROM BOL:

ORDER ( { column [ ASC | DESC ] } [ ,... n ] )

Specifies how the data in the data file is sorted. Bulk load operation performance is improved if the data loaded is sorted according to the clustered index on the table. If the data file is sorted in a different order, or there is no clustered index on the table, the ORDER clause is ignored. The column names supplied must be valid columns in the destination table. By default, the bulk insert operation assumes the data file is unordered.

Bulk Insert - Fixed Length File

I'm trying to do an insert using Bulk Insert with a fixed length file.
I'm using a format file.
I'm getting the following error message:
Cannot perform bulk insert. Invalid collation name for source column 16
in format file '\\wbhq.com\dfsdv\iDataInt\GOPFiles\GOPFormatFile. txt'.

Any suggestions are appreciated.

Thanks!
Jennifer

Format File Contents:
8.0
16
1SQLCHAR02""0Space""
2SQLCHAR04""1YearID""
3SQLCHAR02""0Space""
4SQLCHAR02""2PeriodID""
5SQLCHAR02""3CompanyID""
6SQLCHAR01""0Dash""
7SQLCHAR04""0Space""
8SQLCHAR01""0Dash""
9SQLCHAR04""4UnitID""
10SQLCHAR01""0Dash""
11SQLCHAR04""5AccountCode""
12SQLCHAR05""0Space""
13SQLCHAR01""6AccountType""
14SQLCHAR029""0Space""
15SQLCHAR016""7GLAmount""
16SQLCHAR0105"\r\n"0Space""

Bulk Insert Statement:
BULK INSERT FlatFile_GOP
FROM '\\wbhq.com\dfsdv\iDataInt\GOPFiles\GLPAM.GOP'
WITH
(
FORMATFILE =
'\\wbhq.com\dfsdv\iDataInt\GOPFiles\GOPFormatFile. txt'
)

Table Definition:
CREATE TABLE [dbo].[FlatFile_GOP] (
[YearID] [smallint] NOT NULL ,
[PeriodID] [smallint] NOT NULL ,
[CompanyID] [smallint] NOT NULL ,
[UnitID] [smallint] NOT NULL ,
[AccountCode] [int] NOT NULL ,
[AccountType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[GLBalance] [money] NOT NULL
) ON [PRIMARY]
GO

File Contents:
2007 210- -0002-3000 G
196395.10

2007 210- -0002-3700 B
1484.00

2007 210- -0002-3700 G
1571.13

2007 210- -0002-3800 B
157457.00

2007 210- -0002-3800 G
161577.73Jennifer (J.Evans.1970@.gmail.com) writes:

Quote:

Originally Posted by

I was not able to repeat this. Then again, I was not able to import the
file successfully either, but the error message I got was:

Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 1, column 15
(GLBalance).
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 2, column 15
(GLBalance).

which makes me think that the format file was interpreted OK. (And the
reason for the error is simply that your file was mashed in news transport,
and I did not really make any effort to resurrect it.)

Since column 16 is a column you are not even importing the message seems
funny to me. My prime suspicions that the contents of

Quote:

Originally Posted by

'\\wbhq.com\dfsdv\iDataInt\GOPFiles\GOPFormatFile. txt'

is not what you think it is.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

BULK INSERT - FIELDTERMINATOR

Hi, I need to bulk insert a .csv file into a database table. However, the problem I am encounter now is the in one of my field which is nvarchar(50) ... the data in .csv has ',' - comma ... which my FIELDTERMINATOR in BULK INSERT is also comma. It gives me error because the next field is an integer field. How do I solve this problem? I hereby give few data example as follow:

In .csv file:

============

"J2825JA","FEB22,MAR1,8/05 - RE","22FEB05",45,20000

"J2825JB","FEB22,MAR1,8/05 - RE","22FEB06",765,435653

Query

=========

SQL = "BULK INSERT [" & sTableName & "]" & _
" FROM '" & sNewFile & "'" & _
" WITH ( FIELDTERMINATOR = ',', " & _
" FIRSTROW = 1, " & _
" ROWTERMINATOR = '\n' ) "

BTW, this is written in ASP. But I this is DB problem. So I posted here. Thanks in advance.

Personally I never use comma-delimited files because of this sort of thing; there is nothing wrong with the format per se, but it seems like many software programs do not know how to handle it correctly. Is there any way you can have the file supplied in tab-delimited format? SQL Server will handle this for you a lot more gracefully. It will also fixed-width format readily.

If this is not a possibility, Access should be able to import the file as-is. FoxPro will also handle this format. And then you can export it in a SQL Server-friendly format.

BULK INSERT - escape " - .fmt

Hi All,
Simple table:
TABLE:
create table employee (
name char(20),
title char(120)
)
DATAFILE(.csv):
name;title
Gustavo;"Marketing Assistant"
Catherine;"Engineering Manager"
I'd like use BULK INSERT command to insert this data. Tell me please,
how should look formatfile (.fmt) for this example (SQL 2005). I need
data after BULK INSERT looks like this:
name title
-- --
Gustavo Marketing Assistant
Catherine Engineering Manager
NOT like this:
name title
-- --
Gustavo "Marketing Assistant"
Catherine "Engineering Manager"
So, " should be escape.
Thank you
--
RegardsHello,
I've done this, it's almost finished. You can tray this:
/
***************************************************************************************************/
--table
CREATE TABLE [dbo].[ImportTest](
[fname] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[lname] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[organization] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[address] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[zip] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[city] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[state] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[email] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
--data file (ImportTest.csv), REMEMBER at the end you HAVE TO add
ENTER
fname;lname;organization;address;zip;city;state;email;
Shirley;Birosik;;;90013;"Los Angeles";"CA";"sbirosik@.gmail.com";
Dennis;Eschen;;;94920;"Alhambra";"CA";"de@.hotmail.com";
Valerie;Chambers;;;91803;"Long Beach";"CA";"vweber@.verizon.net";
--fmt file(ImportTest.Fmt), add ENTER at the end
9.0
8
1 SQLCHAR 0 510 ";" 1
fname Latin1_General_CI_AS
2 SQLCHAR 0 510 ";" 2
lname Latin1_General_CI_AS
3 SQLCHAR 0 510 ";" 3
organization Latin1_General_CI_AS
4 SQLCHAR 0 510 ";" 4
address Latin1_General_CI_AS
5 SQLCHAR 0 510 ";\"" 5
zip Latin1_General_CI_AS
6 SQLCHAR 0 510 "\";\"" 6
city Latin1_General_CI_AS
7 SQLCHAR 0 510 "\";\"" 7
state Latin1_General_CI_AS
8 SQLCHAR 0 510 "\r\n" 8
email Latin1_General_CI_AS
bulk insert ImportTest
from 'd:\bulk\ImportTest.csv'
with (
formatfile = 'd:\bulk\ImportTest.Fmt',
firstrow = 2
)
--(2 row(s) affected)
select*
from ImportTest
Dennis Eschen NULL NULL 94920 Alhambra CA
de@.hotmail.com";
Valerie Chambers NULL NULL 91803 Long Beach CA
vweber@.verizon.net";
/
***************************************************************************************************/
For me now most important is why didn't insert first row (data row,
not column definition)?
When I added quotation mark to column definition works fine, but I
can't modify .csv file, I received it from third part company :( is it
possible add all rows without modification .csv file?
Second, how remove quotation mark and semicolon from last column?
Regards,
anxcomp