none
Bulk insert failing RRS feed

  • Question

  • 		CREATE TABLE tempdb.#tempMD
    		(
    			[TableId] [int] NULL,
    			[SourceColumnName] [varchar](100) NULL,
    			[SourceColumnDataType] [varchar](100) NULL,
    			[SourceColumnPrecision] [int] NULL,
    			[SourceColumnScale] [int] NULL,
    			[TargetColumnName] [varchar](100) NULL,
    			[TargetColumnDataType] [varchar](100) NULL,
    			[TargetColumnPrecision] [int] NULL,
    			[TargetColumnScale] [int] NULL
    		);
    
    
    
    		BULK INSERT tempdb.#tempMD FROM 'C:\MD.csv'
    		WITH(
    			FIELDTERMINATOR = ',',
    			ROWTERMINATOR = '0x0a',
    			FIRSTROW=2,
    			KEEPNULLS);

    I dont know why I am getting zero rows I have same number of columns in the md.csv file and no errors.. only zero rows

    Neil



    • Edited by Neilcse Wednesday, July 17, 2019 12:51 PM
    Wednesday, July 17, 2019 12:48 PM

Answers

  • Please upload a file that demonstrates the problem somewhere and post the link. (Don't include the file in a post; we need to see the exact bytes.)

    Without seeing how your file looks there is no way we can help you.


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

    Wednesday, July 17, 2019 9:21 PM
    Moderator
  • Hi Neilcse,

     

    Would you please try to use the following code to bulk insert csv flie:

     

    BULK INSERT tempdb.#tempMD FROM 'C:\MD.csv'    

    WITH     (    

    FIRSTROW = 2,    

    FIELDTERMINATOR = ',',  --CSV field delimiter    

    ROWTERMINATOR = '\n',   --Use to shift the control to next row    

    ERRORFILE = 'C:\MDErrorRows.csv',    

    TABLOCK     )

     

    It will write the rows which aren't loaded into table because of invalid data or format to error file. 

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Thursday, July 18, 2019 8:26 AM

All replies

  • ROWTERMINATOR = '0x0a',			
    Use \n for newline character, see BULK INSERT (Transact-SQL) => ROWTERMINATOR

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Wednesday, July 17, 2019 12:54 PM
    Moderator
  • \n also failed

    Neil

    Wednesday, July 17, 2019 12:56 PM
  • Please upload a file that demonstrates the problem somewhere and post the link. (Don't include the file in a post; we need to see the exact bytes.)

    Without seeing how your file looks there is no way we can help you.


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

    Wednesday, July 17, 2019 9:21 PM
    Moderator
  • Hi Neilcse,

     

    Would you please try to use the following code to bulk insert csv flie:

     

    BULK INSERT tempdb.#tempMD FROM 'C:\MD.csv'    

    WITH     (    

    FIRSTROW = 2,    

    FIELDTERMINATOR = ',',  --CSV field delimiter    

    ROWTERMINATOR = '\n',   --Use to shift the control to next row    

    ERRORFILE = 'C:\MDErrorRows.csv',    

    TABLOCK     )

     

    It will write the rows which aren't loaded into table because of invalid data or format to error file. 

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Thursday, July 18, 2019 8:26 AM