Hi,
I have the data from MS Access to MySQL, half imported by now, that
is not the problem, the same with importing from excel files.
The problem is how can I get all the data in one big table ?
Best regards,
Cristian Stoica
Peter Brawley wrote:
> Cristi,
>
> > In theory it is simple but I need some help on how that will be
> >"translated" to mysql, I have a concept in mind, but I do not know
> >how to exactly apply it to the situation.
> > I am in a situation where I have the data gathered from more persons
> >and everybody had a personal way of designing the database or excel
> >table and now I need to build a database that will include all the
> data in
> >the tables bellow, that is way I was not able to give you a table
> >description from mysql since I do not have all the tables imported into
> >mysql.
>
> 1. First thing may be to turn the spreadsheets into tables. It's
> trivial to turn a spreadsheet into an Access table (eg
> http://databases.about.com/aa123100a.htm).
>
> 2. Once you have all the representations in Access, you have a choice
> to make: standardise the data representation in Access, or export all
> the data in its various models to MySQL and standardise the mess
> there. Presumably you know Access much better than MySQL, so you may
> want to do this standardisation in Access before exporting to MySQL.
> In that case, you'll want to implement logic of the sort I described
> in Access, before exporting the data to MySQL.
>
> 3. You have choices for how to export tables from Access to MySQL.
> Simplest may be to export from Access to text files, then use the
> MySQL utility mysqlimport to import the data into a mysql table. Or,
> you can also use an Access-to-mysql converter script (Google for
> them), or a GUI tool like DbTools, which is freeware. Or (perhaps the
> most complex method), you can create the target MySQL table, use
> ODBCAdmin to create a DSN for it, and export to that.
>
> Is that the info you are looking for?
>
> PB
>
> -----
>
> inferno wrote:
>
>> Hi,
>>
>> First of all thank you for your reply.
>> The table names I've wrote in the e-mail were just for info, I do
>> use underscore instead of space, as you can see I do the same for the
>> fields in the table.
>> In theory it is simple but I need some help on how that will be
>> "translated" to mysql, I have a concept in mind, but I do not know
>> how to exactly apply it to the situation.
>> I am in a situation where I have the data gathered from more
>> persons and everybody had a personal way of designing the database or
>> excel table and now I need to build a database that will include all
>> the data in the tables bellow, that is way I was not able to give you
>> a table description from mysql since I do not have all the tables
>> imported into mysql.
>> After I will import the tables into mysql I have to make somehow
>> the new table and I would like it to be good, and that it can be
>> further upgraded ( maybe a new service will need to be entered for a
>> customer and I need to have all the data in one place ) and to be
>> able to handle each entry not by using 4 fields that are varchar.
>>
>> If you / someone can help me with this task I will be very
>> grateful, as I have said before I am a beginer and do not have much
>> experience with MySQL.
>>
>> Best regards and thank you for your advice,
>> Cristi Stoica
>>
>> Peter Brawley wrote:
>>
>>> Cristi,
>>>
>>> > I have the following tables: ( some in Microsoft Access and some
>>> > in Excel ) and I want to migrate the data into MySQL and develop
>>> > an interface in PHP for easy administration and control...
>>>
>>> A few points:
>>>
>>> 1. Instead of spaces, use underscores or nothing in table names
>>>
>>> 2. If Address_Code contains addresses of Sales_Representatives, it
>>> needs an
>>> column for Sales_Representative ids.
>>>
>>> 3. One way to merge cp2, cp3 and cp4 would be:
>>> (1) add int 'old_id' columns to cp2, cp3 and cp4,
>>> populate with sequential values such that
>>> old_id values are unique across cp2, cp3 or cp4
>>> (2) make a new customers table structure as desired, and include
>>> the new cp2 cp3 & cp4 old_ids, and
>>> an auto_increment int primary key,
>>> (3) import into customer from cp2, cp3, cp4,
>>> excluding dupes computed without reference to old_id
>>> (4) make a customertype table whose columns include
>>> all columns in cp2 cp3 & cp4 which are not in customers
>>> auto_increment primary key id
>>> customer_id
>>> customer_type (whatever values you want corresponding to cp2
>>> etc)
>>> (5) populate customer_type from joins on
>>> customer and cp2
>>> customer and cp3
>>> customer and cp4
>>> using old_id
>>> (6) drop the old_id column from customers
>>>
>>> PB
>>>
>>> -----
>>>
>>> inferno wrote:
>>>
>>>> Hi,
>>>>
>>>> I have the following tables: ( some in Microsoft Access and some
>>>> in Excel ) and I want to migrate the data into MySQL and develop an
>>>> interface in PHP for easy administration and control.
>>>>
>>>> *1) Sales Representative Code
>>>> *sr_id INTEGER ( unique )
>>>> sr_user VARCHAR
>>>> sr_name VARCHAR
>>>> sr_email VARCHAR
>>>> /id INTEGER ( auto increment and primary key ) this is something
>>>> that I will want to add/
>>>>
>>>> *2) Address Code*
>>>> address_street VARCHAR
>>>> address_street_number VARCHAR
>>>> address_street_block_of_flats_number VARCHAR
>>>> address_staircase_number VARCHAR
>>>> address_area VARCHAR
>>>> address_container INTERGER
>>>> address_code VARCHAR
>>>> /id INTEGER ( auto increment and primary key ) this is something
>>>> that I will want to add/
>>>>
>>>> *3) Customer Packet 2*
>>>> cp2_contract_no INTEGER ( unique )
>>>> cp2_name VARCHAR
>>>> cp2_street VARCHAR
>>>> cp2_street_number VARCHAR
>>>> cp2_block_of_flats_number VARCHAR
>>>> cp2_staircase_number VARCHAR
>>>> cp2_apartament_number VARCHAR
>>>> cp2_packet_old VARCHAR
>>>> cp2_packet_new VARCHAR
>>>> cp2_packet2 VARCHAR
>>>> cp2_user VARCHAR
>>>> cp2_customer_phone VARCHAR
>>>> cp2_id_serie VARCHAR
>>>> cp2_id_number INTEGER
>>>> cp2_personal_code INTEGER ( it's the unique code of a person so I
>>>> cannot use it as a unique key since a client can have more
>>>> contracts and more locations )
>>>>
>>>> *4) Customer Packet 3*
>>>> cp3_client_id INTEGER( it is not for the location, so if I select
>>>> all the data with this code it can show more than one entry since
>>>> one client can have more locations )
>>>> cp3_contract_number INTEGER
>>>> cp3_customer_name VARCHAR
>>>> cp3_customer_packet VARCHAR
>>>> cp3_street VARCHAR
>>>> cp3_street_number VARCHAR
>>>> cp3_block_of_flats_number VARCHAR
>>>> cp3_staircase_number VARCHAR
>>>> cp3_floor_number VARCHAR ( ground floor is considered P in the
>>>> current database and other problems )
>>>> cp3_flat_number VARCHAR
>>>>
>>>> *5) Customer Packet 4*
>>>> cp4_client_id INTEGER
>>>> cp4_contract_number INTEGER
>>>> cp4_customer_name VARCHAR
>>>> cp4_customer_packet VARCHAR
>>>> cp4_street VARCHAR
>>>> cp4_street_number VARCHAR
>>>> cp4_block_of_flats_number VARCHAR
>>>> cp4_staircase_number VARCHAR
>>>> cp4_floor_number VARCHAR
>>>> cp4_flat_number VARCHAR
>>>>
>>>> Now a little info on the tables:
>>>> Customer Packet 3 and 4 client_id are identical, but they do not
>>>> exist all the time.
>>>> Customer Packet 2,3,4 can be grouped based on street,
>>>> street_number, block_of_flats_number, staircase_number,
>>>> flat_number, the floor number is not in the Customer Packet 2, but
>>>> there are a few clients that are only inf CP2, CP3 or CP4 and I
>>>> need to add him also, with the data that I find in the tables,
>>>> otherwise I will lose this clients and the will not show up anymore.
>>>>
>>>> >From all the tables I will have to be able to make a new table
>>>> that will have all the data, and if a customer is only in the
>>>> customer packet 2, let's say, he will have blanks for the other
>>>> fields in table CP( Customer Packet)3 and 4.
>>>> Lets say consider this example like this:
>>>> I have CP2 cable contracts, CP3 internet contracts and CP4 the VoIP
>>>> contracts and I need to get all the data in one table.
>>>> I will need to be able to have an unique ID for each entry, so that
>>>> is way I am thinking of creating a big table with all this data
>>>> since I cannot use string as a condition, I've had some problems
>>>> with imports from excel/csv into mysql and I would like to be able
>>>> to control each location.
>>>>
>>>> I will need to get all this data into a new table that looks like
>>>> this:
>>>> *Custmer Data
>>>> *id unique, primary key
>>>> customer_name
>>>> customer_street
>>>> customer_street_number
>>>> customer_block_of_flats_number
>>>> customer_staircase_number
>>>> customer_flat_number
>>>> customer_cp2_packet_old ( use cp2_packet_old )
>>>> customer_cp2_packet_new ( use cp2_packet_new )
>>>> customer_client_id ( you will find it in cp3 or cp4, it should be
>>>> in both, but since user error exists, it's not in both. )
>>>> customer_cp3_packet ( use cp3_customer_packet )
>>>> customer_cp3_name ( use cp3_customer_name )
>>>> customer_cp3_contract_number ( use cp3_contract_number )
>>>> customer_cp4_packet ( use cp4_customer_packet )
>>>> customer_cp4_name ( use cp4_customer_name )
>>>> customer_cp4_contract_number ( use cp4_contract_number )
>>>> customer_area
>>>> customer_container
>>>> customer_code
>>>> ( The customer_area, customer_container, customer_code are based on
>>>> street, street number, block of flats number, staircase number)
>>>>
>>>> I think I will have to join the tables, but I have no experience
>>>> with joining tables.
>>>> MySQL version: 4.0.24
>>>> OS: linux
>>>>
>>>> If you have any sugestions or recomandations on how the new table
>>>> should look / how to solve this problem please feel free to advise
>>>> me since I am willing to learn.
>>>>
>>>> Best regards,
>>>> Cristi Stoica
>>>>
>>>>------------------------------------------------------------------------
>>>>
>>>>No virus found in this incoming message.
>>>>Checked by AVG Anti-Virus.
>>>>Version: 7.0.344 / Virus Database: 267.11.13/126 - Release Date:
> 10/9/2005
>>>>
>>>>
>>>------------------------------------------------------------------------
>>>
>>>No virus found in this outgoing message.
>>>Checked by AVG Anti-Virus.
>>>Version: 7.0.344 / Virus Database: 267.11.14/127 - Release Date: 10/10/2005
>>>
>>>
>>>
>>>
>>>------------------------------------------------------------------------
>>>
>>>
>>>
>>>
>>
>>------------------------------------------------------------------------
>>
>>No virus found in this incoming message.
>>Checked by AVG Anti-Virus.
>>Version: 7.0.344 / Virus Database: 267.11.14/127 - Release Date: 10/10/2005
>>
>>
>>
>------------------------------------------------------------------------
>
>No virus found in this outgoing message.
>Checked by AVG Anti-Virus.
>Version: 7.0.344 / Virus Database: 267.11.14/127 - Release Date: 10/10/2005
>
>
>
>
>------------------------------------------------------------------------
>
>
>
>
