Share via

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,648 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. MelissaMa-MSFT 24,191 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
  2. Ronen Ariely 15,186 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