Good day. There is a file with the structure of the form:

$$ 0002:SOMEDATA20DIG:00129954:0016:82:19:161102:2 0000:DATWITH12DIG:00:0000:00+000000+0000000+0000000000 Пропуск сотни строк 1012:DATWITH12DIG:45:0000:00+000000+0000000+0000000000 1100:DATWITH12DIG:00:0000:00+000000+0000000+0000000000 1101:DATWITH12DIG:65:0500:00+000068+0000069+0015333800 1102:DATWITH12DIG:61:0000:00+000000+0000000+0000000000 1103:DATWITH12DIG:51:0000:00+000000+0000000+0000000000 1104:DATWITH12DIG:51:0000:00+000000+0000000+0000000000 1105:DATWITH12DIG:51:0000:20+000000+0000000+0000000000 1109:DATWITH12DIG:01:0000:00+000000+0000000+0000000000 1201:DATWITH12DIG:27:0000:00+000000+0000000+0000000000 1401:DATWITH12DIG:05:0500:00+000000+0000000+0000000000 1801:DATWITH12DIG:05:0500:00+000065+0000065+0020218750 1901:DATWITH12DIG:45:0000:00+000000+0000000+0000000000 $$

This is one element, there can be a maximum of 800 elements of all such elements $$ Each takes a fixed number of lines 236. All position numbers are repeated at least 2 times. I parsed the file by + and: and wrote it into the base of 8 columns. But there was a problem how to uniquely identify the data block, then to bind the block data to the first line 0002:SOMEDATA20DIG:00129954:0016:82:19:161102:2

When entering into a table, use another column for which to prescribe any identifier? Or pull all the data into one line and write each element to a new column (then it turned out too much)? Maybe there are still some ways?

  • 2
    I reviewed your questions ... you would understand which of the fields in this data means and in the database to store with meaningful names and most importantly, with the appropriate data types. There is no need to store numbers with leading zeros and then invent ways of adding leading zeros after addition. If, at the output of the program, such leading zeros are required, it is at that moment that they should be added. So it will be much easier to work. Maybe some other patterns in the data are worthy of storage in separate tables in general. - Mike

1 answer 1

You should have two tables in the database. In one table you write data rows

 0002:SOMEDATA20DIG:00129954:0016:82:19:161102:2

+ separate field some unique identifier (primary key). It is easier and more correct to make this identifier through an auto-increment field.

In the second table, write the data rows

 0000:DATWITH12DIG:00:0000:00+000000+0000000+0000000000

+ A separate field identifying the entry in the first table. Classic one-to-many relationship