Import an existing list from Excel
Posted by: Roguish (---.brig.adsl.virgin.net)
Date: March 20, 2008 10:35AM

I have had WAMPServer for about 2 months and have tried and tried but to no avail to import an existing list of search records from an Excel file. For some reason I cannot even import a single record and would be grateful if someone could help.

I am a complete novice with this and cannot find anywhere the process of how and what you upload. I realise that there is some script needed to create the search page.

So, any help with

Importing data, and
Uploading

would be great!

Options: ReplyQuote
Re: Import an existing list from Excel
Posted by: toivo (203.19.130.---)
Date: March 20, 2008 11:09AM

Hi,

If you save the Excel file as tab limited file or 'Text (Tab delimited) (*.txt)', you can then import it into an existing MySQL table either through a query in MySQL Query Browser or equivalent, or write a script to do that.

Here is an example of the query that does the import from the c:\temp folder of the local workstation, which is not the same as the MySQL server:


LOAD DATA LOCAL INFILE "c:/temp/example.tab.txt"
INTO TABLE `my_database`.`my_table`
IGNORE 1 LINES

For more info, check out the MySQL reference manual at [dev.mysql.com]

PHP has functions to read CSV (comma separated value) files, but saving Excel spreadsheets as CSV files produce inconsistent results if some of your alphanumeric fields have commas. I found tab limited files easier to handle. You may still have to write an import script to convert the values like remove quotation marks from alpha fields.

Regards,

toivo
nottingham, uk

Options: ReplyQuote
Re: Import an existing list from Excel
Posted by: Roguish (---.brig.adsl.virgin.net)
Date: March 20, 2008 01:00PM

Thanks for your reply toivo!

I saved the Excel file as 'Tab delimited' as you recommended and imported the file. The result was an error on line 112 and so thought that it must be the fact that there are '£' symbols so I removed all of those and saved the file again and another error on line 122. The weird thing was was that ther is nothing in any of the cells that indicate an error of any kind.

Options: ReplyQuote
Re: Import an existing list from Excel
Posted by: toivo (203.19.130.---)
Date: March 20, 2008 01:22PM

Does the MySQL error message or error give any clues? The errors would probably be cause by the way the primary key, if any, was defined and the data types of the columns.

Regards,

toivo
nottingham, uk

Options: ReplyQuote
Re: Import an existing list from Excel
Posted by: Roguish (---.brig.adsl.virgin.net)
Date: March 20, 2008 01:59PM

There are 3 warnings all saying the same


Warning: mb_strpos() [function.mb-strpos]: Unknown encoding or conversion error. in C:\wamp\apps\phpmyadmin2.10.1\libraries\string.lib.php on line 112

Beneath this within a bordered section titled Error is reads

There is a chance that you may have found a bug in the SQL parser. Please examine your query closely, and check that the quotes are correct and not mis-matched. Other possible failure causes may be that you are uploading a file with binary outside of a quoted text area. You can also try your query on the MySQL command line interface. The MySQL server error output below, if there is any, may also help you in diagnosing the problem. If you still have problems or if the parser fails where the command line interface succeeds, please reduce your SQL query input to the single query that causes problems, and submit a bug report with the data chunk in the CUT section below:

Followed by a huge string of code and ending with the data I tried to import.

Options: ReplyQuote
Re: Import an existing list from Excel
Posted by: toivo (---.nsw.bigpond.net.au)
Date: March 20, 2008 05:59PM

That sounds strange. Obviously phpMyAdmin does not like your data :-)

I always use MySQL Administrator from MySQL and its MySQL Query Browser for importing and running queries.

Regards,

toivo
nottingham, uk

Options: ReplyQuote
Re: Import an existing list from Excel
Posted by: manomysql (117.220.212.---)
Date: March 19, 2017 03:42PM

Sir,
I am not able to import data in mysql table from text file in WAMP SERVER . i have given comand

>LOAD DATA LOCAL INFILE 'c:\temp\pol.txt' into table test fields terminated by '';

but error given no such file or directory..
please help me

Manoj

Options: ReplyQuote
Re: Import an existing list from Excel
Posted by: RiggsFolly (Moderator)
Date: March 19, 2017 03:45PM

Try


LOAD DATA LOCAL INFILE 'c:/temp/pol.txt' into table test fields terminated by '';

---------------------------------------------------------------------------------------------
(Windows 7 Pro 32bit) (Wampserver 3.0.6 32bit)
<Apache 2.4.23/2.2.31> <PHP 7.1.2/7.0.16/5.6.28/5.5.38/5.4.45/5.3.29> <MySQL 5.7.10/5.6.17/5.5.28>
<MariaDB 10.1.21> <phpMyAdmin4.6.5.2> <MySQL Workbench 6.3.6.511>

Read The Manuals Apache -- MySQL -- PHP -- phpMyAdmin -- WAMPServer alternate Repo
-?-?-?- Backup your MySQL databases regularly Here is How dont regret it later! Yes even when developing -?-?-?-

Options: ReplyQuote


Sorry, only registered users may post in this forum.