Import CSV to 2 mysql tables?

[ABCStudio]

I have about a 1000 records i need to import into a mysql database however the excel spread sheet contains both the contact information and the service information in 1 table whereas in the database I have 2 separate tables.

Is there a simple way to import say rows 1 to 10 in to table a and rows 11 to 20 in to table b while assigning an autonum id in table a and putting the same id as a client id in table b?

 

[trilby274]

Simplest way I can think of is to create two copies of your excel file. Are you sure it is rows and not columns you wish to import?

Delete columns 1-10 in one spreadsheet and 11-20 in the other and then import each of the new spreadsheets as required.

Don't know much about mysql, but it may allow you to import specific rows/columns at the import stage. If that is the case, you can just specify the row/columns you need without changing the original excel file.

 

[edmondscommerce]

just create two separate csv files with the data in the right places

If you want me to do this for you PM me.

 

[ABCStudio]

just create two separate csv files with the data in the right places

Not quite so simple as there are currently duplicate, ie the same contact appears 4 times with 4 different services, I want to get down to 1 contact with 4 linked services.

If you want me to do this for you PM me.

Thanks for the kind offer but I do need to figure out a simpler way of doing this myself

 

[edmondscommerce]

its hard to give you any advice without seeing the data in question

i suspect though that the easiest way to do this would be to insert the data into mysql as is, and then use mysql queries or php scripting to rebuild the data into the final format with a one to many relationship between contacts and services

 

[computaguy]

Start by downloading DB Manager Professional. This will allow you to administrate your MYSQL tables from a GUI. The DB manager incorporates a facility to create tables from an import. Before you do this however you need to separate the data in your spreadsheets.

Firstly create a unique number for each contact. Then use this number to identify which contact record the lines in your second spreadsheet relate to.

Now once you have done your import into your two new tables called for example: TblContacts and TblDetails you must run a final creation query using Select Distinct or the MYSQl equivalent as the rowsource.

It is a fairly long procedure, and if it were me I would be looking at writing a short routine to do it for me.

Hope this makes things a little clearer.

 

[computaguy]

Also, and I know this goes without saying almost... but check the resultant data very carefully. I have a database of around 10 million vehicle positions and when we last checkd the backup/restore functions mysql started to get confused a few thousand lines into the import. Of course that could have been my programming at fault but I don't think so because it worked perfectly the second time around.

 

[dataferret]

There is a quick way to do this assuming you can use MS Access.

First, import your CSV files into an empty Access database. Dedupe the access tables to remove your duplicates. Next run some queries within access to extract the data you want into two tables - this will give you the unique data tables structured exactly how you want / require. Check the integrity of your data. Now you can delete the original data table you imported, leaving you with only your unique two data tables.

Finally you can export to a CSV or get a copy of Access2SQL (search google) and export the data into an SQL dump file. Alternatively use something like Navicat to transfer the data tables from Access into a Mysql database.

It is not a very pretty method but it will do what you need it to and you can easily automate it later when it all works how you want.

 

[computaguy]

There is a quick way to do this assuming you can use MS Access.

First, import your CSV files into an empty Access database. Dedupe the access tables to remove your duplicates. Next run some queries within access to extract the data you want into two tables - this will give you the unique data tables structured exactly how you want / require. Check the integrity of your data. Now you can delete the original data table you imported, leaving you with only your unique two data tables.

Finally you can export to a CSV or get a copy of Access2SQL (search google) and export the data into an SQL dump file. Alternatively use something like Navicat to transfer the data tables from Access into a Mysql database.



It is not a very pretty method but it will do what you need it to and you can easily automate it later when it all works how you want.

Alternatively expect to pay about £50 for the two hours it would take any decent programmer to give tou software to do it for you.

 

[matt.chatterley]

just create two separate csv files with the data in the right places

If you want me to do this for you PM me.

Ditto; tho as commented, hard to be specific without seeing the data.

I suspect you have something like:

Mr Smith - Service 1
Mr Smith - Service 2

etc - in which case essentially you need to load the whole lot into one table as it is (easier to work on once it is in the database), then pull out a list of services, a list of customers, and join the two up - normalisation.

 

[James Mikkelson]

Hello ABCStudio

Do you have any knowledge of any programming languages or access to programmers within your business? Reason I ask is that this kind of job is so easy in languages such as PHP - you can sort out duplicate rows, send to various tables.

1000 rows of data to MySQL is a very small overhead indeed. What you have specified above you could complete in 5-10 lines of PHP code depending on your CSV file.

May well be something worth considering.

Best of luck,
James

 

[ABCStudio]

Ended up just spliting it into to sheets importing those into the database using phpmyadmin and running a couple of queries to sort it out and tidy it up.

Just seemed like it would be such a common problem that there might have been a simpler way to sort it out.

I guess sometimes there are just no shortcuts!

 

[matt.chatterley]

Ended up just spliting it into to sheets importing those into the database using phpmyadmin and running a couple of queries to sort it out and tidy it up.

Just seemed like it would be such a common problem that there might have been a simpler way to sort it out.

I guess sometimes there are just no shortcuts!

Nice one.

Sometimes no - but there are always "longcuts"

If it was something regular it'd be worth more work - but glad to hear you've sorted it.