How to manipulate txt file's content update to SQL Table

[bfhuang]

I have a text file include the content like the following,and also have SQL table have the same structure, does anyone know how to loop insert the content to SQL from txt file？and everyday we need to import once,we don’t want to import the same record to the table,Pers.no will have the unique value.Many thanks.

Pers.no.	Personnel number	Name	Cost ctr.	Position/work center key	Gender key	Entry date	IC Number
00000001	AAA	123	1234-5678	Title,Department	Female	9999/12/31	1234567890123456789
00000002	BBB	456	1234-5678	Title,Department	Female	9999/12/31	1234567890123456789
00000003	CCC	789	1234-5678	Title,Department	Female	9999/12/31	1234567890123456789
00000004	DDD	223	1234-5678	Title,Department	Female	9999/12/31	1234567890123456789

[Nithinkrishna]

Hey @bfhuang,

Good to see you here.

Assuming the same format as given,

1. Build a datatable with same schema
2. Read notepad content to a string
3. Split the content by means of Newline gives you array of strings containing rows
4. Iterate through rows and split each row by means of space gives you columns
5. Just add that array to the datatable as data row
6. Insert it to DB.

Hope this helps.

Thanks

[J0ska]

Most simple approach is the following:
1/ create datatable of same structure like the target SQL table
2/ read the txt file into the datatable
3/ use “Insert” activity to insert datatable into SQL table

It is not clear what you mean by the below:

@bfhuang

and everyday we need to import once,we don’t want to import the same record to the table,Pers.no will have the unique value

Cheers

[bfhuang]

Thanks, yes, I use the same way to update the data to SQL table. I don’t want to update the duplicate data to the same table, I only need to increase part, not all the data.

[J0ska]

In cases where I receive data which overlap with dataset already inserted in SQL table I usually use an insert trigger to filter out the duplicate data on database level. It’s easier than dealing with duplicates in application.

Sample below

Cheers

CREATE TRIGGER [dbo].[IO_Trig_INS_P002_PriceList] ON [dbo].[P002_PriceList]
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
– Check for duplicate PriceList. If there is no duplicate, do an INSERT.
IF (NOT EXISTS (SELECT 1 FROM P002_PriceList PL, inserted I
WHERE PL.date = I.date
AND PL.warehouse = I.warehouse
AND PL.product = I.product))
INSERT INTO P002_PriceList
SELECT date, warehouse, product, price, file_name
FROM inserted
ELSE
–If there is a duplicate, change to UPDATE so that there will not
–be a duplicate key violation error.
UPDATE P002_PriceList
SET price = I.price, file_name = I.file_name
FROM P002_PriceList PL, inserted I
WHERE PL.date = I.date
AND PL.warehouse = I.warehouse
AND PL.product = I.product
END