Export Excel Data

[beulajo]

Hai. I have student marks in excel sheet. I need to export this data in to msaccess. I have no problem in exporting the data. My excel sheet is in this format

I have 40 students. Each student had attended 9 tests which has 5 criteria, under which marks had been given(10).

one sample data is

B0841 Albert Raja A.
0 0 0 0 0
0 0 0 0 0.5
2 2 0 0 1
0 0 0 0 0
0 0 0 0 0
2 1 1 1 0
2 1 1 1 0
1.5 1.5 1.5 1 0.5
2 1 1.5 1.5 1

This whole data is in one row. Now I want this to be exported in 9 rows, I have 5 columns, so student's regno will be repeated 9 times, but i want this marks to be entered in 9 rows for 9 tests.

How do I do that without modifying the structure of excel sheet.

[Delerna]

You could use a union query
I will assume that the spreadsheet imported into an access table called Sheet1 and has the spreadsheets column cell names as field names. ie A,B,C,D,E.........
I will assime the students regno is in column A and that the 5 columns of results are in sets B,C,D,E,F and G,H,I,J,K and L,M,N,O,P etc.

Here is the union query

SELECT A,B,C,D,E,F FROM Sheet1
union all
SELECT A,G,H,I,J,K FROM Sheet1
union all
SELECT A,L,M,N,O,P FROM Sheet1
...keep repeating the unions
...until all 9 sets of 5 results are selected
 

I hope that helps

[beulajo]

But I do not know how to use these query to get the records from excel to ms access

[Delerna]

sorry, I thought you said

I need to export this data in to msaccess. I have no problem in exporting the data

Anyway, do this (I use access 2003)

Open access
Goto the tables pane
right click in a blank area and select "import" on the context menu
In the "Files of type" dropdown select "Microsoft Excel"
Navigate to the excel file and double click it.
Choose the sheet you want to import
Just keep selecting next untill the sheet is imported.

Now you have the sheet in access as a table.
All you need do is use the query above using the table you just imported

[beulajo]

I get only 5 fields value in a row. I m not able to get all the values

even If i get value

How do I insert these records into another table

please help me
it is very urgent

Thanks in advance

[Delerna]

I thought that was what you wanted, 9 rows of 5 values in each row?

This whole data is in one row. Now I want this to be exported in 9 rows, I have 5 columns, so student's regno will be repeated 9 times, but i want this marks to be entered in 9 rows for 9 tests.

Post your query and a better explanation what it is doing wrong


To insert into another table you can turn the finised query into an append query with the wizard

or you can write it manually
something like this

INSERT INTO tblTheOtherTable
SELECT ID,A,B,C,D,E,F
FROM
(   SELECT ID,A,B,C,D,E,F FROM Sheet1 
    union all 
    SELECT ID,A,G,H,I,J,K FROM Sheet1 
    union all 
    SELECT ID,A,L,M,N,O,P FROM Sheet1
) a
 

but that is pretty basic query writing, perhaps you should spend some time in some tutorials that covers access basics

[beulajo]

I found the reason why I was not able to get all the data in msaccess

1. If I use union all I m not able to get but if I use UNION I m able to join

2. While importing the data from excel, there as an error in excel data

coz I had both string value and number value in my excel. coz of which data was not getting imported into db

3. What I have done is this. I left the headrow with string field and number field
and while importing I have checked the option first row as header

4. Without any error the data is imported and now when i use union i m able to get the record in 9 rows

thanks for guiding

[NeoPa]

You seem to assume that UNION ALL must be the same as UNION. Shouldn't common-sense tell you that this is unlikely to be true? It isn't true.

You are likely to have problems with UNION as opposed to UNION ALL when you lose entries that just happen to contain the same set of values. As you've described it so far you have no indicator exported to indicate which test the record refers to. You are relying on the position of the record within the set. This will fail when records are dropped due to the misuse of the UNION phrase.

You really need to start by getting your requirements clearly understood in your own head first, then posting a sensible question (one that makes logical sense). From that point we can be far more helpful to you.