Sql-server – Txt format from Bank

formatsql server

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:

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.

Best Answer

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.

Related Solutions

Date format on the output of cmdshell dir C:\ /TC

You can get the date format of dd/mm/yy by using wmic command. This command allows you to get current date without getting affected by regional settings.

**@echo off

    SETLOCAL EnableDelayedExpansion

    for /f "skip=1 tokens=1-6 delims= " %%a in ('wmic path Win32_LocalTime Get Day^,Hour^,Minute^,Month^,Second^,Year /Format:table') do (
        IF NOT "%%~f"=="" (
            set /a FormattedDate=10000 * %%f + 100 * %%d + %%a
            set /a FormattedTime=%%b * 10000 + %%c * 100 + %%e
        )
    )

    for /f %%f in ("D:\Test*.zip") DO set filename=%%~nf
move "D:\Test*.zip" "D:\%FormattedDate%%FormattedTime%-%filename%.zip"**

You can save it as date.bat and run this batch file by executing the following in command prompt:

C:>date.bat

Sql-server – Definition of approach to store temporarily unknown data

Option A is similar to what BI developers have dealt with in terms of incomplete data. In our case it's called a late arriving dimension and accommodates the fact that we often times get incomplete data, that is nevertheless important for immediate storage and analysis.

You can implement some form of the following: Instead of leaving NULLs create a default record in related tables so you can make inserts immediately with as much information as you have and later execute minimal updates without breaking your referential integrity design. Product would have a default or unknown row, and this would be used whenever you have no idea what product is coming in. Shipment maintains an FK to Products and once you know the product you can update Shipment with a new FK to Products. You may wish to add date columns to Shipment so you have insight into when these updates occur.

If you have incomplete product information, but enough to tell you've never seen it before (e.g. UPC but no color) do not use the default/unknown row. Insert a new product as you've described using defaults in the Product table, but generate a new PK. Later when your information arrives you should only need to update the product table - since that record has already been created and referenced elsewhere, you can perform minimal updates when your information finally arrives.

Related Question