You could use OpenDataSource to access the file like a table, then you could use a where clause to limit which rows you are inserting into each table based on the value in the first column. To handle a format like that you will need a schema.ini file, I discuss that at: https://www.sqlservercentral.com/articles/reading-ad-hoc-text-files-with-opendatasource
SSIS is very flexible and will let you do it directly, but you could use the very user friendly import and export wizard to bring it into a staging table, specifying your unusual delimiters in the wizard, and then use normal T-SQL to split it out from the staging table.
Of course, you could always use a language like Python or Perl that is really good at text manipulation to split it out for you before you import it if you are more comfortable doing it that way.
9 People are following this question.