Transforming single column data in multiple rows of data (1 Viewer)

gamaz1234

New member
Local time
Today, 10:45
Joined
Aug 3, 2021
Messages
4
Hello,
I got sample data here in an Access single-column table. This data is similar to the original data extracted from a text file.
1
Jack
200000
58
CompanyA
""
2
Jane
180000
50
companyB
""
3
John
190000
53
CompanyC
""
4
Ken
160000
45
CompanyD

Now I need to transform this into a new table as follows
ID Name Salary Age Company
1 Jack 200000 58 CompanyA
2 Jane 180000 50 CompanyB
3 John 190000 53 CompanyC
4 Ken 160000 45 CompnayD

This is a tricky problem that I cannot seem to figure out.
I would appreciate any help/suggestion for resolution on this issue.
Thanks in advance.
 

plog

Banishment Pending
Local time
Today, 10:45
Joined
May 11, 2011
Messages
11,613
I would work with the text file to extract this in the fashion needed, not a table in Access. Essentially you use a scripting language (R, python, even VBA) to iterate through each line in the file and group the records together properly and spit out a new file that fits the format you want.

PsuedoCode:

Code:
fileIn = "Path\To\InputFile\Here.txt"
fileOut="Path\To\OutputFile\Here.txt"

while Not EOF Read in 5 lines from fileIn
  ID = Line1
  Name = Line2
  Salary = Line3
  Age = Line4
  Company = Line5

  print to end of fileOut (ID & ", " & Name & ", " & Salary & ", " & Age & "," & Company)

End While

Then you import that file into Access and have your table.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:45
Joined
May 7, 2009
Messages
19,175
you need to write a Function for the conversion.
see this demo.
 

Attachments

  • oneTomanyColumn.accdb
    600 KB · Views: 402

gamaz1234

New member
Local time
Today, 10:45
Joined
Aug 3, 2021
Messages
4
I would work with the text file to extract this in the fashion needed, not a table in Access. Essentially you use a scripting language (R, python, even VBA) to iterate through each line in the file and group the records together properly and spit out a new file that fits the format you want.

PsuedoCode:

Code:
fileIn = "Path\To\InputFile\Here.txt"
fileOut="Path\To\OutputFile\Here.txt"

while Not EOF Read in 5 lines from fileIn
  ID = Line1
  Name = Line2
  Salary = Line3
  Age = Line4
  Company = Line5

  print to end of fileOut (ID & ", " & Name & ", " & Salary & ", " & Age & "," & Company)

End While

Then you import that file into Access and have your table.
Thank you!
 

gamaz1234

New member
Local time
Today, 10:45
Joined
Aug 3, 2021
Messages
4
you need to write a Function for the conversion.
see this demo.
Thank you for the generous help. The demo works perfectly. However, when I tried to replace the memo field with my data, Access gives me a message that the field is too long. I guess the memo field cannot handle the number of data in my real database. With that constraint do you have any thoughts as to how to handle this scenario?

Thank you.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:45
Joined
May 7, 2009
Messages
19,175
can you upload the Table here.
check if using textstream will do it for you.
 

Attachments

  • oneTomanyColumn.accdb
    584 KB · Views: 404
Last edited:

Users who are viewing this thread

Top Bottom