Posted by Erland Sommarskog on 10/14/05 00:27

Michael Houmaark (mhoum@tdc.dk) writes:
> 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

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_Norwegian_CS_AS
3 SQLCHAR 0 0 "\"\n" 3 col3 Danish_Norwegian_CS_AS
-END---------------- Format file


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

• Next in forum: Re: Update One Based on ANother Table
• Prev in forum: Re: Type Mismatch Error On Bulk Insert
• Thread view: Re: Bulk insert

[Reply to this message]