database help

1337_KR3W

1337_KR3W

1337_KR3W

Soldato
Joined
24 Jan 2003
Posts
2,560
Location
Cornwall
I'm trying to tidy up an export I have been provided to get details from, but am not having much luck - below is an example of the data.

I have the choice of using excel or access, I have been trying to use access as i don't think excel is capable of sorting this data.
If i import to excel it would appear the same as below, i thought setting some relationships in access would sort it out but i cant get the data to import without errors.


Code: 
101,8710625514203,(12),'BLUE CURACAO'
101,8710625514203,(14),8
101,8710625514203,(13),10.1
101,8710625514203,(403),1
101,8710625514203,(16),1
101,8710625514203,(451),1
101,8710649000300,(12),'MELBA TOASTS'
101,8710649000300,(14),2
101,8710649000300,(13),0.96
101,8710649000300,(403),1
101,8710649000300,(16),2
101,8710649000300,(451),1
101,8710649190018,(12),'FRENCH TOAST'
101,8710649190018,(14),2
101,8710649190018,(13),0.93
101,8710649190018,(403),1
101,8710649190018,(16),2
101,8710649190018,(451),1
101,8710785000509,(12),'MELBA TOASTS'
101,8710785000509,(14),2
101,8710785000509,(13),0.96
101,8710785000509,(403),1
101,8710785000509,(16),2
101,8710785000509,(451),1
101,8710800974044,(12),'HIP HOP BAR'
101,8710800974044,(14),3
101,8710800974044,(13),0.05
101,8710800974044,(403),1
101,8710800974044,(16),1
101,8710800974044,(451),1
101,8710873000343,(12),'GOUDA & ALMOND'
101,8710873000343,(14),2
101,8710873000343,(13),1.74
101,8710873000343,(403),1
101,8710873000343,(16),2
101,8710873000343,(451),1
101,8710873100104,(12),'GOUDA TWISTS'
101,8710873100104,(14),2
101,8710873100104,(13),1.78
101,8710873100104,(403),1
101,8710873100104,(16),2
101,8710873100104,(451),1
101,8711371998101,(12),'SALSA DIP DIP'
101,8711371998101,(14),2
101,8711371998101,(13),1.21
101,8711371998101,(403),1
101,8711371998101,(16),2
101,8711371998101,(451),1
101,8711371998118,(12),'TEX MEX DIP'
101,8711371998118,(14),2
101,8711371998118,(13),1.46
101,8711371998118,(403),1
101,8711371998118,(16),2
101,8711371998118,(451),1
101,8711371998125,(12),'CHEDDAR DIP'
101,8711371998125,(14),2
101,8711371998125,(13),1.46
101,8711371998125,(403),1
101,8711371998125,(16),2
101,8711371998125,(451),1
101,8711371998132,(12),'GUACAMOLE DIP'
101,8711371998132,(14),2
101,8711371998132,(13),1.46
101,8711371998132,(403),1
101,8711371998132,(16),2
101,8711371998132,(451),1
101,8711500558213,(12),'CAMERA BATTERY'
101,8711500558213,(14),1
101,8711500558213,(13),6.99
101,8711500558213,(403),1
101,8711500558213,(16),1
101,8711500558213,(451),1
101,8711500806833,(12),'CAMERA BATTERY'
101,8711500806833,(14),1
101,8711500806833,(13),6.99
101,8711500806833,(403),1
101,8711500806833,(16),1
101,8711500806833,(451),1
101,8711500809537,(12),'PHILLIPS R20'
101,8711500809537,(14),1
101,8711500809537,(13),1.99
101,8711500809537,(403),1
101,8711500809537,(16),1
101,8711500809537,(451),1
101,8711500809650,(12),'PHILLIPS R14'
101,8711500809650,(14),1
101,8711500809650,(13),1.99
101,8711500809650,(403),1
101,8711500809650,(16),1
101,8711500809650,(451),1
101,8711500809773,(12),'PHILLIPS R6'
101,8711500809773,(14),1
101,8711500809773,(13),1.99
101,8711500809773,(403),1
101,8711500809773,(16),1
101,8711500809773,(451),1
101,8711500809858,(12),'PHILLIPS 9V'
101,8711500809858,(14),1
101,8711500809858,(13),1.99
101,8711500809858,(403),1
101,8711500809858,(16),1
101,8711500809858,(451),1
101,8711571026833,(12),'HOTPOP AM. CANDY'
101,8711571026833,(14),2
101,8711571026833,(13),2.21
101,8711571026833,(403),1
101,8711571026833,(16),2
101,8711571026833,(451),1
101,8711601310192,(12),'STRIPY POPS'
101,8711601310192,(14),3
101,8711601310192,(13),0.4
101,8711601310192,(403),1
101,8711601310192,(16),1
101,8711601310192,(451),1
101,8711601310208,(12),'TIPSY  POPS'
101,8711601310208,(14),3
101,8711601310208,(13),0.4
101,8711601310208,(403),1
101,8711601310208,(16),1
101,8711601310208,(451),1
101,8711852200013,(12),'GUACAMOLE'
101,8711852200013,(14),2
101,8711852200013,(13),2.15
101,8711852200013,(403),1
101,8711852200013,(16),2
101,8711852200013,(451),1
101,8713621870106,(12),'MINI STROOP'
101,8713621870106,(14),2
101,8713621870106,(13),1.4
101,8713621870106,(403),1
101,8713621870106,(16),2
101,8713621870106,(451),1
101,8714800008921,(12),'BAVARIA STUBBIES'
101,8714800008921,(14),10
101,8714800008921,(13),3.55
101,8714800008921,(403),1
101,8714800008921,(16),1
101,8714800008921,(451),1
101,8714800008945,(12),'BAVARIA STRONG'
101,8714800008945,(14),10
101,8714800008945,(13),4.75
101,8714800008945,(403),1
101,8714800008945,(16),1
101,8714800008945,(451),1
101,8714800013413,(12),'HOLLANDIA'
101,8714800013413,(14),10
101,8714800013413,(13),3.85
101,8714800013413,(403),1
101,8714800013413,(16),1
101,8714800013413,(451),1
101,8716509191508,(12),'DISP LIGHTER'
101,8716509191508,(14),11
101,8716509191508,(13),0.5
101,8716509191508,(403),1
101,8716509191508,(16),1
101,8716509191508,(451),1
101,8717163327166,(12),'CIF CRM SPRAY'
101,8717163327166,(14),5
101,8717163327166,(13),1.29
101,8717163327166,(403),1
101,8717163327166,(16),1
101,8717163327166,(451),1
101,8717163351611,(12),'LUX CREME'
101,8717163351611,(14),4
101,8717163351611,(13),0.52
101,8717163351611,(403),1
101,8717163351611,(16),1
101,8717163351611,(451),1

the 101 can be ignored, the second column would be my primary key (it is the barcode number), the third column would be used to sort the 4th column and bring one row per barcode number. (12) is product name (with ' as text qualifier to remove the '') (13) is price, and (14) is department (the group that product would fall into)

I want to sort the data so i have
<bacode number><name><price><dept> on one row with no duplicate barcodes.

I need to learn to do this myself so if you could take the time to put it step by step it would be appreciated.

Thankyou.
 
it looks like a standard csv file format to me, have you tried opening it in excel?

just copy it to a *.csv file name and open it.

failing that importing a csv file into a DB should be trivial, have you got anything other than access available (SQL server, MSDE or oracle)

Paul
 
It is a standard CSV, and i have imported it into excel, although there are 10000 products, i only posted a sample. I cant drag all the prices, depts, etc to get to the format i need as it would probably take a full days work to do.
I was hoping to automate the process so instead of getting
<barcode><type><dept>
<barcode><type><price>
<barcode><type><name>
<barcode><type><any other bits>

i would get
<barcode><dept><price><name><any more...>


I only have access or excel available, but will download any free sql or other programs if you would recommend that
 
I have been trying (without much luck) to use a few lookups in excel to find the correct entries and create the information in three sheets in the correct order so i can colate it, is there an easy way of doing this?



Edit: I have sorted the data in excel by sorting into the different sections and picking through each column any adjusting so all numbers match up,
It will take a few hours but it will probably be just as quick as learning to do it properly, if anyone does have the time & knowlege to advise me how to use the software to sort it for me I would very much appreciate a step by step guide or some pointers as I will have similar datasets in the future.
 
Last edited:
Access is your friend as you need to merge 4 queries into one resultset.

I don't want to do it for you but import the data into one table.

Design 4 queries, each one to return only one data type and the barcode, i.e. name, dept etc and save them all with a good name

Design a fifth query that uses all of those 4 queries as it's tables. Join them all on barcode number.

Now here's the important bit, you need to return results even if there is no match on one of the data types (ie record count should be the same as the total number of different barcodes). This bit I can't remember how to do in Access other than writing SQL directly. Look up "Full Outer Join" in the help.

The results of this query should be the correct resultset.



If you want to cheat, I just tried it (cos I'm bored) and can email you the database if you want 25KB zipped)
 
Last edited:
I have tried buttons but if you would be willing to send the database i would be very greatful, I have imported eveything but my queries dont seem to work right.

mikehall85 at hotmail.co dot uk
 
You must log in or register to reply here.
Back
Top Bottom