May 6, 2009 at 1:05 pm
Yes, but did you check for error messages in the Logtable? That was the point of my change.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 6, 2009 at 1:58 pm
220 [-i inputfile] [-o outfile] [-a packetsize]52:56.3
221 [-S server name] [-U username] [-P password]52:56.3
222 [-T trusted connection] [-v version] [-R regional enable]52:56.3
223 [-k keep null values] [-E keep identity values]52:56.3
224 [-h "load hints"] [-x generate xml format file]52:56.3
225NULL52:56.3
226NULL52:56.3
Dont know what this refers to ?
May 6, 2009 at 2:13 pm
There should be one or more records before that too?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 6, 2009 at 2:18 pm
OK, I just realized that that is the BCP Help output, which you get when you run BCP but there is some syntax error with the command line. We need to see the whole command line , which should be the first row in Logtable for that time sequence.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 6, 2009 at 2:35 pm
idQueryImporteddate
1"MASTER.DBO.xp_cmdshell 'bcp ""Bank.dbo.Account""
in ""c:\Myimport\a.txt"" -SSQL -T -c -r -t,'"31:21.1
2usage: bcp {dbtable | query} {in | out | queryout | format} datafile31:21.1
3 [-m maxerrors] [-f formatfile] [-e errfile]31:21.1
4 [-F firstrow] [-L lastrow] [-b batchsize]31:21.1
5 [-n native type] [-c character type] [-w wide character type]31:21.1
6 [-N keep non-text native] [-V file format version] [-q quoted identifier]31:21.1
7 [-C code page specifier] [-t field terminator] [-r row terminator]31:21.1
8 [-i inputfile] [-o outfile] [-a packetsize]31:21.1
9 [-S server name] [-U username] [-P password]31:21.1
10 [-T trusted connection] [-v version] [-R regional enable]31:21.1
11 [-k keep null values] [-E keep identity values]31:21.1
12 [-h "load hints"] [-x generate xml format file]31:21.1
13NULL31:21.1
14"MASTER.DBO.xp_cmdshell 'bcp ""Bank.dbo.Account""
in ""c:\Myimport\aa.csv"" -SSQL -T -c -r -t,'"31:21.1
15usage: bcp {dbtable | query} {in | out | queryout | format} datafile31:21.2
16 [-m maxerrors] [-f formatfile] [-e errfile]31:21.2
17 [-F firstrow] [-L lastrow] [-b batchsize]31:21.2
18 [-n native type] [-c character type] [-w wide character type]31:21.2
19 [-N keep non-text native] [-V file format version] [-q quoted identifier]31:21.2
20 [-C code page specifier] [-t field terminator] [-r row terminator]31:21.2
21 [-i inputfile] [-o outfile] [-a packetsize]31:21.2
22 [-S server name] [-U username] [-P password]31:21.2
23 [-T trusted connection] [-v version] [-R regional enable]31:21.2
24 [-k keep null values] [-E keep identity values]31:21.2
25 [-h "load hints"] [-x generate xml format file]31:21.2
26NULL31:21.2
27"MASTER.DBO.xp_cmdshell 'bcp ""Bank.dbo.Account""
in ""c:\Myimport\b.txt"" -SSQL -T -c -r -t,'"31:21.2
28usage: bcp {dbtable | query} {in | out | queryout | format} datafile31:21.2
29 [-m maxerrors] [-f formatfile] [-e errfile]31:21.2
30 [-F firstrow] [-L lastrow] [-b batchsize]31:21.2
31 [-n native type] [-c character type] [-w wide character type]31:21.2
32 [-N keep non-text native] [-V file format version] [-q quoted identifier]31:21.2
33 [-C code page specifier] [-t field terminator] [-r row terminator]31:21.2
34 [-i inputfile] [-o outfile] [-a packetsize]31:21.2
35 [-S server name] [-U username] [-P password]31:21.2
36 [-T trusted connection] [-v version] [-R regional enable]31:21.2
37 [-k keep null values] [-E keep identity values]31:21.2
38 [-h "load hints"] [-x generate xml format file]31:21.2
39NULL31:21.2
40"MASTER.DBO.xp_cmdshell 'bcp ""Bank.dbo.Account""
in ""c:\Myimport\c.txt"" -SSQL -T -c -r -t,'"31:21.3
41usage: bcp {dbtable | query} {in | out | queryout | format} datafile31:21.3
42 [-m maxerrors] [-f formatfile] [-e errfile]31:21.3
43 [-F firstrow] [-L lastrow] [-b batchsize]31:21.3
44 [-n native type] [-c character type] [-w wide character type]31:21.3
45 [-N keep non-text native] [-V file format version] [-q quoted identifier]31:21.3
46 [-C code page specifier] [-t field terminator] [-r row terminator]31:21.3
47 [-i inputfile] [-o outfile] [-a packetsize]31:21.3
48 [-S server name] [-U username] [-P password]31:21.3
49 [-T trusted connection] [-v version] [-R regional enable]31:21.3
50 [-k keep null values] [-E keep identity values]31:21.3
51 [-h "load hints"] [-x generate xml format file]31:21.3
52NULL31:21.3
53NULL31:21.3
May 6, 2009 at 3:02 pm
ifila (5/6/2009)
...27"MASTER.DBO.xp_cmdshell 'bcp ""Bank.dbo.Account""
in ""c:\Myimport\b.txt"" -SSQL -T -c -r -t,'"31:21.2
...
Yeah, there's too many quotes in these, I think. This should look more like this:
...
27"MASTER.DBO.xp_cmdshell 'bcp "Bank.dbo.Account"
in "c:\Myimport\b.txt" -SSQL -T -c -r -t,'"31:21.2
...
Also, that trailing comma doesn't look right.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 6, 2009 at 5:32 pm
ifila (5/6/2009)
I still get nulls in the Account Table 🙁Thanks
Are all of the files you're trying to import in the exact same format and field order?
--Jeff Moden
May 6, 2009 at 5:49 pm
I am using a small sample of test data that can be configured any way i choose.
On my live data i am storing various text files with different positioning of fields.
The way it works at the moment, i load the data into a single large space, which has FTI, then i run a search using the sql contains statement.
It is crude but it works.
Is SSIS better than using the Bulk Insert statement ?
BTW i tried moving the quotes and it did not cure the issue.
What i dont understand is why i dont get sql errors when i run the query ?
Does anyone know where i can get sample code that works using bulk insert on multiple files ? I can get a single file to work fine, but not mutliple files!
The code i have found so far seems to give me errors.
Thanks
May 6, 2009 at 7:43 pm
I thought that I already explained this. You don't get any SQL Errors because you don't have any SQL errors, you have errors in your BCP command line. Please look at my previous post where I point out some of the apparent mistakes in it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 7, 2009 at 5:08 am
I did and i got MORE errors.
May 7, 2009 at 8:45 am
"I was using the VB application since it made the parsing and loading process very low maintenance. The downside is that as my needs have increased, the performance is lacking.
I have never used the bulk insert feature, and based on your numbers, seems very fast. I now have to find a parsing solution."
As Jeff says use bulk insert.
You can still use your old VB code and ADO , I suppose you use.
A simple example
'Create a cmd ADO command
Dim cmd As New ADODB.Command
cmd.CommandText = "Bulk insert table from 'filepath&file.txt'" & _
"With (CODEPAGE = 'ACP' )" 'For more detail look in Book on line
cmd.Execute
Why do you need to parse the text file?
If it is a flat file with no field separator import the file to a temp table with
one field and from that table use the substring function to select the individual
fields. I always do like this because it is very easy to maintain.
Running on a 64 bit computer VB.net ADO.net might be faster?
I know for shure it is faster writing text to a file.
Writing 17 000 000 records about 4 Gbyte text filesize took 3,5 hours with VB6 and ADO
with VB.net and ADO.net I brought down to 1 hour on the same machine.
/Gosta
May 7, 2009 at 10:34 am
Jeff Moden (5/6/2009)
ifila (5/6/2009)
I still get nulls in the Account Table 🙁Thanks
Are all of the files you're trying to import in the exact same format and field order?
I can help, but I still need to know the Yes/No answer to the above.
--Jeff Moden
May 7, 2009 at 10:43 am
RBarryYoung (5/6/2009)
I thought that I already explained this. You don't get any SQL Errors because you don't have any SQL errors, you have errors in your BCP command line. Please look at my previous post where I point out some of the apparent mistakes in it.
Then we would need to see those errors.
This is how debugging integrated solutions works. Because you have several different facilities serially dependent on each other:
SQL -> Dynamic SQL -> CmdShell/DOS -> BCP -> SQL Server Access -> SQL(again)
you have to fix the problems in one before you can find the problems in the next. Right now you are at the BCP facility in this chain, so there is still a couple of more steps to go.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 7, 2009 at 11:11 am
I played with the code and at least have got it to load 574 text resumes in one session 🙂
The downside is that the input folder has 14500 text files, so i dont know why it did not load the rest ?
I set the table up with one varchar(max) field called resume to practice a large import.
Does anything obvious stick out ?
Thanks
alter procedure [dbo].[usp_ImportMultipleFiles] @filepath varchar(500),
@pattern varchar(100), @TableName varchar(128)
as
set quoted_identifier off
declare @ID int
declare @query varchar (max)
declare @max1 int
declare @count1 int
Declare @filename varchar(100)
set @count1 =0
set @ID =0
create table #x (resume varchar(max))
set @query ='master.dbo.xp_cmdshell ''dir '+@filepath+@pattern +' /b'''
insert #x exec (@query)
delete from #x where resume is NULL
select identity(int,1,1) as '@ID', resume into #y from #x
drop table #x
set @max1 = (select max(@ID) from #y)
--print @max1
--print @count1
While @count1 <= @max1
begin
set @count1=@count1+1
set @filename = (select resume from #y where [@id] = @count1)
set @Query ='BULK INSERT '+ @Tablename + ' FROM '''+ @Filepath+@Filename+'''
WITH ( FIELDTERMINATOR = '','',ROWTERMINATOR = '''')'
--print @query
exec (@query)
--insert into logtable (query) select @query
end
drop table #y
May 7, 2009 at 11:29 am
Is there anything in your Logtable?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 16 through 30 (of 48 total)
You must be logged in to reply to this topic. Login to reply