I tried manually pasting the data into my table and use this sql which formats the data correctlyIf there are any ASCII characters in the range 0 to 31, they might interfere with the way the "string" is interpreted. Do you have a layout description? If not, how will you analyse the file contents?
SELECT Mid(message,1,2) AS [Message Type], Mid(message,5,6) AS [Timestamp], Mid(message,95,1) AS [TYPE-OF-08-RESPONSE], Mid(message,97,8) AS [DO-OUT-DLV-PART-NUM], Mid(message,107,9) AS [DO-OUT-CUSIP], Mid(message,144,8) AS [DO-OUT-RCV-PART-NUM], Mid([message],154,4) AS [DO-OUT-ORIG-INPUT-SRCE], Mid(message,169,13) AS [DO-OUT-MONEY-VALUE], Mid(message,182,1) AS [DO-OUT-DELV-RECV-IND], Mid(message,242,6) AS [DO-OUT-SETTLE-DATE], Mid(message,248,3) AS [DO-OUT-REASON-CODE], Mid(message,261,6) AS [DO-OUT-DATE-STAMP], Mid(message,267,6) AS [DO-OUT-TIME-STAMP], Mid(message,273,20) AS [DO-OUT-CUSIP-DESC], Mid(message,520,9) AS [DO-OUT-SHARE-QTY-NEW], Mid(message,749,16) AS IMS_TID, Format((Date()),"mm/dd/yyyy") AS inserteddate
FROM tbltcc;
Not sure how it reads and skips the nulls when I paste manually.
I just wanted to automate this.