How to insert record from pipe separated text file into DataBase Table

#1

I was tried to insert record as in Insert into DB.zip (22.2 KB) this code but its not working, in this insert statement has following difficulty

  • It added extra space in value because value get from text file

  • If field was empty then we have to set that as ‘NULL’ for that i have to apply IF within For Each, but i already used 4-5 times For-Each loop

  • The another point is in real text file contain 60k - 70k records that mins it took more time to execute

  • At the time of Insert query writing how can you sort-out which value is in single cote beacuse if you apply loop it apply single cote on NULL

  • I also tried bulk upload using ‘Insert’ activity but it cant work

    DB%20connection%20Issue
    DB connection Issue.png1318x422 27.2 KB

Please provide the solution on that or another way (another logic) to insert record from pipe separated text file into DataBase Table
Table structure tatacapital_mis_tbl_openitem.zip (1.2 KB)

#2

Not able to understand your requirement/problem, can you share the file also.

#3

@skini76 Please refer Insert into DB.zip (22.2 KB) this zip file contain Open Item 08.08.2018.txt file which contain Data, That Data I have to insert into Data Base Table tatacapital_mis_tbl_openitem.zip (1.2 KB)

#4

I think it is due to the syntax error in the query, the string values are not enclosed in single quotes.

What I suggest is use a while loop where check each field and construct the query, find the below screen shot, hope it is useful

image
image.png989x394 9.26 KB

image

strSql = strSql + “’” + row.Item (colCounter).ToString + “’,”

Let me know if you have any difficulties in this approach

#5

@skini76 I tried logic but it can’t work, the problem is in while loop did not break, and where did I increment colCounter Operation on PipeStructured file.xaml (47.5 KB) Please check ForEachRow of Insert Query into attached code
It goes into infinite loop

#6

The logic given by be will definitely work, add colCounter = colCounter + 1 inside while loop

#7

@skini76 its working, But it took much more time(ie. 27 min) to execute for 44107 rows please check this Insert into DB.zip (1.3 MB) run this code and check into console
Please provide a solution for Bulk upload
refer tatacapital_mis_tbl_openitem.zip (1.2 KB) this table structure

How to insert data into database table?