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\TI RA.CSV', FIRSTROW = 1, FORMATFILE = 'D:\EMI NEW\COMPILED\VKOOL\DATA\FO RMAT UPLOAD FILE GARANSI.FMT'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
ERRORFILE = 'D:\EMI NEW\COMPILED\VKOOL\DATA\TI RA.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.
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\TI
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
ERRORFILE = 'D:\EMI NEW\COMPILED\VKOOL\DATA\TI
) ;
) 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.
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.
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.
it seems your file is not in proper format.
can you share your csv and fmt files.
ASKER
ASKER
Hi Brichsoft,
Any good news ?
Thank you.
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.
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 ;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Let me try first.
Thank you.
Thank you.
ASKER
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.
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.
first try with this much.
Open in new window