I have a text file with some data, which must be read in the database. The data inside the file is as follows:
00000A0011 Nike walking shoes 00000A0012 Nikki Store Mo City 12345 B0001EMC Truck 12345B0002BH78545789785 12345B0003NH170323032015060012345C0011 Steve Jones Shu's 12345C0012 Company, Tele -17545812345C 0031212, Fax: 66234544-4812345D0001Mrs Mary Wilson of 19 45781B0001EMC truck 45781B0002BH78545789785 45781B0003NH1703230320150600 45781C0011Steve Jones Shoe's45781C0012Company, Tel17545845781C001312, fax: 66,234,544-48 45781D0001Mrs. From Mary Wilson, 19th,
each line has the same length with 3 squares in it. There are different types of data in each section, here the interpretation of the data is:
TransactionID Char (5) record character (2) record counter number (3) - data data field 011 sender information character (18,012 from space character (18)) - B 0 data field 001 vehicle type char (18) 002 destination variable (05) 002 package Weight number (05) 002 package data number (03) 002 filler characters (05) - C 0 data field 011 receiver address four (18) 012 receiver address four (18) 013 receiver address four (18) - d data field 001 no. Perk person variables (18)
In the database, which I am running at run time and then the user uploads the file, I am trying to read the line, and each block I am trying to read and remove the data from it.
So far, I am not able to separate the transaction, because each transaction should be a separate R ow in the table as well, some data is divided into several blocks, i.e. "C0" field type
Any help would be appreciated. Thanks!
SQL string is not the best place to process, but it does not seem very difficult. I do not think you can do it easily in a set based way but it is advisable to loop over each row on the input. Here are some pseudo-code that should get you started,
@TransactionId = substring (@Input, 1, 5) @CurrentInputPosition = 6, while (@CurrentInputPosition & LT; Lane ( @Input) start @Record (@Input, @CurrentInputPosition + 2, 18) = substring (@Input, @CurrentInputPosition, 2) if (@Record = 'A0') began @SenderInformation = substring @SenderLocation = substring (@Input, @CurrentInputPosition + 2 + 18, 18) @CurrentInputPosition + @CurrentInputPosition + 2 + 18 + 18 - Save A0 start recording data end else if (@Record = 'B0') @VehicleType = substring (@Input, @CurrentInputPosition + 2, 18) @Destination = Substring (@Input, @CurrentInputPosition + 2 + 18, 5) @Package Weight = Subs Tring (@Input, @CurrentInputPosition + 2 + 18 + 5, 5) @Package Data = substring (@Input, @CurrentInputPosition + 2 + 18 + 5 + 5, 3) @ Filler = substring (@ input, @CurrentInputPosition + 2 + 18 + 5 + 5 + 3, 5) @Cent EntInputPosition + CurrentInputPosition + 2 + 18 + 5 + 5 + 3 + 5 - Save B0 record data end and if (@Record = 'C0') @ receiver ANDRES = (@ Input, @CurrentInputPosition + 2, 18) four (18) @ receiverAddress2 = (@Input, @CurrentInputPosition + 2 + 18, 18) four (18) @ receiverAddress3 = (@Input, @CurrentInputPosition + 2 + 18 + 18, 18 ) Four (18) @CurrentInputPosition + @CurrentInputPosition + 2 + 18 + 18 + 18 - Save even more CEC record data if (@ record = '0') @ contactspers = Note - this is a pseudo-code, cursor it (String (@input, @CurrentInputPosition + 2, 18) @CurrentInputPosition +CurrentInputPosition + 2 + 18 - - DD Record Data and Save
Note / Will have to do meat with loop, etc. Add no comment and do not tell me;)
No comments:
Post a Comment