Forum Moderators: phranque
I have data that looks like this
1,5,20,33,39
4,16,22,33,34
etc...
and I want the data 'moved' into columns of the heading
1 2 3 4 5 6 7 8 9 10 etc...
Where in the first row "1" would fall under the "1" column, then the "5" under the "5" column etc...
I know that Access is to many not a 'real' database but that is what I am using... I really do not want to convert the data by hand because we are talking a thousand+ rows...
Thanks in advance!
TomCat
I'm having a little trouble following. What happens in the second row? From your description, you seem to have a large number of rows that will be converted to one row, with the numbers in that row matching the header row. So you would have as many columns as you do different numbers?
Its early :) Give me some more help :)
By the way, Access certainly is a real database - but it may be possible to make this conversion in Excel, which would then make your Access import pretty straightforward.
There's no excel/spreadsheet forum in here, so i understand your confusion. Anyway, it can be done but there are no built-in excel functions that will do it for you. You simply have to do one of these:
1) move the stuff manually
2) use a VBA progam (a script in the MS-office scripting language VBA)
No. 2 has to be written first, so if you don't know how to it will probably take longer time than doing no. 1 manually. In either case, excel has only 255 columns, so you can not have data values higher than this.
/claus
Just read Mardi_Gras' post, and i'm also in doubt now, i'm not sure i got it right. Do you want just one row in excel, summarizing all the rows in your dataset?
That is: If there are two rows in the dataset containing "4" then the cell R1:C4 should contain the figure 2?
OR: do you want the forth column in your access database to contain the figure 4 - for each of the rows in excel, where the figure four is?
By default, Access will import the figures like this, and i don't think that is what you want:
¦ 1¦ 2¦ 3¦ 4¦ 5¦
----------------
¦ 1¦ 5¦20¦33¦39¦
¦ 4¦16¦22¦33¦34¦[edited by: claus at 2:27 pm (utc) on Aug. 27, 2003]
I dont know which versions you are using or how they were installed (default or full / custom install etc) but it usually starts with the command "file" "Get external data"
If you did the default install of access you may not have as many data import options as you could have with a fuller installation but I doubt MS would have excluded excel in even the most basic installation.
PS: make a blank first row in your excel file and insert the text you would like for column names - the resulting access data table can use the first row as the names for the data columns, you are given the option on setting the import to use first row as cell names.
hth
I dont remember exactly but excel alows you to create html tables (busy overcoded ones when I last did it :-) from data in an excel file so its a pretty bet that if you mark and copy an html table and copy and paste it into excel it may space into relevant columns ....
If not you could use textpad (there used to be a free evaluation from download.com ) to start to auto replace table tags with some kind of comma delimiting to make the data suitable to direct import into excel or access
Then it *should* be in the right format for exporting to access.
HTML dosnt pastep articularly well in Excel.
This will then separate each number into a cell and remove the comma's.
