Bulk Insert Problem

I receive near about 50 text files every month each has near about 1lakh to 3
lakh rows. they come in various formats (few with , delimiters, few with
field qualifiers, few with mix and match of both, and few files with fixed
format string)
so i use bulk insert to copy them into sql server using format files.
Everything works fine so far.
the problem is, in those txt files some times few lines can be either too
long or too short. in that case bulk insert totally rejects the entire file.
That is i dont want the process to stop just because few lines were corrupt.
is there anyway through which i can insert the data by skipping those few
corrupt lines.
Thanks for any help in advance.
This is urgent
Kitty,
Have a look at the -m and -e switches for bcp. This won't solve your problem
though, you need to fix the source data.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Kitty" wrote:

> I receive near about 50 text files every month each has near about 1lakh to 3
> lakh rows. they come in various formats (few with , delimiters, few with
> field qualifiers, few with mix and match of both, and few files with fixed
> format string)
> so i use bulk insert to copy them into sql server using format files.
> Everything works fine so far.
> the problem is, in those txt files some times few lines can be either too
> long or too short. in that case bulk insert totally rejects the entire file.
> That is i dont want the process to stop just because few lines were corrupt.
> is there anyway through which i can insert the data by skipping those few
> corrupt lines.
> Thanks for any help in advance.
> This is urgent
|||Thanks for your time mark.
As you rightly said, this wont solve my problem. and i have no control over
source data, since it comes from various people from various countries all
over the world.
any more suggestions.
Thanks in advance
Kitty
"Mark Allison" wrote:
[vbcol=seagreen]
> Kitty,
> Have a look at the -m and -e switches for bcp. This won't solve your problem
> though, you need to fix the source data.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> "Kitty" wrote:

Bulk Insert Problem

I receive near about 50 text files every month each has near about 1lakh to 3
lakh rows. they come in various formats (few with , delimiters, few with
field qualifiers, few with mix and match of both, and few files with fixed
format string)
so i use bulk insert to copy them into sql server using format files.
Everything works fine so far.
the problem is, in those txt files some times few lines can be either too
long or too short. in that case bulk insert totally rejects the entire file.
That is i dont want the process to stop just because few lines were corrupt.
is there anyway through which i can insert the data by skipping those few
corrupt lines.
Thanks for any help in advance.
This is urgentKitty,
Have a look at the -m and -e switches for bcp. This won't solve your problem
though, you need to fix the source data.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Kitty" wrote:
> I receive near about 50 text files every month each has near about 1lakh to 3
> lakh rows. they come in various formats (few with , delimiters, few with
> field qualifiers, few with mix and match of both, and few files with fixed
> format string)
> so i use bulk insert to copy them into sql server using format files.
> Everything works fine so far.
> the problem is, in those txt files some times few lines can be either too
> long or too short. in that case bulk insert totally rejects the entire file.
> That is i dont want the process to stop just because few lines were corrupt.
> is there anyway through which i can insert the data by skipping those few
> corrupt lines.
> Thanks for any help in advance.
> This is urgent|||Thanks for your time mark.
As you rightly said, this wont solve my problem. and i have no control over
source data, since it comes from various people from various countries all
over the world.
any more suggestions.
Thanks in advance
Kitty
"Mark Allison" wrote:
> Kitty,
> Have a look at the -m and -e switches for bcp. This won't solve your problem
> though, you need to fix the source data.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> "Kitty" wrote:
> > I receive near about 50 text files every month each has near about 1lakh to 3
> > lakh rows. they come in various formats (few with , delimiters, few with
> > field qualifiers, few with mix and match of both, and few files with fixed
> > format string)
> > so i use bulk insert to copy them into sql server using format files.
> > Everything works fine so far.
> > the problem is, in those txt files some times few lines can be either too
> > long or too short. in that case bulk insert totally rejects the entire file.
> > That is i dont want the process to stop just because few lines were corrupt.
> > is there anyway through which i can insert the data by skipping those few
> > corrupt lines.
> >
> > Thanks for any help in advance.
> > This is urgent

Bulk Insert Problem

I receive near about 50 text files every month each has near about 1lakh to
3
lakh rows. they come in various formats (few with , delimiters, few with
field qualifiers, few with mix and match of both, and few files with fixed
format string)
so i use bulk insert to copy them into sql server using format files.
Everything works fine so far.
the problem is, in those txt files some times few lines can be either too
long or too short. in that case bulk insert totally rejects the entire file.
That is i dont want the process to stop just because few lines were corrupt.
is there anyway through which i can insert the data by skipping those few
corrupt lines.
Thanks for any help in advance.
This is urgentKitty,
Have a look at the -m and -e switches for bcp. This won't solve your problem
though, you need to fix the source data.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Kitty" wrote:

> I receive near about 50 text files every month each has near about 1lakh t
o 3
> lakh rows. they come in various formats (few with , delimiters, few with
> field qualifiers, few with mix and match of both, and few files with fixed
> format string)
> so i use bulk insert to copy them into sql server using format files.
> Everything works fine so far.
> the problem is, in those txt files some times few lines can be either too
> long or too short. in that case bulk insert totally rejects the entire fil
e.
> That is i dont want the process to stop just because few lines were corrup
t.
> is there anyway through which i can insert the data by skipping those few
> corrupt lines.
> Thanks for any help in advance.
> This is urgent|||Thanks for your time mark.
As you rightly said, this wont solve my problem. and i have no control over
source data, since it comes from various people from various countries all
over the world.
any more suggestions.
Thanks in advance
Kitty
"Mark Allison" wrote:
[vbcol=seagreen]
> Kitty,
> Have a look at the -m and -e switches for bcp. This won't solve your probl
em
> though, you need to fix the source data.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> "Kitty" wrote:
>sql

Bulk Insert from CSV - trouble with .FMT

I need to bulk insert from multiple files which are comma-separated with quotes as delimiters around each column. I cannot use DTS because the filenames are variable (unless someone knows how to get DTS to read 'DIR *.csv' and then load each file ?)

This .fmt doesn't work because SQL sees "","" as "" - meaning no terminator - then ,"" where it expects whitespace.

8.0
6
1 SQLCHAR 0 1 "","" 3 Prefix SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 1 "","" 5 Forenames SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 1 "","" 4 Surname SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 1 "","" 6 Job_Title SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 1 "","" 7 Org_Name SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 1 "","" 8 Address1 SQL_Latin1_General_CP1_CI_AS

I have tried '","' (single quote doublequote comma) to no avail.

I cannot use the obvious solution - bulk insert ... (with terminator = ' "," ') - as I need to insert all the data into specified columns of an existing table using different mappings. The .fmt file should be helping, but I cannot get past this issue.

Does anyone know how to resolve this?

You should use the escaped syntax for these special characters so in the field separator your " character should be given as \" just like the C language escape syntax. I am providing a sample format file here:

9.0
3
1 SQLCHAR 0 12 "\",\"" 1 c1 ""
2 SQLCHAR 0 20 "\",\"" 2 c2 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 12 "\r\n" 3 c3 ""

This file works for data like this one, which I guess is the format of your data too:

1","abc","234
2","def","567

Thanks

Waseem

|||Thanks Waseem - That's just what I neeed to know. |||Hi.
I have a similar problem where my columns are seperated by a comma. The thing is however, that some of my data contains comma's, and that they are then encapsulated by a " to show that it is data. The problem is that this is then seen as a new column.
SAMPLE DATA:

"2,397", Type-A, EQTY,"2,392",John Smith

What can I do to resolve the comma problem, as well as that the " is not seen as data?|||

Does every column value for a particular column have the same format? What I mean is that will in every row your data for 1st and 4th field be quoted or does this vary. If it changes by rows then you are asking for regular expression matching which bcp is not capable of. If the data format is the same, you could play some tricks, for example:

a) First quote could be considered a 1 byte field with no terminator and no corresponding server column.

b) The terminator for 1st field would then be ", instead of a ,

c) Similar treatment for the 4th column, e.g. terminator for EQTY field could be made ," instead of a , and tterminator for 2,392 could be ", instead of a ,

Thanks

Waseem Basheer

Bulk Insert from CSV - trouble with .FMT

I need to bulk insert from multiple files which are comma-separated with quotes as delimiters around each column. I cannot use DTS because the filenames are variable (unless someone knows how to get DTS to read 'DIR *.csv' and then load each file ?)

This .fmt doesn't work because SQL sees "","" as "" - meaning no terminator - then ,"" where it expects whitespace.

8.0
6
1 SQLCHAR 0 1 "","" 3 Prefix SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 1 "","" 5 Forenames SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 1 "","" 4 Surname SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 1 "","" 6 Job_Title SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 1 "","" 7 Org_Name SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 1 "","" 8 Address1 SQL_Latin1_General_CP1_CI_AS

I have tried '","' (single quote doublequote comma) to no avail.

I cannot use the obvious solution - bulk insert ... (with terminator = ' "," ') - as I need to insert all the data into specified columns of an existing table using different mappings. The .fmt file should be helping, but I cannot get past this issue.

Does anyone know how to resolve this?

You should use the escaped syntax for these special characters so in the field separator your " character should be given as \" just like the C language escape syntax. I am providing a sample format file here:

9.0
3
1 SQLCHAR 0 12 "\",\"" 1 c1 ""
2 SQLCHAR 0 20 "\",\"" 2 c2 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 12 "\r\n" 3 c3 ""

This file works for data like this one, which I guess is the format of your data too:

1","abc","234
2","def","567

Thanks

Waseem

|||Thanks Waseem - That's just what I neeed to know. |||Hi.
I have a similar problem where my columns are seperated by a comma. The thing is however, that some of my data contains comma's, and that they are then encapsulated by a " to show that it is data. The problem is that this is then seen as a new column.
SAMPLE DATA:

"2,397", Type-A, EQTY,"2,392",John Smith

What can I do to resolve the comma problem, as well as that the " is not seen as data?|||

Does every column value for a particular column have the same format? What I mean is that will in every row your data for 1st and 4th field be quoted or does this vary. If it changes by rows then you are asking for regular expression matching which bcp is not capable of. If the data format is the same, you could play some tricks, for example:

a) First quote could be considered a 1 byte field with no terminator and no corresponding server column.

b) The terminator for 1st field would then be ", instead of a ,

c) Similar treatment for the 4th column, e.g. terminator for EQTY field could be made ," instead of a , and tterminator for 2,392 could be ", instead of a ,

Thanks

Waseem Basheer

BULK INSERT file with quoted strings, but no quotes when NULL

Hello,
I've put together a format file for a text file that I need to import
using ",\"" and "\",\"", etc. as column delimiters. This would normally
cover the whole problem with quoted strings. However, in this
particular file if a string value is NULL then that column doesn't have
the double quotes around it for that record.
Is there any way to handle this using BULK INSERT? I know that I can do
it in DTS, but can it be done with BULK INSERT?
Thanks!
-Tom.I should also point out that some of these files can be a couple
hundred million rows, so I would really prefer not to load them in with
the quotes and then use REPLACE() to remove those quotes.
Thanks,
-Tom.
Aardvark wrote:
> Hello,
> I've put together a format file for a text file that I need to import
> using ",\"" and "\",\"", etc. as column delimiters. This would normally
> cover the whole problem with quoted strings. However, in this
> particular file if a string value is NULL then that column doesn't have
> the double quotes around it for that record.
> Is there any way to handle this using BULK INSERT? I know that I can do
> it in DTS, but can it be done with BULK INSERT?
> Thanks!
> -Tom.|||My suggestion since you do not want to use the REPLACE function is to
strip the " character using the Operating System or a text editor..
Maybe you could use cygwin and SED/AWK.
JD.|||Aardvark (tom_hummel@.hotmail.com) writes:
> I've put together a format file for a text file that I need to import
> using ",\"" and "\",\"", etc. as column delimiters. This would normally
> cover the whole problem with quoted strings. However, in this
> particular file if a string value is NULL then that column doesn't have
> the double quotes around it for that record.
> Is there any way to handle this using BULK INSERT? I know that I can do
> it in DTS, but can it be done with BULK INSERT?
Unless there are some very fortunate circumstances with the file format,
the answer is no. The best suggestion I can give is to write a program
that reads the file, strips the quotes and the uses the bulk-copy API
to insert from variables. But you probably prefer to use DTS instead.
(And what those fortunate cicrumstances may be, I don't really know.)
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