Modify and Check Data while using Bulk Insert from CSV file - SQL

Question

I'm using method below inserting Data from csv file into SQL.

BULK
INSERT tblMember
FROM 'F:\target.txt'
WITH
(
DATAFILETYPE='widechar',
CODEPAGE = 'ACP',
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n',
ERRORFILE = 'C:\CSVDATA\ErrorRows.csv',
)
GO

I need to do two things. First check if All Chars in Column One of CSV file for each row are only Digit and if yes Insert it. and Also I need to add a specific Word before these chars while inserting.

01 - 123,M,A,USA
02 - H24,N,Z,USA

I need to only insert row one, Because Column One is only Digit numbers '123', and I need to add "D" before this numbers and then insert it into SQL. so we have something like this is SQL after insertion:

"D123","M","A","USA"

Possible?

Answer 1

Lets consider a sample CSV(in C Drive) file target-c.txt which contain four lines of data.(Notice i have use target-c.txt not target.txt)

123,M,A,USA
H24,N,Z,USA
H25,N,V,USA
456,M,U,USA

Now create a Non-XML Format File(in C Drive) named it targetFormat.fmt. and populate the file in following way

9.0  
4  
1       SQLCHAR       0       100     ","     1     Col1               SQL_Latin1_General_CP1_CI_AS  
2       SQLCHAR       0       100     ","     2     Col2               SQL_Latin1_General_CP1_CI_AS  
3       SQLCHAR       0       100       ","     3     Col3               ""  
4       SQLCHAR       0       100     "\r\n"  4     Col4               SQL_Latin1_General_CP1_CI_AS 

Please Be Careful with this formatting.Click this Link if you want to read more about Non-XML Format File.The basic example would be like this. Please change the format file according to your need.(like DataType, ChaterLength etc.)

I have created a sample table tblMember (please change according to your way, like column name , datatype etc. Remember you have to change the targetFormat.fmt file too)

CREATE TABLE tblMember   
   (  
   Col1 nvarchar(50),  
   Col2 nvarchar(50) ,  
   Col3 nvarchar(50) ,   
   Col4 nvarchar(50)   
   );  

Then Use the following query for bulk-insert according to your way(its add a character "D" in front of Col1 with integer value)

INSERT INTO tblMember(Col1,Col2,Col3,Col4)  
(
select 'D'+t1.Col1 AS Col1,t1.Col2,t1.Col3,t1.Col4 
from openrowset(bulk 'C:\target-c.txt'
        , formatfile = 'C:\targetFormat.fmt'
        , firstrow = 1) as t1
where  t1.Col1  not like '%[^0-9]%' --Not Like Letter Number mixed (123, 456)
UNION 
select t1.Col1,t1.Col2,t1.Col3,t1.Col4 
from openrowset(bulk 'C:\target-c.txt'
        , formatfile = 'C:\targetFormat.fmt'
        , firstrow = 1) as t1
where  t1.Col1   like '%[^0-9]%'--Like Letter Number mixed (H24, H25)
)

Now if you select your table you will get this (i have tried and its working fine)

Here is your answer You can ordered the column if you want to. its very easy, just hold the query in a first bracket and order it or format it according your way.