none
Problem with Bulk Insert and Datetime Format

    Question

  • Hi there,

     

    I got a few *.csv files which I have to import in a table. The Insert looks like this:

     

    bulk insert actocashdb.dbo.MyTable
       from 'c:\Updates\PLUArtikel_01.csv'
       with ( fieldterminator = ';',
                DATAFILETYPE = 'char',
                tablock,
                codepage = 'ACP'
            )

     

    There is a field which contains a Date and Time in US-Format (e.g. 08/15/2007 03:15:00 PM). Some of my SQL - Servers

    (in fact they are just MSDE) are configured as German language. So, when I try to Insert the File I get a Type Mismatch Error, because the MSDE awaits the Date in German Format (e.g. 15.08.2007 15:15:00). Are there any

    options somewhere so I can Insert this Date? NOTE: It is not possible to reconfigure the MSDE to English.

     

    Any help would be appreciated!

     

    Lars

    Tuesday, February 05, 2008 11:28 AM

Answers

All replies

  • Code Snippet
    SET DATEFORMAT mdy;
    GO
    DECLARE @datevar DATETIME;
    SET @datevar = '12/31/1998';
    SELECT @datevar AS DateVar;
    GO
    -- Result: 1998-12-31 00:00:00.000

     

    This command overrides the default.

    I have not tested it with bulk insert

    but it does not care what the default language is.

    Tuesday, February 05, 2008 3:08 PM
  •  AlunJ wrote:

    Code Snippet
    SET DATEFORMAT mdy;
    GO
    DECLARE @datevar DATETIME;
    SET @datevar = '12/31/1998';
    SELECT @datevar AS DateVar;
    GO
    -- Result: 1998-12-31 00:00:00.000

     

    This command overrides the default.

    I have not tested it with bulk insert

    but it does not care what the default language is.



    cant get this to work with bulk insert

    Im really getting tired of bulk insert, errors after errors with not-so-good error messages.
    In SqlLoader you can define the date format in the control file. what a great idea!

    ugh

    anyway, this is the error message I get:
    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 16 (VO_status_RF_date)
    Thursday, February 14, 2008 1:43 PM
  • Use SSIS

     

    Thursday, February 14, 2008 1:55 PM
  •  AlunJ wrote:
    Use SSIS

     



    SSIS doesnt work with Sql Server 2000
    Thursday, February 14, 2008 1:58 PM
  • I can think of a few ways around it...

     

    Try using bcp instead (bulk insert is notably more picky on dateformats/languages than bcp)

     

    Use a staging table and have the offending column as a varchar, insert to the final destination with normal SQL, where you can convert the date with the proper style.

    Which in this case is: convert(datetime, '08/15/2007 03:15:00 PM', 101)

     

    Set language to english before the bulk insert and back to german when done.

    I think this should also work:

    Create a view and bulk insert into the view.

    In the view, the datecolumn has the above convert for the dates.

     

    =;o)

    /Kenneth

     

    Thursday, February 14, 2008 2:44 PM
  • just use load data infile in mysql .

    follow this link this is for bulk insertion of data into database in fraction of seconds.

    http://dev.mysql.com/doc/refman/4.1/en/load-data.html


    MSDN Forum

    Friday, March 16, 2012 8:26 AM
  • The best to apply is the SSIS Import/Export Wizard:

    http://www.sqlusa.com/bestpractices/ssis-wizard/

    The Wizard is based on GUI and generates a package for you. Run it only once, or save it for future reuse.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012
    Wednesday, March 21, 2012 3:40 PM
    Moderator