How Can I Split Data From STring To a Table?

Anirudh Mukherjee 1 Reputation point
2021-06-26T06:04:54.873+00:00

BULK INSERT dbo.USA FROM 'F:\Torrent Downloads\Data\Demo.txt'

WITH
(
FIELDTERMINATOR = ':',
ROWTERMINATOR = '\n',
TABLOCK,
CODEPAGE = 'US'
);

This is the string
11/9/2015 12:00:00 AM:india.delhi@Stuff .com:12/15

And I want to separately truncate datetime,emailid,dob
How can i do it.
Can you please suggest?

C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,179 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,546 questions
{count} votes

2 answers

Sort by: Most helpful
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-06-28T08:52:24.463+00:00

    Hi @Anirudh Mukherjee ,

    Welcome to Microsoft Q&A!

    Please always use a staging/load table for bulk actions. Then you could process, clean, scrub etc the data before flushing to the real table.

    Please refer below example:

    Use below txt.
    109690-demo.png

    CREATE TABLE dbo.DEMO  
    (DEMO VARCHAR(100))  
      
    BULK INSERT dbo.DEMO FROM 'C:\SQL\Demo.txt'  
    WITH  
    (  
    --FIELDTERMINATOR = ':',  
    ROWTERMINATOR = '\n',  
    --TABLOCK,  
    CODEPAGE = 'US'  
    );  
      
    SELECT * FROM dbo.DEMO  
    

    Output:

    DEMO  
    11/9/2015 12:00:00 AM:india.delhi@gmail.com:12/15  
    11/10/2015 12:00:00 PM:india.delhi1@gmail.com:12/16  
    

    Then insert into USA table.

    CREATE TABLE DBO.USA  
    ([DATETIME] DATETIME  
    ,emailid VARCHAR(100),  
    dob VARCHAR(10))  
      
    INSERT INTO DBO.USA  
    SELECT LEFT(DEMO,CHARINDEX('M:',DEMO))  
    ,SUBSTRING(DEMO,CHARINDEX('M:',DEMO)+2,CHARINDEX('com:',DEMO)-CHARINDEX('M:',DEMO)+1)  
    ,REVERSE(LEFT(REVERSE(DEMO),CHARINDEX(':',REVERSE(DEMO))-1))  
    FROM dbo.DEMO  
      
    SELECT * FROM DBO.USA  
    

    Output:

    DATETIME	emailid	dob  
    2015-11-09 00:00:00.000	india.delhi@gmail.com	12/15  
    2015-11-10 12:00:00.000	india.delhi1@gmail.com	12/16  
    

    If above is not working, please provide your txt and expected output.

    Best regards,
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments No comments

  2. Ronen Ariely 15,096 Reputation points
    2021-06-29T20:30:19.67+00:00

    Good day Anirudh,

    Your file is not well formatted for automatic BULK INSERT to multiple columns since the FIELDTERMINATOR is also part of the content inside the time. Meaning that you use ":" as FIELDTERMINATOR but not any existing of this char is actually start a new column sine you have time like "12:00:00".

    In this case, a great option is to use BULK INSERT into a staging table (temporarily table with one column) - just as @MelissaMa-MSFT explained (+5 points).

    Another option is to use INSERT INTO ... SELECT ... FROM OPENROWSET(BULK...)

    Choosing the best option between these two depend on the amount of data that you need to load, the type of the data and the complexity of the parsing you need to execute.

    There are multiple pros and cons for each option which you will need to examine according to your system. For example, when you use OPENROWSET then you can parse the data on the fly without the need to write it on the disk in staging table first. This mean you gain performance on IO, but when using staging table then you can for example index the data after imported...

    https://learn.microsoft.com/en-us/sql/relational-databases/import-export/import-bulk-data-by-using-bulk-insert-or-openrowset-bulk-sql-server?view=sql-server-ver15

    0 comments No comments