locked
BULK INSERT brings no results RRS feed

  • Question

  • Greetings: 

    I've been looking for an answer to this issue for at least 10 straight hours on different forums and no answer.

    Issue: I run my script to insert data from a csv file using BULK INSERT. I've used both methods found (with and without the .fmt file) and had the same results. This is my query:

    ---- Creates the table where the file will be imported
    CREATE TABLE [dbo].[ORCPS_BATCH_TEMP](
    [BATCH_ID] [numeric](15, 0) NOT NULL,
    [CANDIDATE_ID] [numeric](15, 0) NULL,
    [ATTEMPT_ID] [numeric](15, 0) NULL,
    [PROFESSIONAL_PROFILE] [varchar](50) NULL,
    [BOARD] [numeric](15, 0) NULL,
    [PROFESSION] [numeric](15, 0) NULL,
    [FIRST_NAME] [varchar](50) NULL,
    [MIDDLE_NAME] [varchar](50) NULL,
    [LAST_NAME] [varchar](50) NULL,
    [MOTHERS_MAIDEN_NAME] [varchar](50) NULL,
    [SSN_DIGITS] [varchar](4) NULL,
    [EMAIL] [varchar](75) NULL,
    [GENDER] [varchar](10) NULL,
    [BIRTH_DATE] [datetime] NULL,
    [FOREIGN_CITIZEN] [bit] NULL,
    [EDUCATION] [varchar](300) NULL,
    [EXAM_TYPE] [varchar](50) NULL,
    [SCORE] [numeric](15, 0) NULL,
    [TOTAL] [numeric](15, 0) NULL
    )

    ---- Will insert the rows from the archive to the database
    INSERT INTO [dbo].[ORCPS_BATCH_TEMP] (
    [BATCH_ID],
    --[CANDIDATE_ID] is not included in the insert because is not an existing column in the import file
    [ATTEMPT_ID],
    [PROFESSIONAL_PROFILE],
    [BOARD],
    [PROFESSION],
    [FIRST_NAME],
    [MIDDLE_NAME],
    [LAST_NAME],
    [MOTHERS_MAIDEN_NAME],
    [SSN_DIGITS],
    [EMAIL],
    [GENDER],
    [BIRTH_DATE],
    [FOREIGN_CITIZEN],
    [EDUCATION],
    [EXAM_TYPE],
    [SCORE],
    [TOTAL]
    )
    SELECT 
    [BATCH_ID],
    [ATTEMPT_ID],
    [PROFESSIONAL_PROFILE],
    [BOARD],
    [PROFESSION],
    [FIRST_NAME],
    [MIDDLE_NAME],
    [LAST_NAME],
    [MOTHERS_MAIDEN_NAME],
    [SSN_DIGITS],
    [EMAIL],
    [GENDER],
    [BIRTH_DATE],
    [FOREIGN_CITIZEN],
    [EDUCATION],
    [EXAM_TYPE],
    [SCORE],
    [TOTAL]

    FROM OPENROWSET (BULK 'C:\Users\Public\Desktop\batch_102_20121029.csv', -- import file path
    FORMATFILE = 'C:\Users\Public\Desktop\BatchFormat.fmt', -- format file path
    FIRSTROW = 2 ) AS Batch -- will begin reading at the second row, since first row is the csv header

    -- Other method of inserting the import batch file without using the format file
    --BULK INSERT ORCPS_BATCH_TEMP
    --FROM 'C:\batch_102_20121029.csv'
    --WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' , FIRSTROW = 2 )--,  FORMATFILE = 'C:\Users\Public\Desktop\BatchFormat.fmt')-- will skip the first row, since it's the header.
    --GO


    SELECT * FROM ORCPS_BATCH_TEMP

    THIS IS THE .fmt FILE

    10.0
    18
    1 SQLCHAR 0 15 "," 1 BATCH_ID SQL_Latin1_General_CP1_CI_AS
    2 SQLCHAR 0 15 "," 3 ATTEMPT_ID SQL_Latin1_General_CP1_CI_AS
    3 SQLCHAR 0 50 "," 4 PROFESSIONAL_PROFILE SQL_Latin1_General_CP1_CI_AS
    4 SQLCHAR 0 15 "," 5 BOARD SQL_Latin1_General_CP1_CI_AS
    5 SQLCHAR 0 15 "," 6 PROFESSION SQL_Latin1_General_CP1_CI_AS
    6 SQLCHAR 0 50 "," 7 FIRST_NAME SQL_Latin1_General_CP1_CI_AS
    7 SQLCHAR 0 50 "," 8 MIDDLE_NAME SQL_Latin1_General_CP1_CI_AS
    8 SQLCHAR 0 50 "," 9 LAST_NAME SQL_Latin1_General_CP1_CI_AS
    9 SQLCHAR 0 50 "," 10 MOTHERS_MAIDEN_NAME SQL_Latin1_General_CP1_CI_AS
    10 SQLCHAR 0 4 "," 11 SSN_DIGITS SQL_Latin1_General_CP1_CI_AS
    11 SQLCHAR 0 75 "," 12 EMAIL SQL_Latin1_General_CP1_CI_AS
    12 SQLCHAR 0 10 "," 13 GENDER SQL_Latin1_General_CP1_CI_AS
    13 SQLDATETIME 0 8 "," 14 BIRTH_DATE SQL_Latin1_General_CP1_CI_AS
    14 SQLCHAR 0 1 "," 15 FOREIGN_CITIZEN SQL_Latin1_General_CP1_CI_AS
    15 SQLCHAR 0 300 "," 16 EDUCATION SQL_Latin1_General_CP1_CI_AS
    16 SQLCHAR 0 50 "," 17 EXAM_TYPE SQL_Latin1_General_CP1_CI_AS
    17 SQLCHAR 0 15 "," 18 SCORE SQL_Latin1_General_CP1_CI_AS
    18 SQLCHAR 0 15 "\r\n" 19 TOTAL SQL_Latin1_General_CP1_CI_AS

    THIS IS PART OF THE .csv FILE 

    BATCH_ID,ATTEMPT_ID,PROFESSIONAL_PROFILE,BOARD,PROFESSION,FIRST_NAME,MIDDLE_NAME,LAST_NAME,MOTHERS_MAIDEN_NAME,SSN_DIGITS,EMAIL,GENDER,BIRTH_DATE,FOREIGN_CITIZEN,EDUCATION,EXAM_TYPE,SCORE,TOTAL
    102,85,2012-SAL-0014367,12,35,Adamaris,,Villa,Rios,0000,villanueva000000@yahoo.com,F,22-07-1900,false,Univercidad Metropolitana de Aguadilla - Grado Asociado:Dr. Efrain Sanchez Hidalgo - Escuela Superior secundaria o equivalente,EX_REV_ENF_ASO,,
    102,86,2012-SAL-991256,12,35,Kamyla ,Marlyza,Collazo,Silva,5000,kamy00000@msn.com,F,10-08-1900,false,Instituto Tecnologico de Puerto Rico - Grado Asociado,EX_REV_ENF_ASO,,
    102,87,2012-SAL-999999,12,35,MARTA ,JEAN,FIGUEROA,HIDALGO,0000,testingmail@yahoo.com,F,10-08-1900,false,INTERAMERICANA - Grado Asociado:PABLO COLON BERDECIA  - Escuela Superior secundaria o equivalente,EX_REV_ENF_ASO,,

    I ALWAYS get 0 rows affected after running the script, no errors.

    Permissions on the folder are all granted. I have admin permissions on the database. 

    What am I missing? What do I do?


    Friday, November 2, 2012 6:36 PM

Answers

  • Hi,

    The 2nd bulk insert will work if you change the data as

    1)FOREIGN_CITIZEN column is a bit type and it expects 0 or 1 in place of False and True

    2)BIRTH_DATE column format as MM/dd/yyyy. in your sample data 22/07/1900 to 07/22/1900. This format I am saying as per my system. You may change as per your system.

    3)Insert a blank column in .csv file after BATCH_ID as CANDIDATE_ID

    4)Close the .csv file and then execute the query.

    BULK INSERT ORCPS_BATCH_TEMP
    FROM 'C:\batch_102_20121029.csv'
    WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' , FIRSTROW = 2 )
    Thanks

    Johnson



    • Edited by Johnson T A Tuesday, November 6, 2012 4:14 PM
    • Proposed as answer by Papy Normand Tuesday, November 6, 2012 10:49 PM
    • Marked as answer by Iric Wen Monday, November 12, 2012 8:41 AM
    Tuesday, November 6, 2012 4:09 PM
  • I have already given working model for your 2nd method. Your first method was successed by following steps.

    • created table using your CREATE SQL
    • created a .csv file with your data.
    • created format file using

    E:\>bcp yourDatabase.dbo.orcps_batch_temp format nul -c -t, -f BatchFormatter.fmt -S YourSQLInstance -T

    I got format file content as

    10.0
    19
    1       SQLCHAR             0       41      ","      1     BATCH_ID                                     ""
    2       SQLCHAR             0       41      ","      2     CANDIDATE_ID                                 ""
    3       SQLCHAR             0       41      ","      3     ATTEMPT_ID                                   ""
    4       SQLCHAR             0       50      ","      4     PROFESSIONAL_PROFILE                         SQL_Latin1_General_CP1_CI_AS
    5       SQLCHAR             0       41      ","      5     BOARD                                        ""
    6       SQLCHAR             0       41      ","      6     PROFESSION                                   ""
    7       SQLCHAR             0       50      ","      7     FIRST_NAME                                   SQL_Latin1_General_CP1_CI_AS
    8       SQLCHAR             0       50      ","      8     MIDDLE_NAME                                  SQL_Latin1_General_CP1_CI_AS
    9       SQLCHAR             0       50      ","      9     LAST_NAME                                    SQL_Latin1_General_CP1_CI_AS
    10      SQLCHAR             0       50      ","      10    MOTHERS_MAIDEN_NAME                          SQL_Latin1_General_CP1_CI_AS
    11      SQLCHAR             0       4       ","      11    SSN_DIGITS                                   SQL_Latin1_General_CP1_CI_AS
    12      SQLCHAR             0       75      ","      12    EMAIL                                        SQL_Latin1_General_CP1_CI_AS
    13      SQLCHAR             0       10      ","      13    GENDER                                       SQL_Latin1_General_CP1_CI_AS
    14      SQLCHAR             0       24      ","      14    BIRTH_DATE                                   ""
    15      SQLCHAR             0       3       ","      15    FOREIGN_CITIZEN                              ""
    16      SQLCHAR             0       300     ","      16    EDUCATION                                    SQL_Latin1_General_CP1_CI_AS
    17      SQLCHAR             0       50      ","      17    EXAM_TYPE                                    SQL_Latin1_General_CP1_CI_AS
    18      SQLCHAR             0       41      ","      18    SCORE                                        ""
    19      SQLCHAR             0       41      "\r\n"   19    TOTAL                                        ""

    • You can either Insert a blank column in .csv file after BATCH_ID as CANDIDATE_ID or

           in format file remove CANDIDATE_ID row (row no 2 ) , change the row no of next rows to rowno -1, change the total column count 19 to 18

    • FOREIGN_CITIZEN column is a bit type. so edit .csv file with 0 or 1 in place of False and True
    • BIRTH_DATE column format as MM/dd/yyyy. in your sample data 22/07/1900 to 07/22/1900. This format I am saying as per my system. You may change as per your system.
    • Close the .csv file and .fmt file and then execute the query.
    INSERT INTO [dbo].[ORCPS_BATCH_TEMP] (
    [BATCH_ID],
    --CANDIDATE_ID ,
    [ATTEMPT_ID],
    [PROFESSIONAL_PROFILE],
    [BOARD],
    [PROFESSION],
    [FIRST_NAME],
    [MIDDLE_NAME],
    [LAST_NAME],
    [MOTHERS_MAIDEN_NAME],
    [SSN_DIGITS],
    [EMAIL],
    [GENDER],
    [BIRTH_DATE],
    [FOREIGN_CITIZEN],
    [EDUCATION],
    [EXAM_TYPE],
    [SCORE],
    [TOTAL]
    )
    SELECT 
    [BATCH_ID],
    --[CANDIDATE_ID] ,
    [ATTEMPT_ID],
    [PROFESSIONAL_PROFILE],
    [BOARD],
    [PROFESSION],
    [FIRST_NAME],
    [MIDDLE_NAME],
    [LAST_NAME],
    [MOTHERS_MAIDEN_NAME],
    [SSN_DIGITS],
    [EMAIL],
    [GENDER],
    [BIRTH_DATE],
    [FOREIGN_CITIZEN],
    [EDUCATION],
    [EXAM_TYPE],
    [SCORE],
    [TOTAL]
    FROM OPENROWSET (BULK 'E:\batchFile.csv', 
    FORMATFILE = 'E:\BatchFormatter.fmt', 
    FIRSTROW = 2
    ) AS Batch

    Table has been inserted by the above

    Thanks

    Johnson

                                                                                                                                   _

    Please mark as answer if solved your issue.

    • Marked as answer by Iric Wen Monday, November 12, 2012 8:41 AM
    Thursday, November 8, 2012 4:35 AM

All replies

  • UPDATE: I have tried several examples located in the web, and had the same problem with all of them. 0 rows affected.
    Friday, November 2, 2012 7:47 PM
  • Hi,

    The 2nd bulk insert will work if you change the data as

    1)FOREIGN_CITIZEN column is a bit type and it expects 0 or 1 in place of False and True

    2)BIRTH_DATE column format as MM/dd/yyyy. in your sample data 22/07/1900 to 07/22/1900. This format I am saying as per my system. You may change as per your system.

    3)Insert a blank column in .csv file after BATCH_ID as CANDIDATE_ID

    4)Close the .csv file and then execute the query.

    BULK INSERT ORCPS_BATCH_TEMP
    FROM 'C:\batch_102_20121029.csv'
    WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' , FIRSTROW = 2 )
    Thanks

    Johnson



    • Edited by Johnson T A Tuesday, November 6, 2012 4:14 PM
    • Proposed as answer by Papy Normand Tuesday, November 6, 2012 10:49 PM
    • Marked as answer by Iric Wen Monday, November 12, 2012 8:41 AM
    Tuesday, November 6, 2012 4:09 PM
  • I have already given working model for your 2nd method. Your first method was successed by following steps.

    • created table using your CREATE SQL
    • created a .csv file with your data.
    • created format file using

    E:\>bcp yourDatabase.dbo.orcps_batch_temp format nul -c -t, -f BatchFormatter.fmt -S YourSQLInstance -T

    I got format file content as

    10.0
    19
    1       SQLCHAR             0       41      ","      1     BATCH_ID                                     ""
    2       SQLCHAR             0       41      ","      2     CANDIDATE_ID                                 ""
    3       SQLCHAR             0       41      ","      3     ATTEMPT_ID                                   ""
    4       SQLCHAR             0       50      ","      4     PROFESSIONAL_PROFILE                         SQL_Latin1_General_CP1_CI_AS
    5       SQLCHAR             0       41      ","      5     BOARD                                        ""
    6       SQLCHAR             0       41      ","      6     PROFESSION                                   ""
    7       SQLCHAR             0       50      ","      7     FIRST_NAME                                   SQL_Latin1_General_CP1_CI_AS
    8       SQLCHAR             0       50      ","      8     MIDDLE_NAME                                  SQL_Latin1_General_CP1_CI_AS
    9       SQLCHAR             0       50      ","      9     LAST_NAME                                    SQL_Latin1_General_CP1_CI_AS
    10      SQLCHAR             0       50      ","      10    MOTHERS_MAIDEN_NAME                          SQL_Latin1_General_CP1_CI_AS
    11      SQLCHAR             0       4       ","      11    SSN_DIGITS                                   SQL_Latin1_General_CP1_CI_AS
    12      SQLCHAR             0       75      ","      12    EMAIL                                        SQL_Latin1_General_CP1_CI_AS
    13      SQLCHAR             0       10      ","      13    GENDER                                       SQL_Latin1_General_CP1_CI_AS
    14      SQLCHAR             0       24      ","      14    BIRTH_DATE                                   ""
    15      SQLCHAR             0       3       ","      15    FOREIGN_CITIZEN                              ""
    16      SQLCHAR             0       300     ","      16    EDUCATION                                    SQL_Latin1_General_CP1_CI_AS
    17      SQLCHAR             0       50      ","      17    EXAM_TYPE                                    SQL_Latin1_General_CP1_CI_AS
    18      SQLCHAR             0       41      ","      18    SCORE                                        ""
    19      SQLCHAR             0       41      "\r\n"   19    TOTAL                                        ""

    • You can either Insert a blank column in .csv file after BATCH_ID as CANDIDATE_ID or

           in format file remove CANDIDATE_ID row (row no 2 ) , change the row no of next rows to rowno -1, change the total column count 19 to 18

    • FOREIGN_CITIZEN column is a bit type. so edit .csv file with 0 or 1 in place of False and True
    • BIRTH_DATE column format as MM/dd/yyyy. in your sample data 22/07/1900 to 07/22/1900. This format I am saying as per my system. You may change as per your system.
    • Close the .csv file and .fmt file and then execute the query.
    INSERT INTO [dbo].[ORCPS_BATCH_TEMP] (
    [BATCH_ID],
    --CANDIDATE_ID ,
    [ATTEMPT_ID],
    [PROFESSIONAL_PROFILE],
    [BOARD],
    [PROFESSION],
    [FIRST_NAME],
    [MIDDLE_NAME],
    [LAST_NAME],
    [MOTHERS_MAIDEN_NAME],
    [SSN_DIGITS],
    [EMAIL],
    [GENDER],
    [BIRTH_DATE],
    [FOREIGN_CITIZEN],
    [EDUCATION],
    [EXAM_TYPE],
    [SCORE],
    [TOTAL]
    )
    SELECT 
    [BATCH_ID],
    --[CANDIDATE_ID] ,
    [ATTEMPT_ID],
    [PROFESSIONAL_PROFILE],
    [BOARD],
    [PROFESSION],
    [FIRST_NAME],
    [MIDDLE_NAME],
    [LAST_NAME],
    [MOTHERS_MAIDEN_NAME],
    [SSN_DIGITS],
    [EMAIL],
    [GENDER],
    [BIRTH_DATE],
    [FOREIGN_CITIZEN],
    [EDUCATION],
    [EXAM_TYPE],
    [SCORE],
    [TOTAL]
    FROM OPENROWSET (BULK 'E:\batchFile.csv', 
    FORMATFILE = 'E:\BatchFormatter.fmt', 
    FIRSTROW = 2
    ) AS Batch

    Table has been inserted by the above

    Thanks

    Johnson

                                                                                                                                   _

    Please mark as answer if solved your issue.

    • Marked as answer by Iric Wen Monday, November 12, 2012 8:41 AM
    Thursday, November 8, 2012 4:35 AM