I'm looking for a clever way to extract 500 plus lines of data from an excel spreadsheet and enter is into my database.
The spreadsheet is like this
My table 'tbl_foot_teams' is set out as
id | name | rating
Quite simply, I need to enter get the two columns from the spreadsheet into the database fields name and rating.
Is there any efficient way to achieve this? Individually, it will take me a ridiculous amount of time!
Thanks
Save Excel file as CSV and use LOAD DATA INFILE command to import data.
Your excel file has no id field. Make id field in the table as AUTO_INCREMENT, and use command like this -
LOAD DATA INFILE 'file_name.csv' INTO TABLE tbl_foot_teams
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
-- IGNORE 1 LINES -- if csv file has column headers
(name, rating)
SET id = NULL; -- this will set unique value for each row
Also, have a look at GUI Data Import tool (Excel or CSV format) in dbForge Studio for MySQL.
In phpmyadmin you have an Import From Excel option.
If you don't have one, you may have Import From CSV, so just convert the spreadsheet to CSV.
If you have none of above, you can write a php function that opens a text file, makes explode by rows and then explode by values
If we are talking about 50 rows, you can create easily a new column on spreadsheet with a formula to concatenate your values to a insert statement. Something like:
=concat( "insert into tbl_foot_teams ( name , rating) values ( " , $b8 , " ...
then, copy paste calculate formula text result on your database.
You don't specify what database you're using, but an easy way to do this with MySQL would be to export the spreadsheet as a csv file and then import to MySQL with mysqlimport.
This is described in a comment on this MySQL page, from user Philippe Jausions:
If you are one of the many people trying to import a CSV file into MySQL using mysqlimport under MS-Windows command/DOS prompt, try the following:
mysqlimport --fields-optionally-enclosed-by=""" --fields-terminated-by=, --lines-terminated-by="\r\n" --user=YOUR_USERNAME --password YOUR_DATABASE YOUR_TABLE.csvBetween quotes " and backslashes \ it can really give you a hard time finding the proper combination under Windows...
I usually run this command from the folder containing the YOUR_TABLE.csv file.
If you have a header in your .csv file with the name of columns or other "junk" in it, just add a --ignore-lines=X to skip the first X lines (i.e. --ignore-lines=1 to skip 1 line)
If your fields are (optionally) enclosed by double-quotes " and which themselves are doubled inside a value (i.e. a double double-quote "" = 1 double-quote ") then also use --fields-escaped-by=\ (default) and NOT --fields-escaped-by="""
After converting to a CSV file, you can import into any database that supports importing from CSV files (MySQL, PostgreSQL, etc.) but you would have to do this from the command-line:
You can use a programming language such as Python and use a library for it such as the Excel spreadsheet reading library and then another library for interfacing with your SQL database.
You connect to the data, load the Excel file, and loop through each row and extract whichever column data you want. Then you take that data and execute the INSERT statement.
If you have an installation of phpMyAdmin on a server, you can use the Import from CSV option though you would first have to re-save your Excel spreadsheet as a CSV (Comma-Separated Values) file.
Working from the Excel end, you can use ADO, for example Excel VBA: writing to mysql database
Dim cn As ADODB.Connection
''Not the best way to get the name, just convenient for notes
strFile = Workbooks(1).FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
Set cn = CreateObject("ADODB.Connection")
''For this to work, you must create a DSN and use the name in place of
''DSNName, however, you can also use the full connection string
strSQL = "INSERT INTO [ODBC;DSN=DSNName;].NameOfMySQLTable " _
& "Select AnyField As NameOfMySQLField FROM [Sheet1$];"
cn.Execute strSQL
