Bulk Insert 1

[mydisney]

I'm doing a bulk insert from a text file and get error:

Bulk insert: Unexpected end-of-file (EOF) encountered in data file

Here is my sp:

CREATE PROCEDURE MyJobs
@PathFileName varchar(100)

as

If Exists(Select * From Information_Schema.Tables Where Table_Type = 'Base Table' And Table_Name = 'MyJobTable')
Begin
Drop Table MyJobTable
End

create table MyJobTable
(
job_date datetime,
job_number char(15),
job_phase char(15),
qty_delivered decimal(6,2),
qty_received decimal(6,2),
plant_id char (5)
)

DECLARE @SQL varchar(2000)
SET @SQL = "BULK INSERT MyJobTable FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = '"",""' ) "
EXEC (@SQL)
GO

Here is my text file:

11/28/2007,3004245 ,05201 ,408
11/28/2007,3006204 ,05101 ,330
11/28/2007,3006204 ,05401 ,135

 

[gmmastros]

There may be several problems here.

Your text file has 4 columns of date, but the table you are loading it in to has 6 columns. It's important for them to match.

Also, your @SQL line should look like this...

[COLOR=blue]SET[/color] @SQL = [COLOR=red]'BULK INSERT MyJobTable  FROM '''[/color] + @PathFileName + [COLOR=red]''' WITH (FIELDTERMINATOR = '','' ) '[/color]

Notice the changes I made from quote to single-quote.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

One more thing...

When posting code, it's better to enclose to code in TGML tags. For example:

[ignore]

This is my code line 1
This is my code line 1

[/ignore]

Will display as...

This is my code line 1
This is my code line 1

It's not necessary to do this, but it does make the post look better and is easier to notice things like quote vs 2 single quotes " vs. ''

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

And while for this particular data set, it looks ok, I almost never accept a file that is comma separated as data often has internal commas which mess up the fields on import. Usually I insist on using | or ~ as the separator as they are far less likely to be included in the data itself. Just something to think about for the future.


"NOTHING is more important in a database than integrity." ESquared

 

[mydisney]

Do I need a row terminator as well?

 

[gmmastros]

The default row terminator is Carriage Return/Line Feed. Is this what you are using?

If you are using the default, then you do not need to include it.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mydisney]

Here is what I have:

Dim content As String = ticket_date & "\t" & job & "\t" & job_phase & "\t" & tons & "\n"
        IO.File.AppendAllText(absolutePath, content + Environment.NewLine)
[code]

My sp:

[code]
CREATE PROCEDURE MyJobs
@PathFileName varchar(100),
@Return int =0 output
as
If Exists(Select * From Information_Schema.Tables Where Table_Type = 'Base Table' And Table_Name = 'MyJobTable')
  Begin
    Drop Table MyJobTable
  End

create table MyJobTable 
(
job_date datetime, 
job_number char(15),
job_phase char(15),
qty_delivered decimal(6,2)
)
DECLARE @SQL varchar(2000)
SET @SQL = 'BULK INSERT MyJobTable  FROM ''' + @PathFileName + ''' WITH (FIELDTERMINATOR = ''\t'' ,ROWTERMINATOR = "\n") '
EXEC (@SQL)
RETURN @Return
GO
[code]

Get error:

Incorrect syntax near '\n'

 

[gmmastros]

Your code blocks didn't work because you need to end them with [ignore][[/ignore][!]/[/!]code]

You need to STOP using the QUOTE " symbol. In fact, in SQL Server, you should NEVER use them. EVER. All it will do is cause problems. There are times when you CAN use them as an acceptable replacement for another symbol, but it's best to just not use them at all. Sorry if that sounded a little harsh. It's just frustrating because in my first response, I show you the proper syntax which essentially amounted to removing the quote symbols, and then come back with a follow up question that has more quotes added back to it.

SET @SQL = 'BULK INSERT MyJobTable  FROM ''' + @PathFileName + ''' WITH (FIELDTERMINATOR = ''\t'' ,ROWTERMINATOR = ''\n'') '

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mydisney]

I still get "Unexpected EOF encountered in data file"

VB:

Dim content As String = ticket_date & "\t" & job & "\t" & job_phase & "\t" & tons & vbCr
IO.File.AppendAllText(absolutePath, content + Environment.NewLine)

SP:

CREATE PROCEDURE MyJobs
@PathFileName varchar(100),
@Return int =0 output

as

If Exists(Select * From Information_Schema.Tables Where Table_Type = 'Base Table' And Table_Name = 'MyJobTable')
  Begin
    Drop Table MyJobTable
  End
create table MyJobTable 
(
job_date datetime, 
job_number char(15),
job_phase char(15),
qty_delivered decimal(6,2)
)
DECLARE @SQL varchar(2000)
SET @SQL = 'BULK INSERT MyJobTable  FROM ''' + @PathFileName + ''' WITH (FIELDTERMINATOR = ''\t'') '
EXEC (@SQL)
RETURN @Return
GO

 

[gmmastros]

Try...

Dim content As String = ticket_date & "\t" & job & "\t" & job_phase & "\t" & tons & [!]vbCrLf[/!]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mydisney]

I tried that and get same error, the "vbCrLf" gives me blank lines in the data file. Should I try to write a blank line with "vbCr" as content after I have filled all mydata?