Tech Support Guy banner
Status
Not open for further replies.
1 - 9 of 9 Posts

·
Registered
Joined
·
10 Posts
Discussion Starter · #1 ·
Hello there!
I am working on a database for work two of the tables are of customers and distributors contact details which are are excell spreadsheets that have been exported from sage.
These spreadsheets are all over the place ie I need to list the postcodes in a form in access but they are not all in one column in the excell sheet!
does anyone know a way, using a macro perhaps, to sort through the excell sheets and putting all postcodes into a certain column???
thanks for the help!
iain
 

·
Registered
Joined
·
1,225 Posts
Everything can be done with macros, but sometimes it's very hard to do it without seeing the problem.
What do you mean by not all in the same column?
They should be in the same column, but when opening the file they got placed in different columns?
Or are they scattered originally (eg. customer#1 has his postcode in the "postcode" field, Customer #2 has it in the "remarks" field, etc?
What is the file extension of the tables exported from sage?

Edit:
And what is sage, anyway?
 

·
Registered
Joined
·
10 Posts
Discussion Starter · #3 ·
Sage is an accounts package that we use. the reason the spread sheet is all muddled is because the original data in sage is not held in an excell sheet so as some address' are longer than others when the data was exported into an excell sheet it all got muddled.
i could go through and sort it manually but there is 5000 records and it would just take so long!
 

·
Registered
Joined
·
19,896 Posts
This should be fairly straight forward to do, how many columns are the post codes spread over?
Can you post a small example, or some dummy data for us to look at?
What format do the Post Codes have?
 

·
Registered
Joined
·
10 Posts
Discussion Starter · #5 ·
The post codes are UK postcodes and US/European ones ther are spead over 4 columns.
work wont let me up load a sample due to customer confidentiality bah
I told my manager they may not be an easyway to sort it, so i think he is gonna get the office girls to re do it all. they will hate me!!
 

·
Registered
Joined
·
1,225 Posts
I looked up google for sage. They say the newest version is fully integerated with MS Office. Based on this, I think even an older version of this sage should be able to export data in a more decent way. Can't you choose export file format? For example, "comma separated values" (.csv) format would do nicely.
 

·
Registered
Joined
·
10 Posts
Discussion Starter · #8 ·
yeah she gave me the files in .csv format but you cant import them into access as .csv so you have to convert them to reguar excell format.
i'll have another look see if im doing it wrong!
 
1 - 9 of 9 Posts
Status
Not open for further replies.
Top