Importing from Excel

[Armine]

Hi everyone

I am trying to import spreadsheets from Excel to Acces. So there are a lot of numbers that are missing and instead I have NA in excel. What can I do with this ?

Tnx
Armine

 

[noboffinme]

Hi Armine,

Are you using the Access Import Wizard to import data from an Excel spreadsheet or using a Macro?

Can you post an example of the data you are trying to upload & tell us the version of Office you're using.

Thanks

 

[Armine]

Hi, tnx for feedback.
Frankly, I am a beginner and not very much informed so sorry beforehand for stupid questions
I attached the file, so I have approximately like this big time series in excel and I am importing it in Access (2007-2010) by "External data - Excel...". So I put the first part with names as a text type but The other part with the numbers I cant put as text coz I am afterwards will need to do some calculations or reports or smth. So I was wondering if there is any alternative. Also what about normalization , when I create this new table how can I know that it is not gonna cause me problems with relationships etc.

Thanks again
Armine

 

[noboffinme]

Hi Armine,

So the Access Import process is working correctly & your issue is that it imports the 'NA's that are already on the source spreadsheet & these will not work in calcs?

Is that correct?

Sounds like you know about the table field datatypes as you've chosen the Number datatype for your numbers in order to do calcs.

I would strongly advise you to change the 'NA' values in your spreadsheet to something else (Like Zero) before importing to Access in order to do calcs.

You will need to read about Normalisation as your table setup is not good for a database table.

Read up on this link;
http://support.microsoft.com/kb/283878

Hth

 

[Armine]

hi ,
Yea I will replace the NAs with smth, but this normalization is smth not understandable at all, for some example I look it is understandable coz they have small data but with this time series dont know what to do (((Like when I was makeing a small database with limited thing and didnt have to import any table it was more understandable now I am kind of confused dont know what comes after what.
Tnx for the help
Armine

 

[noboffinme]

Have a read of the link as it shows a similar example.

The way to think about Normalising at the beginning is to write out the field names & what has a One to One or a One to Many relationship.

Your spreadsheet has more than one year for the 'Partner_Europe_Name'

Therefore the ONE 'Partner_Europe_Name' has a MANY relationship with the years.

It is poor design to have both ONE & MANY data on the same table as it causes a lot of duplication of info.

I'm not familiar with your data so keep in mind the below advice is only based on my assumptions of this data.

The answer I would suggest is to make another table called 'Years' that contains the Fields ID_No (Autonumber), 'Year' (Text or Number) & 'Sales' (Number) (assuming the numbers refer to Sales) & then join it to the Rest of the data in another table called 'Partner' adding an ID_No (Autonumber) field.

These Autonumber fields become your Primary Keys & these are joined to form a Relationship.

AS you add information, the Years info will be linked to the Record for the Partner & you will be able to retrieve results when you query the data.

Are you planning to import spreadsheets into the Access database more than once?

 

[Mihail]

Create a database. Only the tables you think are needed.

Fill the tables with some data but, unlike what I see in Excel, with data that will show us the reality. For example I saw in Excel a column "Country" but the data show me only one country (Belgium). I think that should be more countries but, from your Excel, I can't be sure. The same for more other columns.

Then upload this database (Access 2007 or, better, 2003) and will try together to find the best design.

Finally well see how to import data from Excel. I'm sure that well find a solution

 

[noboffinme]

This may help you understand how your data could be better organized.

Have a look at the attached database file.

I have created 2 tables as I suggested above in Excel & imported them into Access.

I then created a relationship between the 2 tables on the ID field.

Have a look at how the db manages multiple Years for the same Partner by looking at the tables info.

Cheers

 

[Armine]

Let me describe maybe the situation fully so u maybe could help me guys.

So I have 25 countries with their own separate spreadsheets u can see 2 small examples in reality they r quiet big. So every column (Direction, Cargo..) has list of possible results which is the same for all this 25 countries besides the Port_coast_code_Name, Port_coast_code which is separate sub lists for the country. So every value like 2000 20, 2001 ... is a unique combination of these several parameters.

And sorry for acting like a kindergarten child will be very happy to see any comments like how to start and to proceed after importing this tables into access.

Thanks
Armine

 

[Armine]

Yea also see attached one of the examples of the column criteria separate table , ex. Cargo column is coming from the separate table cargo with some other details.
With this I think I know what to do but with the years and sales still cant imagine ((

Armine

 

[Mihail]

So every column (Direction, Cargo..)

The dots don't help me.
While you know the situation, I (and I am sure that I am not the single) try to understand the spreadsheets.
Right now I have more problems here:
As example, what is stored in Country column ? The same country as the spreadsheet name ? Other thing like a destination ?

Try to explain each column in Estonia spreadsheet. Each one.

I've attached a sketch of database. But I can't help you anymore without the fully understanding the fields.

 

[Armine]

Re: Importing from Excel and normalization

Hi everyone I am back again

Sorry previously for confusion I will try to explain it better and in details this time and see attached the partial sample files.
The file Romania is one of the app. 20 countries I have data spreadsheets for which I need to import into excel and normalise somehow so I can use it afterwards. For all this spreadsheets the values of the cell can be found in the sample file "list" which I am attaching but it doesn't have to contain all the values that List file has.

So in the file u can see several columns : Direction which is taken from "Directions" sheet of the List file , Goods which is coming from the "Goods" sheet of List , Partner_Name and Partner_code are coming from "Partner" sheet, Port_coast_Name and Port_coast_Code are coming from "county details" sheet. Then there are the values for each year.
So for the unique combination mentioned in each row of these criterias there are the values 2000-2012 and there were NA values which I replaced with -1 (just didn't know else what to do ).


The most problem for me is that I don't imagine at all what to do with the values of years 2000-2012 coz the way I have in the spreadsheets I think I cant use for upcoming queries or reports etc.

Will be waiting for questions or any help .
Thanks A LOT
Armine