Import From Excel

[andydeans]

Hi,

My MYSQL database has set columns for the data.

The users have spreadsheets setup with their own columns which holds pretty much the same data on it.

name, address etc.

is the simpliest way to import it, to change the column headings on the spreadsheet for each user to represent my column names in mysql database and import csv via phpmyadmin?

Many thanks for you help

[chrishea]

When you import to mysql you don't want the first row with column headings in the file. It strictly depends on the order of the columns. The easiest way would be to have the users convert to a standard format that is consistent with what you need for the DB. If that isn't possible, then it depends how often you plan to do this. If it is a one-time deal, (and there aren't many spreadsheets) then it might be quicker to cut and paste from the current spreadsheets into the format you need. If you have many spreadsheets or you need to do this regularly, then you could certainly upload the spreadsheets, read the temp file that is created, convert to the appropriate format (new temp files) and then import into the database.

[drjohn]

Arrange the order of the columns to match that of the database, and every column in the database must have a matching column in the spreadsheet.

Export the data from the spreadsheet as txt (tab delimited) not csv.
no column headings, as already mentioned.

Run a query like this.

load data local infile "C:/Documents and Settings/dr john/My Documents/logpile/AccountDataExport_2010-03-26.txt" into table mycounts;

I do this every week to update an online database with an extract from an offline one.

I do this remotely using SQL Manager Lite for MySQL, which is free from EMS.

[andydeans]

thanks for your help.

drjohn i think i will give that a go with re arranging the columns to match db fields.
thanks