Member Avatar for teedoff

Ok back again. lol I just purchased a new hosting provider and was trying to transfer my database over to my new site. I exported the tables using Navicat and "tab" as the delimiter. I have three tables and they all saved as text files and look fine when I open them. I went into my DB admin in my sites control panel and that of course opened phpMyAdmin page where I can import these text files into my new db. When I try this however, I get an error for all three text files that really isnt very descriptive of how or why its occurring. Here is the error:

SQL query: 

1Philips2Eiko3Donar4Sylvania5Hikari6GE7Osram8Kandolite9Ushio10Westinghouse11Unknown12Demo13Generic14Thorn15Halco16Perko17Narva18CEC19Import20Orbitech21Prism22Plus23EYE24Plusrite25Hosobuchi26Wagner27Carley28Welch Allyn29Nikon30Topcon31Radiac32LTI33Major 

MySQL said:  

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1    Philips
2    Eiko
3    Donar
4    Sylvania
5    Hikari
6    GE
7    Osram
8    Kandolite
9    ' at line 1

I tried importing all three tables separately, but no luck. I see it has that apostrophe "at line 1" Not sure if thats part of the error message, but I dont see any strange characters in my text files. Any help would be greatly appreciated.

  • 2 Contributors
  • 4 Replies
  • 126 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by mwasif

Dani AI

Generated

Good that got the data moved with Navicat. For others who see a MySQL “syntax” error when uploading a plain text file: phpMyAdmin will try to execute whatever you upload as SQL unless you tell it otherwise or import into a table with the correct format selected. That is the usual cause when a tab-delimited file is treated like SQL and the server chokes.

A short checklist to import a tab-delimited file safely in phpMyAdmin:

  • Select the database and then the target table (not just the database root).
  • Open Import, choose the file and set Format to CSV (or the CSV import options).
  • Set Fields terminated by to \t and Lines terminated by \n (or \r\n for Windows files).
  • If the file has a header row, set “Ignore 1 line” or remove the header before importing.
  • Pick the correct character set (usually utf8) and ensure the column order matches the table.
  • If the file is large, use command-line tools or a chunked importer rather than phpMyAdmin.

If you prefer a direct SQL-style import, LOAD DATA is fast and reliable (host permitting). Example:

LOAD DATA LOCAL INFILE '/path/to/file.txt'
INTO TABLE your_table
FIELDS TERMINATED BY '\t'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(col1, col2, col3);

Troubleshooting notes: remove any UTF-8 BOM from the start of the file, normalize line endings, verify every row has the same column count, and check PHP's upload limits or the MySQL secure_file_priv / local_infile settings if imports fail. As pointed out, a full SQL dump (schema + data) avoids delimiter issues and is often the safest way to move an entire database between hosts.

Member Avatar for teedoff

Nevermind...lol I had tried and thought I made a new connection with Navicat, but in fact hadn't. So I created a new conneciton again and then just copied and pasted the tables from my old hosting site to my new site using Navicat. Awesome!

Member Avatar for mwasif

Welcome back.

I exported the tables using Navicat and "tab" as the delimiter.

I always prefer exporting database as an SQL file. In SQL you don't need to be careful about delimiters, formatting etc.

Member Avatar for teedoff

Hi mwasif, thats good information and I will try it out in the future! Thanks for the help. Until I learn more SQL, I will probably stick with Navicat. Its so easy and I have alot of others issues and problems Im trying to solve for a new site! It never ends! lol

Member Avatar for mwasif

There is no problem sticking with Navicat. Navicat also gives the option to export DB as an SQL file :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.