Rename default table field names based on list

jkeyes

Active Member
Joined
Apr 15, 2003
Messages
343
This is my first post in the Access forum, as I am an Access newbie. So, please let me know if you need more info from me, but also, please be as explicit as possible since I just know enough to be dangerous. :) Having said that, I DO understand VBA as it relates to Excel so I will probably understand concepts...

Anyway, my issue is that I have to import a table that is well over 1,000 columns wide. However, the original data does not have column headers (field names) attached- those are in another Excel file, arranged vertically in Column D, with row number equal to the field number in Access. Make sense?

So, I need a a way to rename those fields according to the field names in the Excel file... I'm sure there's a way, besides keying them in manually :)... any ideas?

Thanks in advance for any and all help!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
If these columns each represent a field then you are going to have problems from the start, an Access table can only have 255 fields.:)
 

jkeyes

Active Member
Joined
Apr 15, 2003
Messages
343
Well doesn't that just blow my idea out of the water?! :)

OK, so, if you had a .txt file that was over 1,000 columns wide, with no column headers, and needed to be imported using the fixed width parameter, but you also had a CSV file that had the fixed width boundaries for each field (i.e. start point, end point, # of char.) & the field name, how would you go about it? I guess I'm looking for either conceptually or actually... I'm thinking put them into separate tables, just not sure how to... ideas?

Thanks again!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
What type of data is it?

Why so many columns?

The only way I can think of to import the data would be using VBA's file input/output methods.

Then you could use string functions to parse it out.
 

jkeyes

Active Member
Joined
Apr 15, 2003
Messages
343

ADVERTISEMENT

It's demographic data by zip code (each zip is a record). We get the data from a vendor, so I don't control the file size, unfortunately :(. I guess they like to pile the data in there :)

Maybe I'll see if they can break it into smaller chunks for us...
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
If you are talking about demographic data, you should be able to create a table of ZipCode, DemographicType(current column name from that left hand column in the other spreadsheet), and DemographicData for three fields in a table. Then use VBA to read the data from the text file and load the Normalized table.
HTH,
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

jkeyes

Can you post some sample data?
 

jkeyes

Active Member
Joined
Apr 15, 2003
Messages
343
VR and Norie-

Thanks for the replies, I appreciate it!

I think I'm going to go back to the vendor on this... it is, after all, their responsibility to give us data we can use easily, and this is obviously not turning out to be easy :) Plus, I would imagine they've come across this issue with other clients before... (which is also a bit confusing that they still gave it to us like this, but we'll see...).

Thanks again, I'll definitely stop back here if I have more questions, or it doesn't resolve itself in my favor! :)
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
jkeyes

This could probably be done quite straightforwardly using a little bit of VBA.

I know in the past I've had to deal with this sort of thing.

ie getting badly formatted data into a workable format

Mind you I was dealing with legacy databases whose output just couldn't be changed, and even if it would have been possible to change it then it would just have been a nightmare - filling out IT request forms, explaining the business reasons/requirements etc

So, yes go back to the vendor, but if they can't/won't do anything by all means post back.:)
 

Forum statistics

Threads
1,141,704
Messages
5,707,968
Members
421,538
Latest member
Krisco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top