Bulk Insert: One Row not importing in...

Ok so I got this working:

BULK INSERT

dbo.tbCheckPointTest

FROM 'c:\inetpub\wwwroot\upload\maxie_task_aging.csv'

WITH

(DATAFILETYPE = 'char', FIELDTERMINATOR = ',',

FIRSTROW = 2, ROWS_PER_BATCH = 6, ROWTERMINATOR = '\n')

My data file looks like this:

REPORT_EXECUTED_ON:20070403_170001 Research 306 263 2470 State 1031 7 0 Program 13 1 0 Program Tech 85 69 139

Total of 6 rows, so I indicate to start on Row 2, yet only the last three lines are getting into my table.

I have changed and manipulated the query varies counts, but still just the last three lines get imported.

Thoughts?

Thanks!

Hi,

From the looks of your data file, it is difficult to tell how many blank lines are associated with your data file.

It appears that "Research" may begin on line 4, in which case you would have only three rows of data: "Research", "State", and "Program".

I am confused by the fact that you get the last three rows: "State", "Program", and "Program Tech".

Is it possible that some rows are terminated by "0A0D" and others only by "0A" or "0D", and "\n" is being interpreted as strictly "0A0D" (hexadecimal)?

I also am confused by your use of "FIELDTERMINATOR = ',' ", since I don't see any commas in the data you have displayed.

Dan

|||

Sorry. I had the .csv open with excel when I copied and pasted it:

REPORT_EXECUTED_ON:20070404_110001

Research Specialist - Research , 340, 91, 2635
State Worker (Interim) , 918, 3, 0
Program Specialist - Quality Assurance Specialist , 16, 1, 0
Program Technician - Data Entry Hold , 82, 28, 160

I am going to see if anything you suggested will help.

|||

The problem seems to be that the BULK INSERT function seems to expect all the rows in the file to have the correct number of fields for the target table (even those being skipped). So it is ignoring the newlines until it has found enough commas (and then counts any other commas until it finds the row terminator as part of that field).

Try loading the file:

REPORT_EXECUTED_ON:20070404_110001,,,
,,,
Research Specialist - Research , 340, 91, 2635
State Worker (Interim) , 918, 3, 0
Program Specialist - Quality Assurance Specialist , 16, 1, 0
Program Technician - Data Entry Hold , 82, 28, 160

Though you will need a FIRSTROW = 3

If you put the last field into a varchar rather than an int then if you add commas to the end (e.g. 2635,,,,) then you will find that these commas end up in that field (if not then it will complain about unexpected characters when it tries to convert it to an int or whatever).

So any rows to be skipped should have the correct number of field separators in them (3) or they will not be included in the skip count.

|||

Thank you, that solved the issue. Now I have to get w/the programmer who delivers the file to me.

|||

Nice catch, Dhericean!

Dan

Bulk Insert/ one column

I have no problem importing a file.txt to my table (mehet).

Bulk Insert mehet From 'C:\test.txt'

With (DataFileType = 'char', FIELDTERMINATOR = ',')

But I would appreciated if someone could help me how to import only 1 or 2 columns.

instead of all columns.

Thanks.

juvan

hi,

you can perhaps have a look at format file support, that can be used by BULK operations..

define a file to be imported, say
d:\imp\data.txt like
[d:\imp\data.txt]
Andrea,Montanari,5
Juvan,Juvan,2
[/d:\imp\data.txt]

you can then use a format file to specify the required mappings... define a first one like
[fmt.txt]
9.0
3
1 SQLCHAR 0 10 "," 1 lName ""
2 SQLCHAR 0 10 "," 2 fName ""
3 SQLCHAR 0 7 "\r\n" 3 Id ""
[/fmt.txt]

define another fmt file like

[fmt2.txt]
9.0
3
1 SQLCHAR 0 10 "," 1 lName ""
2 SQLCHAR 0 10 "," 2 fName ""
3 SQLCHAR 0 7 "\r\n" 0 Id ""
[/fmt2.txt]

all included columns will be mapped to a column of the target table (see SQL script);

SET NOCOUNT ON;

USE tempdb;

GO

CREATE TABLE dbo.Test (

fName varchar(10),

lName varchar(10) DEFAULT 'not set',

Id int DEFAULT -1000

);

GO

BULK INSERT dbo.Test

FROM 'd:\imp\Data.txt'

WITH (formatfile='d:\imp\fmt.txt');

SELECT * FROM dbo.Test;

GO

PRINT 'modify the fmt file putting 0 in the ID column as fmt2.txt';

BULK INSERT dbo.Test

FROM 'd:\imp\Data.txt'

WITH (formatfile='d:\imp\fmt2.txt');

SELECT * FROM dbo.Test;

GO

PRINT 'just use an INSERT ... specifying the required colums';

SELECT lName

FROM OPENROWSET(BULK 'd:\imp\Data.txt',

FORMATFILE='d:\imp\fmt2.txt'

) AS t1;

INSERT INTO dbo.Test (fName)

SELECT lName

FROM OPENROWSET(BULK 'd:\imp\Data.txt',

FORMATFILE='d:\imp\fmt2.txt'

) AS t1;

SELECT * FROM dbo.Test;

GO

DROP TABLE dbo.Test;

--<-

fName lName Id

- - --

Andrea Montanari 5

Juvan Juvan 2

modify the fmt file putting 0 in the ID column as fmt2.txt

fName lName Id

- - --

Andrea Montanari 5

Juvan Juvan 2

Andrea Montanari -1000

Juvan Juvan -1000

just use an INSERT ... specifying the required colums

lName

-

Andrea

Juvan

fName lName Id

- - --

Andrea Montanari 5

Juvan Juvan 2

Andrea Montanari -1000

Juvan Juvan -1000

Andrea not set -1000

Juvan not set -1000

as you can see, you can play with the fmt file as required, excluding unwanted colums at the retrival source time, or later if you just use an INSERT SELECT FROM OPENROWSET..

regards

|||

Hi Andrea,

Thanks for your help. I got it right thanks to you.

I have one more question, if you don't mind?

Sometimes I go out of town where there has no internet. It is possible to connect to my Database at home by using

phoneline direct? If there is a way, Can you let me know please.

Thanks.

Juvan

|||

hi,

you can connect over the net to your server from your home pc... but a direct SQL Server service open over the internet is not that secure... you should eventually read about V(irtual) P(rivate) N(etwork) connections to protect your server and not to allow someone to just open a connection over your TCP/IP xxx-xxx-x-xxx IP address ...

stay secure as you can

regards

|||

hi,

Yes, but as I have said that the area where I go to has no internet connection at all. So in this case what should I do?

Thanks.

Juvan

bulk insert question

Hi! We are in the process of adding few columns to our large tables (200
million rows) and altering few columns from varchar to char. One of the
option I am thinking is to bcp data out, change the schema and bulk insert
data in. Looks like with either bcp.exe or bulk insert command, you won't be
able to load the data in, if the schema of table get changed. Is this true?
I know that I can use DTS export/import to do this task but since bulk
insert is the fastest method I would like to try that option if possible.
Besides, Bulk insert I could also change schema with Alter table command. I
don't know if thats better than unloading/change/reload method that I
mentioned above.
I would appreiciate it, if anyone who have export/change schema/import large
table, give me some direction here.
thanks
If you use the Native mode I don't think you can do it but have you actually
tried? BCP out a few thousand rows, create anew table and Bulk Insert it
back in. If native wont work I am pretty sure char mode will.
Andrew J. Kelly SQL MVP
"james" <kush@.brandes.com> wrote in message
news:%23j3fNPKrFHA.3640@.tk2msftngp13.phx.gbl...
> Hi! We are in the process of adding few columns to our large tables (200
> million rows) and altering few columns from varchar to char. One of the
> option I am thinking is to bcp data out, change the schema and bulk insert
> data in. Looks like with either bcp.exe or bulk insert command, you won't
> be
> able to load the data in, if the schema of table get changed. Is this
> true?
> I know that I can use DTS export/import to do this task but since bulk
> insert is the fastest method I would like to try that option if possible.
> Besides, Bulk insert I could also change schema with Alter table command.
> I
> don't know if thats better than unloading/change/reload method that I
> mentioned above.
> I would appreiciate it, if anyone who have export/change schema/import
> large
> table, give me some direction here.
> thanks
>
|||I have tried both native and character mode and both erroed out. Which is
kind of expected, since schema got changed, the program doesn't have any way
of knowing which column in datafile (native or char) maps to which column in
table.
I haven't tried format file so far, which is what I am going to do next and
see if I can alter format file and make this thing work.
Thanks for your Input.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uWZ$MoLrFHA.2624@.TK2MSFTNGP15.phx.gbl...
> If you use the Native mode I don't think you can do it but have you
actually[vbcol=seagreen]
> tried? BCP out a few thousand rows, create anew table and Bulk Insert it
> back in. If native wont work I am pretty sure char mode will.
> --
> Andrew J. Kelly SQL MVP
>
> "james" <kush@.brandes.com> wrote in message
> news:%23j3fNPKrFHA.3640@.tk2msftngp13.phx.gbl...
insert[vbcol=seagreen]
won't[vbcol=seagreen]
possible.[vbcol=seagreen]
command.
>
|||Yes if you change the columns around you need to use a format file. But I
don't see why it wont work with the format file.
Andrew J. Kelly SQL MVP
"james" <kush@.brandes.com> wrote in message
news:%23Z9GTdWrFHA.2624@.TK2MSFTNGP15.phx.gbl...
>I have tried both native and character mode and both erroed out. Which is
> kind of expected, since schema got changed, the program doesn't have any
> way
> of knowing which column in datafile (native or char) maps to which column
> in
> table.
> I haven't tried format file so far, which is what I am going to do next
> and
> see if I can alter format file and make this thing work.
> Thanks for your Input.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uWZ$MoLrFHA.2624@.TK2MSFTNGP15.phx.gbl...
> actually
> insert
> won't
> possible.
> command.
>
|||With the format file both native and character mode worked. Thanks again for
your time.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23ov1hlWrFHA.3352@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Yes if you change the columns around you need to use a format file. But I
> don't see why it wont work with the format file.
> --
> Andrew J. Kelly SQL MVP
>
> "james" <kush@.brandes.com> wrote in message
> news:%23Z9GTdWrFHA.2624@.TK2MSFTNGP15.phx.gbl...
column[vbcol=seagreen]
it[vbcol=seagreen]
the[vbcol=seagreen]
bulk[vbcol=seagreen]
schema/import
>

bulk insert question

Hi! We are in the process of adding few columns to our large tables (200
million rows) and altering few columns from varchar to char. One of the
option I am thinking is to bcp data out, change the schema and bulk insert
data in. Looks like with either bcp.exe or bulk insert command, you won't be
able to load the data in, if the schema of table get changed. Is this true?
I know that I can use DTS export/import to do this task but since bulk
insert is the fastest method I would like to try that option if possible.
Besides, Bulk insert I could also change schema with Alter table command. I
don't know if thats better than unloading/change/reload method that I
mentioned above.
I would appreiciate it, if anyone who have export/change schema/import large
table, give me some direction here.
thanksIf you use the Native mode I don't think you can do it but have you actually
tried? BCP out a few thousand rows, create anew table and Bulk Insert it
back in. If native wont work I am pretty sure char mode will.
Andrew J. Kelly SQL MVP
"james" <kush@.brandes.com> wrote in message
news:%23j3fNPKrFHA.3640@.tk2msftngp13.phx.gbl...
> Hi! We are in the process of adding few columns to our large tables (200
> million rows) and altering few columns from varchar to char. One of the
> option I am thinking is to bcp data out, change the schema and bulk insert
> data in. Looks like with either bcp.exe or bulk insert command, you won't
> be
> able to load the data in, if the schema of table get changed. Is this
> true?
> I know that I can use DTS export/import to do this task but since bulk
> insert is the fastest method I would like to try that option if possible.
> Besides, Bulk insert I could also change schema with Alter table command.
> I
> don't know if thats better than unloading/change/reload method that I
> mentioned above.
> I would appreiciate it, if anyone who have export/change schema/import
> large
> table, give me some direction here.
> thanks
>|||I have tried both native and character mode and both erroed out. Which is
kind of expected, since schema got changed, the program doesn't have any way
of knowing which column in datafile (native or char) maps to which column in
table.
I haven't tried format file so far, which is what I am going to do next and
see if I can alter format file and make this thing work.
Thanks for your Input.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uWZ$MoLrFHA.2624@.TK2MSFTNGP15.phx.gbl...
> If you use the Native mode I don't think you can do it but have you
actually
> tried? BCP out a few thousand rows, create anew table and Bulk Insert it
> back in. If native wont work I am pretty sure char mode will.
> --
> Andrew J. Kelly SQL MVP
>
> "james" <kush@.brandes.com> wrote in message
> news:%23j3fNPKrFHA.3640@.tk2msftngp13.phx.gbl...
insert[vbcol=seagreen]
won't[vbcol=seagreen]
possible.[vbcol=seagreen]
command.[vbcol=seagreen]
>|||Yes if you change the columns around you need to use a format file. But I
don't see why it wont work with the format file.
Andrew J. Kelly SQL MVP
"james" <kush@.brandes.com> wrote in message
news:%23Z9GTdWrFHA.2624@.TK2MSFTNGP15.phx.gbl...
>I have tried both native and character mode and both erroed out. Which is
> kind of expected, since schema got changed, the program doesn't have any
> way
> of knowing which column in datafile (native or char) maps to which column
> in
> table.
> I haven't tried format file so far, which is what I am going to do next
> and
> see if I can alter format file and make this thing work.
> Thanks for your Input.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uWZ$MoLrFHA.2624@.TK2MSFTNGP15.phx.gbl...
> actually
> insert
> won't
> possible.
> command.
>|||With the format file both native and character mode worked. Thanks again for
your time.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23ov1hlWrFHA.3352@.TK2MSFTNGP14.phx.gbl...
> Yes if you change the columns around you need to use a format file. But I
> don't see why it wont work with the format file.
> --
> Andrew J. Kelly SQL MVP
>
> "james" <kush@.brandes.com> wrote in message
> news:%23Z9GTdWrFHA.2624@.TK2MSFTNGP15.phx.gbl...
column[vbcol=seagreen]
it[vbcol=seagreen]
the[vbcol=seagreen]
bulk[vbcol=seagreen]
schema/import[vbcol=seagreen]
>sql

bulk insert question

Hi! We are in the process of adding few columns to our large tables (200
million rows) and altering few columns from varchar to char. One of the
option I am thinking is to bcp data out, change the schema and bulk insert
data in. Looks like with either bcp.exe or bulk insert command, you won't be
able to load the data in, if the schema of table get changed. Is this true?
I know that I can use DTS export/import to do this task but since bulk
insert is the fastest method I would like to try that option if possible.
Besides, Bulk insert I could also change schema with Alter table command. I
don't know if thats better than unloading/change/reload method that I
mentioned above.
I would appreiciate it, if anyone who have export/change schema/import large
table, give me some direction here.
thanksIf you use the Native mode I don't think you can do it but have you actually
tried? BCP out a few thousand rows, create anew table and Bulk Insert it
back in. If native wont work I am pretty sure char mode will.
--
Andrew J. Kelly SQL MVP
"james" <kush@.brandes.com> wrote in message
news:%23j3fNPKrFHA.3640@.tk2msftngp13.phx.gbl...
> Hi! We are in the process of adding few columns to our large tables (200
> million rows) and altering few columns from varchar to char. One of the
> option I am thinking is to bcp data out, change the schema and bulk insert
> data in. Looks like with either bcp.exe or bulk insert command, you won't
> be
> able to load the data in, if the schema of table get changed. Is this
> true?
> I know that I can use DTS export/import to do this task but since bulk
> insert is the fastest method I would like to try that option if possible.
> Besides, Bulk insert I could also change schema with Alter table command.
> I
> don't know if thats better than unloading/change/reload method that I
> mentioned above.
> I would appreiciate it, if anyone who have export/change schema/import
> large
> table, give me some direction here.
> thanks
>|||I have tried both native and character mode and both erroed out. Which is
kind of expected, since schema got changed, the program doesn't have any way
of knowing which column in datafile (native or char) maps to which column in
table.
I haven't tried format file so far, which is what I am going to do next and
see if I can alter format file and make this thing work.
Thanks for your Input.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uWZ$MoLrFHA.2624@.TK2MSFTNGP15.phx.gbl...
> If you use the Native mode I don't think you can do it but have you
actually
> tried? BCP out a few thousand rows, create anew table and Bulk Insert it
> back in. If native wont work I am pretty sure char mode will.
> --
> Andrew J. Kelly SQL MVP
>
> "james" <kush@.brandes.com> wrote in message
> news:%23j3fNPKrFHA.3640@.tk2msftngp13.phx.gbl...
> > Hi! We are in the process of adding few columns to our large tables (200
> > million rows) and altering few columns from varchar to char. One of the
> > option I am thinking is to bcp data out, change the schema and bulk
insert
> > data in. Looks like with either bcp.exe or bulk insert command, you
won't
> > be
> > able to load the data in, if the schema of table get changed. Is this
> > true?
> > I know that I can use DTS export/import to do this task but since bulk
> > insert is the fastest method I would like to try that option if
possible.
> > Besides, Bulk insert I could also change schema with Alter table
command.
> > I
> > don't know if thats better than unloading/change/reload method that I
> > mentioned above.
> > I would appreiciate it, if anyone who have export/change schema/import
> > large
> > table, give me some direction here.
> >
> > thanks
> >
> >
>|||Yes if you change the columns around you need to use a format file. But I
don't see why it wont work with the format file.
--
Andrew J. Kelly SQL MVP
"james" <kush@.brandes.com> wrote in message
news:%23Z9GTdWrFHA.2624@.TK2MSFTNGP15.phx.gbl...
>I have tried both native and character mode and both erroed out. Which is
> kind of expected, since schema got changed, the program doesn't have any
> way
> of knowing which column in datafile (native or char) maps to which column
> in
> table.
> I haven't tried format file so far, which is what I am going to do next
> and
> see if I can alter format file and make this thing work.
> Thanks for your Input.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uWZ$MoLrFHA.2624@.TK2MSFTNGP15.phx.gbl...
>> If you use the Native mode I don't think you can do it but have you
> actually
>> tried? BCP out a few thousand rows, create anew table and Bulk Insert it
>> back in. If native wont work I am pretty sure char mode will.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "james" <kush@.brandes.com> wrote in message
>> news:%23j3fNPKrFHA.3640@.tk2msftngp13.phx.gbl...
>> > Hi! We are in the process of adding few columns to our large tables
>> > (200
>> > million rows) and altering few columns from varchar to char. One of the
>> > option I am thinking is to bcp data out, change the schema and bulk
> insert
>> > data in. Looks like with either bcp.exe or bulk insert command, you
> won't
>> > be
>> > able to load the data in, if the schema of table get changed. Is this
>> > true?
>> > I know that I can use DTS export/import to do this task but since bulk
>> > insert is the fastest method I would like to try that option if
> possible.
>> > Besides, Bulk insert I could also change schema with Alter table
> command.
>> > I
>> > don't know if thats better than unloading/change/reload method that I
>> > mentioned above.
>> > I would appreiciate it, if anyone who have export/change schema/import
>> > large
>> > table, give me some direction here.
>> >
>> > thanks
>> >
>> >
>>
>|||With the format file both native and character mode worked. Thanks again for
your time.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23ov1hlWrFHA.3352@.TK2MSFTNGP14.phx.gbl...
> Yes if you change the columns around you need to use a format file. But I
> don't see why it wont work with the format file.
> --
> Andrew J. Kelly SQL MVP
>
> "james" <kush@.brandes.com> wrote in message
> news:%23Z9GTdWrFHA.2624@.TK2MSFTNGP15.phx.gbl...
> >I have tried both native and character mode and both erroed out. Which is
> > kind of expected, since schema got changed, the program doesn't have any
> > way
> > of knowing which column in datafile (native or char) maps to which
column
> > in
> > table.
> > I haven't tried format file so far, which is what I am going to do next
> > and
> > see if I can alter format file and make this thing work.
> > Thanks for your Input.
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:uWZ$MoLrFHA.2624@.TK2MSFTNGP15.phx.gbl...
> >> If you use the Native mode I don't think you can do it but have you
> > actually
> >> tried? BCP out a few thousand rows, create anew table and Bulk Insert
it
> >> back in. If native wont work I am pretty sure char mode will.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "james" <kush@.brandes.com> wrote in message
> >> news:%23j3fNPKrFHA.3640@.tk2msftngp13.phx.gbl...
> >> > Hi! We are in the process of adding few columns to our large tables
> >> > (200
> >> > million rows) and altering few columns from varchar to char. One of
the
> >> > option I am thinking is to bcp data out, change the schema and bulk
> > insert
> >> > data in. Looks like with either bcp.exe or bulk insert command, you
> > won't
> >> > be
> >> > able to load the data in, if the schema of table get changed. Is this
> >> > true?
> >> > I know that I can use DTS export/import to do this task but since
bulk
> >> > insert is the fastest method I would like to try that option if
> > possible.
> >> > Besides, Bulk insert I could also change schema with Alter table
> > command.
> >> > I
> >> > don't know if thats better than unloading/change/reload method that I
> >> > mentioned above.
> >> > I would appreiciate it, if anyone who have export/change
schema/import
> >> > large
> >> > table, give me some direction here.
> >> >
> >> > thanks
> >> >
> >> >
> >>
> >>
> >
> >
>

BULK INSERT PROBLEM

I am trying to BULK INSERT a unix data file (.csv) into temperary table, then replace all CHAR(13) into CHAR(10) or '\n'. In Classic ASP, I was using Replace(text,char(13),vbcrlf), but now in MS SQL, so I use Replace(@.text,char(13),'\n').
After finished converting, I will then BULK INSERT into a load_table with rowterminator, firstrow and fieldterminator. But, I got this Error:

============================================

Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].
The statement has been terminated.

==============CODE========================

DECLARE @.sLoadFileLayout AS NVARCHAR(1000)
DECLARE @.SQL AS NVARCHAR(1000)
DECLARE @.sFieldName AS NVARCHAR(1000)
DECLARE @.iRow AS INT
DECLARE @.TextLine AS NVARCHAR(4000)
DECLARE @.fieldTerminator AS char(3)

SET @.sLoadFileLayout = 'C:/folder/file.CSV'

CREATE TABLE #textfile (line varchar(8000))
SET @.SQL = 'BULK INSERT #textfile FROM ''' + @.sLoadFileLayout + ''''
EXEC sp_executesql @.SQL

DECLARE TableMap CURSOR FOR SELECT * FROM #textfile
OPEN TableMap
FETCH NEXT FROM TableMap INTO @.TextLine

SET @.iRow = 0
SET @.SQL = ''
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.iRow = @.iRow + 1
IF @.iRow >= 1
BEGIN
IF @.SQL <> '' SET @.SQL = @.SQL + ','
SET @.sFieldName = REPLACE(@.TextLine, char(10),'\n')
SET @.SQL = @.SQL + ' ' + @.sFieldName
END
FETCH NEXT FROM TableMap INTO @.TextLine
END

CLOSE TableMap
DEALLOCATE TableMap

DROP TABLE #textfile

BULK INSERT MARKETING.DBO.LOAD_Table FROM #textfile WITH (DATAFILETYPE='char',FIELDTERMINATOR=',',FIRSTROW=4,ROWTERMINATOR='\n')

Strange. I tested your script and got the same rows in table #textfile and Load_Table. Seems your code works fine I'm using SQL2000, here is the content of the csv file:

One user can only have one user instance.
Replication will be disabled.
User Instance does not support SQL Server Authentication. Only Windows Authentication is supported.
The network protocol support for user instances is local named pipes only.
The user instance shares the registry entries of the parent instance.
There is no support for user instance with native code. This feature is only supported with ADO .NET.
WMI Provider for Server Events will not be supported on the dynamically spawned user instances. This should still work on the parent SQL Server Express instance. For more information on WMI provider see WMI Provider for Server Events in SQL Server 2005 Books Online.

BTW, I think you do not need to replace char(10) or char(13) with '\n', because SQL does accept carriage return.

BULK INSERT - escape " - .fmt

Hi All,
Simple table:
TABLE:
create table employee (
name char(20),
title char(120)
)
DATAFILE(.csv):
name;title
Gustavo;"Marketing Assistant"
Catherine;"Engineering Manager"
I'd like use BULK INSERT command to insert this data. Tell me please,
how should look formatfile (.fmt) for this example (SQL 2005). I need
data after BULK INSERT looks like this:
name title
-- --
Gustavo Marketing Assistant
Catherine Engineering Manager
NOT like this:
name title
-- --
Gustavo "Marketing Assistant"
Catherine "Engineering Manager"
So, " should be escape.
Thank you
--
RegardsHello,
I've done this, it's almost finished. You can tray this:
/
***************************************************************************************************/
--table
CREATE TABLE [dbo].[ImportTest](
[fname] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[lname] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[organization] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[address] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[zip] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[city] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[state] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[email] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
--data file (ImportTest.csv), REMEMBER at the end you HAVE TO add
ENTER
fname;lname;organization;address;zip;city;state;email;
Shirley;Birosik;;;90013;"Los Angeles";"CA";"sbirosik@.gmail.com";
Dennis;Eschen;;;94920;"Alhambra";"CA";"de@.hotmail.com";
Valerie;Chambers;;;91803;"Long Beach";"CA";"vweber@.verizon.net";
--fmt file(ImportTest.Fmt), add ENTER at the end
9.0
8
1 SQLCHAR 0 510 ";" 1
fname Latin1_General_CI_AS
2 SQLCHAR 0 510 ";" 2
lname Latin1_General_CI_AS
3 SQLCHAR 0 510 ";" 3
organization Latin1_General_CI_AS
4 SQLCHAR 0 510 ";" 4
address Latin1_General_CI_AS
5 SQLCHAR 0 510 ";\"" 5
zip Latin1_General_CI_AS
6 SQLCHAR 0 510 "\";\"" 6
city Latin1_General_CI_AS
7 SQLCHAR 0 510 "\";\"" 7
state Latin1_General_CI_AS
8 SQLCHAR 0 510 "\r\n" 8
email Latin1_General_CI_AS
bulk insert ImportTest
from 'd:\bulk\ImportTest.csv'
with (
formatfile = 'd:\bulk\ImportTest.Fmt',
firstrow = 2
)
--(2 row(s) affected)
select*
from ImportTest
Dennis Eschen NULL NULL 94920 Alhambra CA
de@.hotmail.com";
Valerie Chambers NULL NULL 91803 Long Beach CA
vweber@.verizon.net";
/
***************************************************************************************************/
For me now most important is why didn't insert first row (data row,
not column definition)?
When I added quotation mark to column definition works fine, but I
can't modify .csv file, I received it from third part company :( is it
possible add all rows without modification .csv file?
Second, how remove quotation mark and semicolon from last column?
Regards,
anxcomp