KillerGriller Posted March 26, 2012 Report Share Posted March 26, 2012 Can anybody help me with this? (yes I know there are help files, but I thought of you nerd first )I'm trying to convert a flat table into a DB table (Access), but the data is oriented in individual records e.g.record 1attribute 1attribute 2attribute 3attribute 4record 2attribute 1attribute 2attribute 3attribute 4how can I import/convert it into a proper DB table? e.g.record 1 record 2attribute 1 attribute 1attribute 2 attribute 2attribute 3 attribute 3attribute 4 attribute 4I feel like I'm missing something pretty obvious. I've added column headings to all the records which they didn't have before, but I'm stuck now. Quote Scout: STR: 20.5 | DEX: 13 | STA: 28 | CON: 13.5 | WIS: 8 | CHA: 4http://51feetunder.wordpress.com/ - Running, Rock & Roll, Rock Climbing and PhotographyFitocracy ProfileTwitter Latest Challenge Link to comment
wildross Posted March 26, 2012 Report Share Posted March 26, 2012 Export it to text and use awk/Perl to convert it. Or spend a lot of time cutting and pasting. YOu can also do some things like numbering the rows (add a column), then use some vtext lookup functions to pull them into a new page using the line numbers. Will only work if they all have exactly the same number of entries so you can do math on the line numbers.It aint easy.Then go "speak to" the person who generated the data... Or maybe ask them first to regenerate it?? Quote Warriors don't count reps and sets. They count tons. My psychologist weighs 45 pounds, has an iron soul and sits on the end of a bar Tally Sheet for 2019 Encouragement for older members: Chronologically Blessed Group; Encouragement for newbie lifters: When we were weaker Link to comment
KillerGriller Posted March 26, 2012 Author Report Share Posted March 26, 2012 damn, I thought there would be a simple transpose range function where I could identify values based on "name"Yes the list is shitty, but not the worst I've seen...unfortunately. Quote Scout: STR: 20.5 | DEX: 13 | STA: 28 | CON: 13.5 | WIS: 8 | CHA: 4http://51feetunder.wordpress.com/ - Running, Rock & Roll, Rock Climbing and PhotographyFitocracy ProfileTwitter Latest Challenge Link to comment
BarefootDawsy Posted March 26, 2012 Report Share Posted March 26, 2012 I'm sure we can work something out here, but let me clarify a few things.If your spreadsheet's like this:Record1applesceleryRecord2orangescarrotsShouldn't your DB table be something like[record_id],[fruit],[vegetables]1,apples,celery2,oranges,carrotsIf not, then I'm not really sure what you're trying to do, if so, then you have a few options. The easiset would be to add your column headers on a sheet, then for each record, copy, then Paste Special>Transpose them under the headers.You could write a macro to do this automatically too if there are a ton of records.PM me if you like and I'll give you a hand. Quote BAREFOOT DAWSY Scout Commander (ret.) Link to comment
KillerGriller Posted March 26, 2012 Author Report Share Posted March 26, 2012 I'm sure we can work something out here, but let me clarify a few things.If your spreadsheet's like this:Record1applesceleryRecord2orangescarrotsShouldn't your DB table be something like[record_id],[fruit],[vegetables]1,apples,celery2,oranges,carrotsIf not, then I'm not really sure what you're trying to do, if so, then you have a few options. The easiset would be to add your column headers on a sheet, then for each record, copy, then Paste Special>Transpose them under the headers.You could write a macro to do this automatically too if there are a ton of records.PM me if you like and I'll give you a hand.Your example is exactly what I would like to make my DB table into. I have used transpose before, but never on 4000 records, I'll look into macros and see what I can come up with. Thanks Quote Scout: STR: 20.5 | DEX: 13 | STA: 28 | CON: 13.5 | WIS: 8 | CHA: 4http://51feetunder.wordpress.com/ - Running, Rock & Roll, Rock Climbing and PhotographyFitocracy ProfileTwitter Latest Challenge Link to comment
BarefootDawsy Posted March 27, 2012 Report Share Posted March 27, 2012 No worries, let me know if u need help with the macro...I used to be an excel vba programmer Quote BAREFOOT DAWSY Scout Commander (ret.) Link to comment
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.