Transform my Excel data (1 Viewer)

ECEK

Registered User.
Local time
Today, 14:39
Joined
Dec 19, 2012
Messages
717
I want to select my (variable) columns from my Excel file and be able to transform paste into an Access table.

The easiest way would be to transform paste in Excel but for functionality I do not want to do this.

What I would like to do is to paste into a row table (that contains several columns), then transform this into another table either by a query or by looping a function.

Does anybody have any direction or advise for doing this.
Your time is appreciated in advance.
 

isladogs

MVP / VIP
Local time
Today, 14:39
Joined
Jan 14, 2017
Messages
18,186
Suggest you show us what the original data looks like and what the final table will be
 

ECEK

Registered User.
Local time
Today, 14:39
Joined
Dec 19, 2012
Messages
717
My Excel Data is the selecting and copying of column headers:

"ColumnTitle" "ColumnFirstName" "ColumnSurname"etc

If I select a new row in my Access Table1 and paste my data into it I will get one row containing all of my column headers. i.e.

I will get "ColumnTitle" in FieldOne "ColumnFirstName" in FieldTwo "ColumnSurname" in FieldThree etc

What Im looking to do is to append this row data from Table1 into a single field Table2.
FieldOne
"ColumnTitle"
"ColumnFirstName"
"ColumnSurname"
etc (depending on how many columns there were.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:39
Joined
Oct 29, 2018
Messages
21,357
My Excel Data is the selecting and copying of column headers:

"ColumnTitle" "ColumnFirstName" "ColumnSurname"etc

If I select a new row in my Access Table1 and paste my data into it I will get one row containing all of my column headers. i.e.

I will get "ColumnTitle" in FieldOne "ColumnFirstName" in FieldTwo "ColumnSurname" in FieldThree etc

What Im looking to do is to append this row data from Table1 into a single field Table2.
FieldOne
"ColumnTitle"
"ColumnFirstName"
"ColumnSurname"
etc (depending on how many columns there were.
Hi. Probably not exactly what you're looking for; but also, maybe there's something in this demo you might find useful.
 

ECEK

Registered User.
Local time
Today, 14:39
Joined
Dec 19, 2012
Messages
717
I could do with creating a looping Insert into query where Field1 increments to a finite number (255) or it goes to the next field and/or loops to next
Code:
INSERT INTO Table2 ( Name )
SELECT Field1
FROM Table1
 

ECEK

Registered User.
Local time
Today, 14:39
Joined
Dec 19, 2012
Messages
717
I ended up creating a union query for each field then appending it to Table2
This involved unionising 1 to 40 fields, then 41 to 80, 81 to 120 and 121 to 160. Then Union these Union queries.
40 Unions seemed to be the Max amount. What a ball ache !!!
 

June7

AWF VIP
Local time
Today, 06:39
Joined
Mar 9, 2014
Messages
5,423
Should be able to do 50 SELECT lines in UNION. But doubt that makes the process any easier. Is this a one-time event?
 

Users who are viewing this thread

Top Bottom