BULK INSERT

Hi All,
I want to BULK INSERT from a text file to SQL but I only want to insert the
lines that begin with 'valid'. Is it possible? Is there a link where can i
find help with this problem?
Thanks!
joelConsider employing a staging table as the target of your BULK INSERT. You
can then use INSERT ...SELECT...WHERE to populate the main table with the
desired rows (e.g. WHERE MyColumn = 'valid').
Hope this helps.
Dan Guzman
SQL Server MVP
"Joel Gacosta" <joel@.gacosta.net> wrote in message
news:%23Jw6fTzPFHA.2584@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> I want to BULK INSERT from a text file to SQL but I only want to insert
> the lines that begin with 'valid'. Is it possible? Is there a link where
> can i find help with this problem?
> Thanks!
> joel
>|||My source is a CSV text file and not from a database. How can i do this?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u0gqRm1PFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Consider employing a staging table as the target of your BULK INSERT. You
> can then use INSERT ...SELECT...WHERE to populate the main table with the
> desired rows (e.g. WHERE MyColumn = 'valid').
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Joel Gacosta" <joel@.gacosta.net> wrote in message
> news:%23Jw6fTzPFHA.2584@.TK2MSFTNGP15.phx.gbl...
>|||> My source is a CSV text file and not from a database. How can i do this?
Let's say you have a csv with 3 fields containing the following records:
"valid",1,"A"
"invalid",2,"B"
"valid",3,"C"
Create a staging table with the columns you want to import:
CREATE TABLE StagingTable
(
Col1 varchar(10) NOT NULL,
Col2 int NOT NULL,
Col3 varchar(10) NOT NULL
)
Create a BCP format file that specifies the input file format and target
column mapping:
8.0
4
1 SQLCHAR 0 0 "\"" 0 unused ""
2 SQLCHAR 0 10 "\"," 1 Col1 ""
3 SQLCHAR 0 10 ",\"" 2 Col2 ""
4 SQLCHAR 0 10 "\"\r\n" 3 Col3 ""
Import the data into the staging table, specifying the table name, data file
and format file:
BULK INSERT dbo.StagingTable
FROM 'C:\Mydata.csv'
WITH
(
FORMATFILE='C:\Mydata.fmt'
)
Finally, insert the data into your main table:
INSERT INTO MainTable (Col2, Col3)
SELECT Col2, Col3
FROM dbo.StagingTable
WHERE Col1 = 'valid'
You might also consider using DTS so that you can filter the rows in an
ActiveX script and bypass the staging table.
Hope this helps.
Dan Guzman
SQL Server MVP
"Joel Gacosta" <joel@.gacosta.net> wrote in message
news:unZAfa7PFHA.3760@.TK2MSFTNGP10.phx.gbl...
> My source is a CSV text file and not from a database. How can i do this?
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:u0gqRm1PFHA.3716@.TK2MSFTNGP14.phx.gbl...
>|||Thanks Dan! I'll give it a try.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uiD7Sr8PFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Let's say you have a csv with 3 fields containing the following records:
> "valid",1,"A"
> "invalid",2,"B"
> "valid",3,"C"
> Create a staging table with the columns you want to import:
> CREATE TABLE StagingTable
> (
> Col1 varchar(10) NOT NULL,
> Col2 int NOT NULL,
> Col3 varchar(10) NOT NULL
> )
> Create a BCP format file that specifies the input file format and target
> column mapping:
> 8.0
> 4
> 1 SQLCHAR 0 0 "\"" 0 unused ""
> 2 SQLCHAR 0 10 "\"," 1 Col1 ""
> 3 SQLCHAR 0 10 ",\"" 2 Col2 ""
> 4 SQLCHAR 0 10 "\"\r\n" 3 Col3 ""
> Import the data into the staging table, specifying the table name, data
> file and format file:
> BULK INSERT dbo.StagingTable
> FROM 'C:\Mydata.csv'
> WITH
> (
> FORMATFILE='C:\Mydata.fmt'
> )
> Finally, insert the data into your main table:
> INSERT INTO MainTable (Col2, Col3)
> SELECT Col2, Col3
> FROM dbo.StagingTable
> WHERE Col1 = 'valid'
> You might also consider using DTS so that you can filter the rows in an
> ActiveX script and bypass the staging table.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Joel Gacosta" <joel@.gacosta.net> wrote in message
> news:unZAfa7PFHA.3760@.TK2MSFTNGP10.phx.gbl...
>