Get value from one field, and paste it in to a new column

Question

I'm relatively new to Microsoft Access, and just a beginner with SQL programming.

I have a table in MS Access. It is an import from a .txt file. In the first column There is a String with a Number or Code in it. This number always Comes after a string "Arbeitslpatz "(The first character starts at string Position 17) and always changes every 76 rows. (Total rows 29790). What I want to achieve is to copy this number (for example 101100) and paste it in a new column in the first 76 rows. Then comes the next number for the next 76 rows, and so on. (I later want to to a lookup function with SQL WHERE functions and return some desired values, and for that I need to have these "Arbeitsplatz numbers" in a new row. I already assigned an ID to the file, but I didn't manage to get farther. (I experimented with VBA Loops, but didn' really succeed) Either SQL or VBA Code would be appreciated.

I don't know how well the screenshots can be seen &/ if I provided enough Information...

Thank you all for your help

This is an Image from the orignial text file which I linkt to the database in Access:

Answer 1

Edited OP code:

Function Macro1()
'' dim variables
Dim tmp As String, temp() As String
Dim rs As Recordset
'' open source for reading
Open "J:\80_Applications\SAPGuiScripting\01_TXT_Files\CM01_Total.csv" For Input As #1
'' attach recordset to table
Set rs = CurrentDb.OpenRecordset("CM01_Total_1") 
'' loop source - read line-by-line until its end
Do Until EOF(1)
  '' read one line
  Line Input #1, tmp
  '' look if it is a header, if yes, extract and store the number
  If Left(tmp, 12) = "Arbeitsplatz" Then num = Trim(Mid(tmp, 17,8))
  '' look if it is a data line
  If InStr(tmp, "|") > 0 And InStr(tmp, "Woche") = 0 Then
    '' divide the line to separate fields
    temp = Split(tmp, "|")
    With rs
      '' add new, empty, record
      .AddNew
      '' set values into fields
      !Arbeitsplatz = num
      !Woche = temp(1)
      !Bedarf = temp(2)
      !Angebot = temp(3)
      !Belast = temp(4)
      ![freie_Kap] = temp(5)
      !Einh = temp(6)
      '' save editions
      .Update
    End With
  End If
'' loop to reading the next line
Loop
'' close source file and recordset
Close #1
rs.Close
Set rs = Nothing
End Function

PS. Error handler must be added.

Answer 2

This code runs without an error, but doesn't do anything...

Option Compare Database

Function Macro1()
Dim tmp As String, temp() As String
Dim rs As Recordset
Open "J:\80_Applications\SAPGuiScripting\01_TXT_Files\CM01_Total_1.txt" For Input As #1
Set rs = CurrentDb.OpenRecordset("CM01_Total_1")
Do Until EOF(1)
  Line Input #1, tmp
  If Left(tmp, 12) = "Arbeitsplatz" Then num = Val(Mid(tmp, 17))
  If InStr(tmp, "|") > 0 And InStr(tmp, "Woche") = 0 Then
    temp = Split(tmp, "|")
    With rs
      .AddNew
      !Arbeitsplatz = num
      !Woche = temp(1)
      !Bedarf = temp(2)
      !Angebot = temp(3)
      !Belast = temp(4)
      ![freie Kap] = temp(5)
      !Einh = temp(6)
      .Update
    End With
  End If
Loop
Close #1
rs.Close
Set rs = Nothing
End Function