0

I'm working on a service that allows users to store data from the bank, but banks give me their account statements in different formats, one of them is the following

68520157286 0106201809510981PAY TRAN SPEI -000000000088640000001321436306018973NON1 CLA24 REF 0000000

I would like to know if someone knows the name of this format and how I can treat it to store it in SQL Server, I receive this format in a txt file, which can contain more than a thousand records. , what strikes me about the format are two things:

enter image description here

First, that at first glance we might think that the information is separated by blank spaces as shown

Secondly, there are many zeros among the quantities, which make it difficult for me to know if they are part of the quantity to be treated or if they are part of a character separation format.

So, my question is to know the name of the format and if it is convenient to treat all that information from SQL to store it or if it should be passed through a previous separation process before trying to put it in the database. Thank you very much everyone for your time.

  • Is it the swift format? – Vérace Jul 11 '18 at 0:42
5

I worked on a similar project, and we asked the source institution to provide a breakdown of the field types, lengths, and delimiters. We then stored the source files as-is for auditing, and processed them into tables in SQL Server.

Ask the bank for a key to the file layout. Guessing and asking the Internet is setting yourself up for failure.

  • Thank you kind sir, I'll work on it and if I get any trouble I'll post it here – E.Rawrdríguez.Ophanim Jul 11 '18 at 14:11
  • I was thinking if i could split the string with a Bulk Insert in SQL? – E.Rawrdríguez.Ophanim Jul 13 '18 at 18:54
  • Bulk insert the file into a staging table first, then do string manipulation on the data once it's in the staging table. – Randolph West Jul 13 '18 at 19:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.