I need to import largish (24MB) text files into a MySQL table. Each line looks like this: 1 1 0.008 0 0 0 0 0 There are one or more spaces after each field, and the last field is tailed by about 36 spaces before the newline. How do I import such a file into MySQL? From the documentation it seems that LOAD DATA expects all fields...
First, some disclosure: I am not a Linux admin, and my Linux admin is not a programmer. That said, we have a cronjob that runs a mysqlimport command to import a text file that's generated daily. I have no control or involvement in how this file is created. Through trial and error, we discovered that the text file is generated on a Wi...
We have a large tab-delimited text file (approximately 120,000 records, 50MB) that we're trying to shove into MySQL using mysqlimport. Some fields are enclosed in double-quotes, some not. We're using the fields-optionally-enclosed-by='\"' switch, but the problem is some of the field values themselves contain double-quotes (indicating i...
Once a day I need to update an MySQL table with a new file downloaded from the Net using ftp and then mysqlimport. However, I want my website to keep running smoothly during the mysqlimport operation, which takes quite some time (it's a big table). What would be a good way to assure that users do not wait for the import to finish? I am...
I have a csv file that has a date field in a format like (among other fields): 17DEC2009 When I do a mysqlimport, the other fields are imported properly, but this field remains 0000-00-00 00:00:00 How can I import this date properly? Do I have to run a sed/awk command on the file first to put it into a proper format? If so, what ...
I like mysqlimport, its fast and relatively easy to use (much faster then say, PHP or Python front-ends for importing > 20GB data files). However I'd like to do the following. I have a data file that looks like: cat dog 7 Cat Dog 3 CaT DOG 1 with the fields as varchar, varchar, int And I would like the final result to be stored as ['...
As the title says: I've got a bunch of tab-separated text files containing data. I know that if I use 'CREATE TABLE' statements to set up all the tables manually, I can then import them into the waiting tables, using 'load data' or 'mysqlimport'. But is there any way in MySQL to create tables automatically based on the tab files? See...
I'm importing some data from a .txt file into a MySQL database table, using mysqlimport. It seems to import OK (no error messages) but looks very odd when displayed, and can't be searched as expected. Here are the details. The original text file is saved in UTF-8, with records that look (in a text editor) like this. The second field inc...
Sorry - this was an accidental duplicate of http://stackoverflow.com/questions/2188522/importing-text-to-mysql-strange-format, don't know how to delete question :( If an editor sees this, please delete. ...
I have successfully dumped an entire MySQL database using mysqldump --databases generating a nice .txt file. However, I can't see how to read the whole file back into MySQL in one go; mysqlimport seems to want just one table at a time. ...
Hi all, I've been playing with mysqlimport and I've run into the restriction where the filename has to be the same as the table name. Is there any way to work round this? I can't rename the file as it is used by other processes and I don't want to copy the file as there will be many of them, some being very large. I want to use mysql...
When I export a database on my development PC, for import on my webhost, it contains the following line: -- -- Table structure for table `vi_sr_videntity_0` -- CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `starrise`.`vi_sr_videntity_0` AS select `starrise`.`t_sr_u_identityfingerprint`.`c_r_Identity` A...
I have an export from a MYSQL database on a linux machine however when importing that database into MYSQL on windows all of the table names that were camel cased are now all lower case. The sql dump has the correct case in it but the importing via phpmyadmin seams to remove these. How can I import it and keep the case? Thanks ...
I'm importing a legacy db to a new version of our program, and I'm wondering if there's a way to not import some columns/tables from the dump, and rename other tables/columns as i import them? I'm aware I could edit the dump file in theory, but that seems like a hack, and so far none of my editors can handle opening the 1.3 gb file (Yes...
Hi, is it possible to leave out the last line of a CSV file, because i have a huge file, like 300.000 records in a CSV file and at the end i have END because else we had some issues with the FTP server not giving complete files as Oracle was still writing to it. so now I would like to import the CSV file with mysqlimport but leave out ...
We are running an import of an existing product table into a new table of our own. The import script we've written runs perfectly and inserts the right amount of rows (6000 or so). However, after the import the next auto incremented primary key/id is 1500 entries (or so) above the number of rows in the table. We can't understand why MyS...
I inherited a poorly created mysql database and now I need to migrate data to a new server. Long story short, I need to keep it stored this way and I use phpmyadmin. Know of any tools to help the migration of this 1.2GB mysql table? Hope I don't get slaughtered for this post... ...
I don't have shell access. Database of 8,000 database entries with images and max I can get at a time with the max_allowed_packets parameter is about 30-35. Tried bigdump to no avail. Also downloaded numerous other items. Shell access is not working on the server. Tried to change the max_allowed_packet param in php.ini, my.ini, ...