0

i have text inside txt file that i need to insert to table i already use bulk insert but there are 2 field terminator in my text text would look like this

006749xxxx161200000572~0039xx9949~0 SUCCESS.UPD 006137xxxx180300010348~0039xx3288~1512259 NOT.UPD-KTA 0039xx3288~1512259 NOT CHANGED 006190xxxx201600010442~0039xx0811~0 FAILED.INPUT-ACCOUNT.NUMBER.INVALID 0039xx0811

i expect to insert to 4 column table like

        cuscode               || norek      ||    a      || status          || description
        006749xxxx161200000572   0039xx9949        0         SUCCESS.UPD         sucess
        006137xxxx180300010348   0039xx3288      1512259     NOT.UPD-KTA...     failed
        006190xxxx201600010442   0039xx0811        0       FAILED.INPUT-...      failed

how to insert my text file to table like above? i already using bulk insert but apperently bulk insert only allowed me useing 1 fieldterminator, and in my text file there are 2 " " and "~"

omik919
  • 29
  • 7
  • Check it out: https://stackoverflow.com/questions/27730419/bulk-insert-with-two-row-terminators – PM 77-1 May 10 '20 at 15:10
  • What happened to `.UPD` in the first row? Seems like you'd be better off uploading the data as a single value into a temp, and splitting them in the database. `BULK INSERT` only accepts one field terminator (as I told you in your now [deleted previous question](https://stackoverflow.com/q/61709803/2029983)). – Thom A May 10 '20 at 15:28
  • I already trying upload file on temp, and i dont know how to spliting them because there are ~and ' ' there – omik919 May 10 '20 at 15:34
  • While reading the file (.csv or .txt) you can create a data table using different delimiters (some rows populated by splitting " " and other rows populated by splitting "~"). After then you can insert into DB using bulk insert. – Bhasyakarulu Kottakota May 10 '20 at 15:46

0 Answers0