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

Help
,
1

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
1 Like
2

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 :slight_smile:

3

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:

Cheers

4

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.

5

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

6

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.