1

My data in below format. I have to insert into sql server table.

agency    NO
booknbr   06000330
bookdttm  2006-07-19 10:56:00
arrdttm   2006-05-07 05:42:00
aj_id     A000009454
casenbr   06006640
off1      619
off2      634
arrplace  DENNYS RESTAURANT
howarr    O
juvstat   
ko        
remarks   
armed     
federal   N

agency    NO
booknbr   06000331
bookdttm  2006-07-24 12:11:00
arrdttm   2006-07-16 16:11:00
aj_id     A000003215
casenbr   06010336
off1      641
off2      
arrplace  219 W GLENCOVE AV
howarr    V
juvstat   
ko        
remarks   
armed     
federal   N

agency    NO
booknbr   06000332
bookdttm  2006-07-25 15:11:00
arrdttm   2006-06-13 22:47:00
aj_id     A000009455
casenbr   06008615
off1      624
off2      
arrplace  113 JULIE DR
howarr    V
juvstat   
ko        
remarks   
armed     
federal   N
4

You can use BULK INSERT and provide the FIELDTERMINATOR and ROWTERMINATOR values like below which will insert the data into the correct columns. The only downside is the data also includes the column name. A simple cursor to clean this up and the data looks fine.

You would want to insert into a staging table as all data types are VARCHAR on insert, then you could insert into a final data table that has correct data types after cleaning.

Create Table to Store Data:

CREATE TABLE dbo.SampleCSVTable
(
    agency    VARCHAR(255),
    booknbr   VARCHAR(255),
    bookdttm  VARCHAR(255),
    arrdttm   VARCHAR(255),
    aj_id     VARCHAR(255),
    casenbr   VARCHAR(255),
    off1      VARCHAR(255),
    off2      VARCHAR(255),
    arrplace  VARCHAR(255),
    howarr    VARCHAR(255),
    juvstat   VARCHAR(255),
    ko        VARCHAR(255),
    remarks   VARCHAR(255),
    armed     VARCHAR(255),
    federal   VARCHAR(255),
)
GO

BULK INSERT the data:

BULK INSERT dbo.SampleCSVTable
FROM 'D:\SQLDATA\SQL01DEV\SampleData.csv'
WITH
(
    FIELDTERMINATOR = '\n',
    ROWTERMINATOR = '\n\n'
)
GO

Clean the data (this cursor loops through each column and removes the column name from the inserted data):

DECLARE @ColName NVARCHAR(255),
    @SqlCmd NVARCHAR(255)

DECLARE C1 CURSOR FOR
    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'SampleCSVTable'

OPEN C1

FETCH NEXT FROM C1 INTO @ColName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SqlCmd = 'UPDATE dbo.SampleCSVTable SET [' + @ColName + '] = LTRIM(RTRIM(REPLACE([' + @ColName + '], ''' + @ColName + ''', '''')));'
    EXEC sp_executesql @SqlCmd

    FETCH NEXT FROM C1 INTO @ColName
END

CLOSE C1

DEALLOCATE C1

Query the data:

SELECT * FROM dbo.SampleCSVTable

Results:

agency  |   booknbr     |   bookdttm            |   arrdttm             |   aj_id       |   casenbr     |   off1    |   off2    |   arrplace            |   howarr  |   juvstat |   ko  |   remarks |   armed   |   federal
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NO      |   06000330    |   2006-07-19 10:56:00 |   2006-05-07 05:42:00 |   A000009454  |   06006640    |   619     |   634     |   DENNYS RESTAURANT   |   O       |           |       |           |           |   N
NO      |   06000331    |   2006-07-24 12:11:00 |   2006-07-16 16:11:00 |   A000003215  |   06010336    |   641     |           |   219 W GLENCOVE AV   |   V       |           |       |           |           |   N
NO      |   06000332    |   2006-07-25 15:11:00 |   2006-06-13 22:47:00 |   A000009455  |   06008615    |   624     |           |   113 JULIE DR        |   V       |           |       |           |           |   N
  • Thank you So much, You save my day:) – B.Muthamizhselvi Oct 15 at 8:05
  • Im getting error for another file. Msg 4832, Level 16, State 1, Line 118 Bulk load: An unexpected end of file was encountered in the data file. Msg 7399, Level 16, State 1, Line 118 The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error. Msg 7330, Level 16, State 2, Line 118 Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)". – B.Muthamizhselvi Oct 15 at 14:38
  • That sounds like an issue with the source file, a hidden character or something that is being interpreted as end-of-file (EOF). Try copying out the data in portions to a new file until you hit the portion that gives the error, and examine the hidden characters. – HandyD Oct 15 at 21:53
  • my first file imported successfully. My seconf file only getting issue. if i open my second file in CSV it shows in upto 5 columns. unable to concate because record is more than 10lakhs. Is it possible to use txt file. – B.Muthamizhselvi Oct 16 at 5:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.