build sql script: urgent

Wednesday, March 7, 2012

Bulk insert and vb.net - URGENT

Hello.
I want to use bulk insert in order to import a huge file, lets say
"Source.txt" in my db, lets say "MyDB".
The data in "Source.txt" are numerical and date format. Values are seperated
with the tab character.
When I use :
BULK INSERT [owner].[dbname].[tablename] FROM
'c:\Source.txt' WITH (ROWTERMINATOR = '\n', FIELDTERMINATOR = '\t' ,
KEEPNULLS )
everything works fine.
Now, I want to add a datetime field in the beginning of each line in
source.txt.
I do this by :
<...open Source.txt as 1...>
<...open Altered.txt as 2...>
OldLine=LineInput(1)
NewLine="...DateVariable..." & vbTab &
OldLine
Print 2, OldLine & vbNewLine
<...close files...>
Why does
BULK INSERT [owner].[dbname].[tablename] FROM
'c:\Altered.txt' WITH (ROWTERMINATOR = '\n', FIELDTERMINATOR = '\t' ,
KEEPNULLS )
doesn't work?
The error message I get is
Bulk insert data conversion error (type mismatch) for row 1,
column 385 (...column description...).
I have tried with different row terminators but no success.
I suspect that while creating the Altered.txt, 2 row terminator characters
are added instead of 1.
Please any help will be much appreciated.
Thank you,
VasilisDid you also recreate the table with the datetime as the first column? File
fields are mapped to target table columns by ordinal position unless you
specify a format file.
Hope this helps.
Dan Guzman
SQL Server MVP
"Vasilis X" <v.hantziaras@.interwind.gr> wrote in message
news:du1d59$2pa3$1@.ulysses.noc.ntua.gr...
> Hello.
> I want to use bulk insert in order to import a huge file, lets say
> "Source.txt" in my db, lets say "MyDB".
> The data in "Source.txt" are numerical and date format. Values are
> seperated with the tab character.
> When I use :
> BULK INSERT [owner].[dbname].[tablename] FROM
> 'c:\Source.txt' WITH (ROWTERMINATOR = '\n', FIELDTERMINATOR = '\t' ,
> KEEPNULLS )
> everything works fine.
> Now, I want to add a datetime field in the beginning of each line in
> source.txt.
> I do this by :
> <...open Source.txt as 1...>
> <...open Altered.txt as 2...>
> OldLine=LineInput(1)
> NewLine="...DateVariable..." & vbTab & OldLine
> Print 2, OldLine & vbNewLine
> <...close files...>
>
> Why does
> BULK INSERT [owner].[dbname].[tablename] FROM
> 'c:\Altered.txt' WITH (ROWTERMINATOR = '\n', FIELDTERMINATOR = '\t' ,
> KEEPNULLS )
> doesn't work?
> The error message I get is
> Bulk insert data conversion error (type mismatch) for row
> 1, column 385 (...column description...).
>
> I have tried with different row terminators but no success.
> I suspect that while creating the Altered.txt, 2 row terminator characters
> are added instead of 1.
> Please any help will be much appreciated.
> Thank you,
> Vasilis
>
>
>|||Yes. The table stracture was changed accordingly.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23WQyROGPGHA.2124@.TK2MSFTNGP14.phx.gbl...
> Did you also recreate the table with the datetime as the first column?
> File fields are mapped to target table columns by ordinal position unless
> you specify a format file.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Vasilis X" <v.hantziaras@.interwind.gr> wrote in message
> news:du1d59$2pa3$1@.ulysses.noc.ntua.gr...
>
>|||>>> Print 2, OldLine & vbNewLine
Reviewing your pseudo-code, it looks to me like your are writing out the
original record instead of the new one with the date. Check the output file
to ensure it is as expected. If you still have problems, post the actual
code.
Hope this helps.
Dan Guzman
SQL Server MVP
"Vasilis X" <v.hantziaras@.interwind.gr> wrote in message
news:du1gjs$14o$1@.ulysses.noc.ntua.gr...
> Yes. The table stracture was changed accordingly.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23WQyROGPGHA.2124@.TK2MSFTNGP14.phx.gbl...
>
>|||Yes, you are right.
I actually use Print 2, Newline & vbNewLine.
Following is the actual code :
FileOpen(2, Bulk_File, OpenMode.Output)
While Not EOF(1)
Input(1, lineText)
'create new line
Dim NewLine As String
NewLine = NewDateVariable & vbTab & lineText
'print new line
Print(2, NewLine & vbCrLf) 'Also tried with NewLine, NewLine &
vbNewLine, even with a simple terminator, eg "!" and changed bulk command
syntax to ...ROWTERMINATOR='!'...
End While
FileClose()
Dim bulkCom As String
bulkCom = "BULK INSERT [mydatabase].[owner].[mytable] FROM '" & Bulk_File &
"' WITH ( FIELDTERMINATOR = '\t' , ROWTERMINATOR = '\n'' , KEEPNULLS )"
bulkCom &= WED.ExecuteCommand(bulkCom, False)
End If
WED is a class to communicate with the server. It doesn't add anything to
the command that is to be executed
Bulk_File is the file to be imported|||Did you view the output file to make sure the format is as expected?
Unfortunately, this code snippet doesn't include the NewDataVariable
assignment, which may be part of the problem. I'd like to have enough code
so that I can reproduce your problem in my environment.
I notice that you use NewLine as a variable name. Perhaps
System.Environment.NewLine is being used instead so the output is not as
expected.
Hope this helps.
Dan Guzman
SQL Server MVP
"Vasilis X" <v.hantziaras@.interwind.gr> wrote in message
news:du1kki$c2q$1@.ulysses.noc.ntua.gr...
> Yes, you are right.
> I actually use Print 2, Newline & vbNewLine.
> Following is the actual code :
> FileOpen(2, Bulk_File, OpenMode.Output)
> While Not EOF(1)
> Input(1, lineText)
> 'create new line
> Dim NewLine As String
> NewLine = NewDateVariable & vbTab & lineText
> 'print new line
> Print(2, NewLine & vbCrLf) 'Also tried with NewLine, NewLine
> & vbNewLine, even with a simple terminator, eg "!" and changed bulk
> command syntax to ...ROWTERMINATOR='!'...
> End While
> FileClose()
> Dim bulkCom As String
> bulkCom = "BULK INSERT [mydatabase].[owner].[mytable] FROM '" & Bulk_File
> & "' WITH ( FIELDTERMINATOR = '\t' , ROWTERMINATOR = '\n'' , KEEPNULLS )"
> bulkCom &= WED.ExecuteCommand(bulkCom, False)
> End If
>
> WED is a class to communicate with the server. It doesn't add anything to
> the command that is to be executed
> Bulk_File is the file to be imported
>
>|||The first thing I'd check is that the file you are getting has the correct
separator and terminator bytes. Possibly Print is adding its own line
terminators, e.g., vbCrLf. The easiest way to check is to open a Command
window, enter debug source.txt, and then type d at the - prompt. That will
dump the start of the file in ASCII and Hex. Enter d again to dump the next
128 bytes. Enter q to quit out of debug.
Your lines should be terminated by 0A. If they are terminated by 0D 0A, for
example, VB is adding a CR and LF (the standard Windows text file line
terminator), and you will have to modify your program or the BULK INSERT
accordingly.
HTH,
Mike Abraham
"Vasilis X" <v.hantziaras@.interwind.gr> wrote in message
news:du1kki$c2q$1@.ulysses.noc.ntua.gr...
> Yes, you are right.
> I actually use Print 2, Newline & vbNewLine.
> Following is the actual code :
> FileOpen(2, Bulk_File, OpenMode.Output)
> While Not EOF(1)
> Input(1, lineText)
> 'create new line
> Dim NewLine As String
> NewLine = NewDateVariable & vbTab & lineText
> 'print new line
> Print(2, NewLine & vbCrLf) 'Also tried with NewLine, NewLine
> & vbNewLine, even with a simple terminator, eg "!" and changed bulk
> command syntax to ...ROWTERMINATOR='!'...
> End While
> FileClose()
> Dim bulkCom As String
> bulkCom = "BULK INSERT [mydatabase].[owner].[mytable] FROM '" & Bulk_File
> & "' WITH ( FIELDTERMINATOR = '\t' , ROWTERMINATOR = '\n'' , KEEPNULLS )"
> bulkCom &= WED.ExecuteCommand(bulkCom, False)
> End If
>
> WED is a class to communicate with the server. It doesn't add anything to
> the command that is to be executed
> Bulk_File is the file to be imported
>
>

Saturday, February 25, 2012

BULK INSERT - Urgent

I have a tab delimeted file which i BCP into a temp table using BULK INSERT
When i use DTS Transform data task i get all 7000 records in the table
In DTS data transform i choose Column Delimeter is a tab ann row delimeter
is {CR}{LF}
When i do Bulk insert in using command below a lot of these records are
combined with previous records dud to improper row terminators on some records
How do i make this work in BULK INSERT same way as it works in DTS
BULK INSERT #stage_obligor_exposure
FROM "k:\sqldb2\ermg\data\apr2005\exposures\test1.txt"
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = "\t",
ROWTERMINATOR = "\n"
)
Pls help..........This is a multi-part message in MIME format.
--020900050805040007080207
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
One thing I noticed is that you use "\r\n" as your row terminator in
your DTS package but only "\n" in your BULK INSERT statement. Perhaps
every row in your input file doesn't have a "\n" to terminate but only a
"\r" (from memory, although it's been a while so I could be wrong,
typically text files generated with Unix/Linux tools only use a "\r"
(ASCII 13) to terminate a line). I don't know why \r\n would work in
the DTS package if a line only has a \r but maybe you could try
different combinations of row terminators for your BULK INSERT statement
(like "\n", "\r\n", "\r").
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Sanjay wrote:
>I have a tab delimeted file which i BCP into a temp table using BULK INSERT
>When i use DTS Transform data task i get all 7000 records in the table
>In DTS data transform i choose Column Delimeter is a tab ann row delimeter
>is {CR}{LF}
>When i do Bulk insert in using command below a lot of these records are
>combined with previous records dud to improper row terminators on some records
>How do i make this work in BULK INSERT same way as it works in DTS
>BULK INSERT #stage_obligor_exposure
> FROM "k:\sqldb2\ermg\data\apr2005\exposures\test1.txt"
> WITH
> (
> FIRSTROW = 2,
> FIELDTERMINATOR = "\t",
> ROWTERMINATOR = "\n"
> )
>Pls help..........
>
>
--020900050805040007080207
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>One thing I noticed is that you use "\r\n" as your row terminator
in your DTS package but only "\n" in your BULK INSERT statement.Â
Perhaps every row in your input file doesn't have a "\n" to terminate
but only a "\r" (from memory, although it's been a while so I could be
wrong, typically text files generated with Unix/Linux tools only use a
"\r" (ASCII 13) to terminate a line).Â I don't know why \r\n would work
in the DTS package if a line only has a \r but maybe you could try
different combinations of row terminators for your BULK INSERT
statement (like "\n", "\r\n", "\r").</tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
Sanjay wrote:
<blockquote cite="midBC983130-5E18-4E77-92BB-0FFC20421FAA@.microsoft.com"
type="cite">
<pre wrap="">I have a tab delimeted file which i BCP into a temp table using BULK INSERT
When i use DTS Transform data task i get all 7000 records in the table
In DTS data transform i choose Column Delimeter is a tab ann row delimeter
is {CR}{LF}
When i do Bulk insert in using command below a lot of these records are
combined with previous records dud to improper row terminators on some records
How do i make this work in BULK INSERT same way as it works in DTS
BULK INSERT #stage_obligor_exposure
FROM "k:\sqldb2\ermg\data\apr2005\exposures\test1.txt"
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = "\t",
ROWTERMINATOR = "\n"
)
Pls help..........
</pre>
</blockquote>
</body>
</html>
--020900050805040007080207--|||I have gotten this bulk insert statement to work and all you would have to do
is change the fieldterminator to '\t'. Notice the file I was working with
was CSV and it had "," for field termination. You can try using a FORMAT
file parameter but so far I have been unsuccessful in getting that to work.
You can make a format file from the table using the following code:
EXEC master.dbo.xp_cmdshell 'BCP sqlsrv.dbo.SystemTemp FORMAT -Usa
-Ppassword -N -fc:\TBL_Format.fmt'
Which will make you a format file of the table you are trying to bulk import
into.
---
BULK INSERT [dbo].[SystemTemp]
FROM 'c:\SystemTempIn.csv'
WITH
(
DATAFILETYPE = 'char',
FIELDTERMINATOR = '","',
FIRSTROW = 2,
ROWTERMINATOR = '"\n'
)
"Sanjay" wrote:
> I have a tab delimeted file which i BCP into a temp table using BULK INSERT
> When i use DTS Transform data task i get all 7000 records in the table
> In DTS data transform i choose Column Delimeter is a tab ann row delimeter
> is {CR}{LF}
> When i do Bulk insert in using command below a lot of these records are
> combined with previous records dud to improper row terminators on some records
> How do i make this work in BULK INSERT same way as it works in DTS
> BULK INSERT #stage_obligor_exposure
> FROM "k:\sqldb2\ermg\data\apr2005\exposures\test1.txt"
> WITH
> (
> FIRSTROW = 2,
> FIELDTERMINATOR = "\t",
> ROWTERMINATOR = "\n"
> )
> Pls help..........
>

Wednesday, March 7, 2012

Bulk insert and vb.net - URGENT

Saturday, February 25, 2012

BULK INSERT - Urgent

build sql script

Blog Archive

About Me