Bulk insert

[Michael Houmaark]

Hi

I have a text file with this information
-BEGIN----------------- tekst.txt-------------
10, "firstname" , "lastname"
11, "Mette", "Larsen"
--| |--
6 000 000, "Michael", "Houmaark"
-END-------------------- tekst.txt-------------

I use this SQL-query
-BEGIN------------------SQL--------------
bulk insert tlf.dbo.bruger_ data from 'C:\TEKST.txt'
with
(
FIRSTROW = 1,
FIELDTERMINATOR = '";"',
ROWTERMINATOR = '"\n'
)
-END--------------------SQL--------------


But when the data is in the table its still have the " arround the firstname
and lastname
what do I do ?????

Best Regards
Michael H

[Erland Sommarskog]

Re: Bulk insert

Michael Houmaark (mhoum@tdc.dk) writes:[color=blue]
> I have a text file with this information
> -BEGIN----------------- tekst.txt-------------
> 10, "firstname" , "lastname"
> 11, "Mette", "Larsen"
> --| |--
> 6 000 000, "Michael", "Houmaark"
> -END-------------------- tekst.txt-------------
>
> I use this SQL-query
> -BEGIN------------------SQL--------------
> bulk insert tlf.dbo.bruger_ data from 'C:\TEKST.txt'
> with
> (
> FIRSTROW = 1,
> FIELDTERMINATOR = '";"',
> ROWTERMINATOR = '"\n'
> )
> -END--------------------SQL--------------
>
>
> But when the data is in the table its still have the " arround the
> firstname and lastname[/color]

You need to use a format file, because your field delimiters are not
consistent.

-BEGIN---------------- Format file
8.0
3
1 SQLCHAR 0 0 ", \"" 1 col1 ""
2 SQLCHAR 0 0 "\", \"" 2 col2 Danish_Norwegia n_CS_AS
3 SQLCHAR 0 0 "\"\n" 3 col3 Danish_Norwegia n_CS_AS
-END---------------- Format file


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp