How Can I Split Data From STring To a Table?

Question

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?

Answer 1

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.

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:******@gmail.com:12/15  
11/10/2015 12:00:00 PM:******@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	******@gmail.com	12/15  
2015-11-10 12:00:00.000	******@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.

Answer 2

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