Importing Date data from csv file using bulk insert in SQL Server

Question

I am having trouble importing date data into SQL Server from a csv file using bulk insert.

I'm using the following stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_ImportTestData]
    @Filepath varchar(500), 
    @Pattern varchar(100), 
    @TableName varchar(128), 
    @ViewName varchar(128), 
    @ResetTable bit = 0 
AS
    SET QUOTED_IDENTIFIER OFF

    DECLARE @query varchar(1000)
    DECLARE @numfiles int
    DECLARE @filename varchar(100)
    DECLARE @files TABLE (Name varchar(200) NULL)

    --Delete the contents of the rawData table and let the user know
    IF @ResetTable = 1
    BEGIN
        PRINT 'Emptying table [' + @TableName + ']...'

        EXEC ('DELETE ' + @TableName)
    END

    --Pull a list of the CSV file names from the folder that they're stored in
    SET @query ='master.dbo.xp_cmdshell "dir '+@filepath+@pattern +' /b"'

    INSERT @files(Name) 
        EXEC (@query)

    DECLARE curs_files CURSOR FOR
         SELECT Name 
         FROM @files 
         WHERE Name IS NOT NULL

    --For each CSV file, execute a query
    SET @numfiles = 0

    OPEN curs_files
    FETCH NEXT FROM curs_files INTO @filename

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @numfiles += 1

        -- BULK INSERT each CSV file into the rawData view and update the rawData 
        -- table with the file name and the upload datetime
        SET @query = ('BULK INSERT ' + @ViewName + ' FROM ''' + @Filepath+@filename + ''' WITH(FIRSTROW = 2, FIELDTERMINATOR='','', ROWTERMINATOR=''\n'');'
                + ' UPDATE ' + @TableName
                + ' SET [FileName] = ' + '''' + @filename + ''''
                + ' WHERE [FileName] Is Null;'
                + ' UPDATE ' + @TableName
                + ' SET [UploadDatetime] = ' + '''' + CAST(GETDATE() as nvarchar(1000)) + ''''
                + ' WHERE [UploadDatetime] Is Null;'
    )

    PRINT 'Importing [' + @filename + '] into [' + @TableName + ']...'
    EXEC (@query)

    FETCH NEXT FROM curs_files INTO @filename
END

CLOSE curs_files
DEALLOCATE curs_files

Please note I got this code from http://www.decisivedata.net/blog/import-data-from-multiple-csv-files-using-bulk-insert.

This is my table:

[dbo].[Test]
(
    [Money] [float] NULL,
    [Amount] [varchar](50) NULL,
    [Date] [datetime] NULL,
    [DataID] [int] IDENTITY(1,1) NOT NULL,
    [FileName] [varchar](50) NULL,
    [UploadDatetime] [datetime] NULL
)

This is my view

SELECT Money, Amount, Date
FROM dbo.Test

Here is a sample of one of my csv files:

Money, Amount, Date, 
1043333333.5,4, 01/29/2018
57,4, 01/29/2018
604,3,01/29/2018

Here is my query:

EXEC usp_ImportTestData
         @filepath = 'C:\TestDataCSV\',
         @pattern = '*.csv',
         @TableName = 'Test',
         @ViewName = 'V_Test',
         @ResetTable = 1

When I run the query I get this error message

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 3 (Date)

I have no idea what is wrong! If I try to do a straight bulk insert for just one file it doesn't seem to have any trouble with the date field. If I get rid of the date column it seems to work fine too..

This could be a format error. When you try to put '01/29/2018' into a DATETIME field that expects 'yyyy-MM-dd' will give you this error. If you import the raw data into a table then move it somewhere and reuse the table for more imports, change that column to string, and perform Convert(datetime, stringDate, 101). I found 101 here: learn.microsoft.com/en-us/sql/t-sql/functions/… matches the format your date values are currently in. You could do the convert in your Import function, but I do not see where in the code you provided. — user7396598
– user7396598, Commented Jan 31, 2018 at 18:52
Also, I would change the name of your column from 'Date' to 'someDescDate' so that the column name does not match a keyword if possible — user7396598
– user7396598, Commented Jan 31, 2018 at 18:53

Andy In NC · Accepted Answer · 2018-01-31 18:37:01Z

0

If that is your actual data you posted, note that there is a space included with the date.

Also, not part of your question, avoid FLOAT and REAL data types unless you are counting the number of inches between planets. Those data types are only for numbers that have estimates because they are so big.

answered Jan 31, 2018 at 18:37

Andy In NC

1121 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Importing Date data from csv file using bulk insert in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related