Link to home
Start Free TrialLog in
Avatar of emi_sastra
emi_sastra

asked on

BULK INSERT ERROR

Hi All,

I have below code. I want to log row of data cause error.

INSERT INTO TMWARRANTYNOADMINIST
(
EntryNo
, WarrantyNo
, CategoryCode
, Flag
, Location
, CrtId
, CrtDate
, UpdId
, UpdDate
)
SELECT
EntryNo
, WarrantyNo
, CategoryCode
, '' AS Flag
, 1 AS Location
, 'ADMINIST' AS CrtId
, '29/05/2013 17:26:02' AS CrtDate
, 'ADMINIST' AS UpdId
, '29/05/2013 17:26:02' AS UpdDate
FROM OPENROWSET(BULK 'D:\EMI NEW\COMPILED\VKOOL\DATA\TIRA.CSV', FIRSTROW = 1, FORMATFILE = 'D:\EMI NEW\COMPILED\VKOOL\DATA\FORMAT UPLOAD FILE GARANSI.FMT'
 WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
ERRORFILE = 'D:\EMI NEW\COMPILED\VKOOL\DATA\TIRA.TXT'
) ;
) Temp

Incorrect syntax near the keyword 'WITH'.

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

What is the problem ?

Thank you.
Avatar of Bhavesh Shah
Bhavesh Shah
Flag of India image

Hi,

first try with this much.

INSERT INTO TMWARRANTYNOADMINIST
(
EntryNo
, WarrantyNo
, CategoryCode
, Flag
, Location
, CrtId
, CrtDate
, UpdId
, UpdDate
)
SELECT
EntryNo
, WarrantyNo
, CategoryCode
, '' AS Flag
, 1 AS Location
, 'ADMINIST' AS CrtId
, '29/05/2013 17:26:02' AS CrtDate
, 'ADMINIST' AS UpdId
, '29/05/2013 17:26:02' AS UpdDate
FROM OPENROWSET(BULK 'D:\EMI NEW\COMPILED\VKOOL\DATA\TIRA.CSV', FIRSTROW = 1, FORMATFILE = 'D:\EMI NEW\COMPILED\VKOOL\DATA\FORMAT UPLOAD FILE GARANSI.FMT') ;
) Temp

Open in new window

Avatar of emi_sastra
emi_sastra

ASKER

Hi  Brichsoft,

I've tried your code before. Since it has problem, I want to check which data cause the problem.

Bulk load: An unexpected end of file was encountered in the data file.

The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

What should I do ?

Thank you.
hi,

it seems your file is not in proper format.
can you share your csv and fmt files.
Please see this and above attachment.

Thank you.
CSV-SAMPLE.csv
Hi  Brichsoft,

Any good news  ?

Thank you.
Hi,

sorry for late reply.
your csv file is ; delimited.
and you specified comma(,) as your delimiter in format file.

That was causing problem, i guess.

please checkout this format file and try again.


SELECT
EntryNo
, WarrantyNo
, CategoryCode
, '' AS Flag
, 1 AS Location
, 'ADMINIST' AS CrtId
, '29/05/2013 17:26:02' AS CrtDate
, 'ADMINIST' AS UpdId
, '29/05/2013 17:26:02' AS UpdDate
INTO TEMP_TABLE
FROM OPENROWSET(BULK 'C:\CSV-SAMPLE.csv', FIRSTROW = 1, FORMATFILE = 'c:\FORMAT.FMT') AS A ;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Bhavesh Shah
Bhavesh Shah
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Let me try first.

Thank you.
Hi  Brichsoft,

Yes, the delimiter is not the same with csv.

I wonder why, since it has been used for several months.

Anyway, it works again  now.

Thank you very much for your help.