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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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.:)
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
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,
 
Upvote 0
jkeyes

Can you post some sample data?
 
Upvote 0
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! :)
 
Upvote 0
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.:)
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

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
Back
Top