load-data-infile

Program for working with large CSV Files

Are there any good programs for dealing with reading large CSV files? Some of the datafiles I deal with are in the 1 GB range. They have too many lines for Excel to even deal with. Using Access can be a little slow, as you have to actually import them into a database to work with them directly. Is there a program that can open large CS...

Easy way to export a SQL table without access to the server or phpMyADMIN

I need a way to easily export and then import data in a MySQL table from a remote server to my home server. I don't have direct access to the server, and no utilities such as phpMyAdmin are installed. I do, however, have the ability to put PHP scripts on the server. How do I get at the data? I ask this question purely to record my way ...

How do I import a whitespace-delimited text file into MySQL?

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...

Loading flatfiles into a normalized MySQL database

What is the fastest way to load data from flatfiles into a MySQL database, and then create the relations between the tables via foreign keys? For example... I have a flat file in the format: [INDIVIDUAL] [POP] [MARKER] [GENOTYPE] "INDIVIDUAL1", "CEU", "rs55555","AA" "INDIVIDUAL1", "CEU", "rs535454","GA" "INDIVIDUAL1", "CEU",...

mysql import script

Is it possible to import csv data into mysql and automatically create the column names, as in can I create just the table, or must I create the table names as well? Is it possible to check for duplicate entries upon importing? I have an identifier field, but dont know how to make it so it will not be imported twice. How would you impor...

What is the best way to achieve speedy inserts of large amounts of data in MySQL?

I have written a program in C to parse large XML files and then create files with insert statements. Some other process would ingest the files into a MySQL database. This data will serve as a indexing service so that users can find documents easily. I have chosen InnoDB for the ability of row-level locking. The C program will be gene...

How does 'LOAD DATA INFILE' work in statement-based replication?

In MySQL, using statement based replication I assume that every SQL statement gets executed on the slave exactly the same way as on the master. However, when I run the statement: LOAD DATA INFILE 'filename' INTO SomeTable; I'm not sure if the file needs to exist on the slave the same way at it does on the master. Can someone point me ...

How do I 'load data' with multiple tables?

I have three tables. For simplicity let's say they are: tbl1, tbl2, tbl3 both tbl2, and tbl3 have f-keys linking to tbl1 I am using 'load data infile...' as I really need the +16,000 writes/second I am getting right now with it. I don't use auto-increment because I need the f-keys for my child records. So I create my txtfile on the ...

problem while importing the data from text to mysql

Hi, I have used an application to import some data from a text file to mysql. I have used the following code. try { stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); stmt1= (Statement) conn.createStatement(); int deleteRows = stmt1.executeUpdate("delete fro...

Error in mysql while importing from csv

This is my command line query. mysql> load data local infile "c:\\re\\30-11-08.csv" into table powerdata(Date, DG1, DG2, DG3, Dg4, DG5, ChillerPanel1, ChillerPanel2, ChillerPanel3, ChillerPanel4,1st_Floor, 2nd_Floor, 3rd_Floor, 4th_Floor, UPS1, UPS2, UPS3, UPS4, UPS5,Server_Power, Cooling_Power) fields terminated by...

MySQL Query, bulk insertion

I have a bulk data for insertion in MYSQL Tables, let use suppose, 10k in one time, What I am doing is store the data in an XML file and then go for insertion (data is around 50K rows), It will take a lot of time, Is there any option for bulk insertion in MySQL tables. Thanks in advance, Please help. ...

SQL Loader : Load into 2 Tables from 1 controlfile and 1 CSV

Table T1 Structure:col1 number,col2 number Table T2 Structure: col1 number,col2 number,col3 number csv file: row1:1,2,3,4,5,6 row2:1,2,3,4,5,6 I want data in Table T1 as: col1 col2 ===== ==== 1 3 1 3 I want data in Table T2 as: col1 col2 col3 ===== ==== ==== 1 3 5 1 3 5 following ...

Switching from MySQL to PostgreSQL - tips, tricks and gotchas?

I am contemplating a switch from MySQL to PostgreSQL. What are your tips, tricks and gotchas for working with PostgreSQL? What should a MySQLer look out for? See also: How different is PostgreSQL to MySQL? See also: Migrate from MySQL to PostgreSQL Note - I don't think this is a duplicate. In particular the type of answers are ...

Load inFile Data in Mysql

I have a Text file of Size 1.5GB or around 1.3Crores of Rows. When I am Loading this text file to the Table of same structure in MYSQL, Its Loads only 62Lack records, shows 65K warnings and Finished. Whatis the reason, Why It loads only half of records??? Do I need to increase Buffer Size?? If yes then How?? or there is some other rea...

What mysql settings affect the speed of LOAD DATA INFILE?

Let me set up the situation. We are trying to insert a modestly high number of rows (roughly 10-20M a day) into a MyISAM table that is modestly wide: +--------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+-------...

C# transfer data from mssql to mysql

I need to transfer data from sql server 2008 express to MySql. The data is around 2.000.000 records in total and 4 tables. In C# I can insert these records very quickly with the Table-Valued parameter. (Around 50 seconds). Within a transaction. Now I was wondering if there is something similar for MySql that I can do in C#. ...

Mysql permission errors with 'load data'

I am running into a permission error when trying to load data from a flat file database dump into a new table. I know that the schema of the file and my table is the same and I tried tweaking the permissions. What else should I try? mysql> load data infile 'myfile.txt' into table mytable fields terminated by ',' enclosed by '"'; ERROR 1...

mysqldump table without dumping the primary key

I have one table spread across two servers running MySql 4. I need to merge these into one server for our test environment. These tables literally have millions of records each, and the reason they are on two servers is because of how huge they are. Any altering and paging of the tables will give us too huge of a performance hit. Becau...

Batch insertion of data to MySQL database using php

I have a thousands of data parsed from huge XML to be inserted into database table using PHP and MySQL. My Problem is it takes too long to insert all the data into table. Is there a way that my data are split into smaller group so that the process of insertion is by group? How can set up a script that will process the data by 100 for ex...

collecting mysql statistics

What would be the easiest way to count the new records that are inserted into a database? Is it possible to include a count query in with the load query? Or is something more complex needed, such as recording the existing last record and counting everything added after it? edit: I have a cron job, that uses LOAD DATA INFILE in a scrip...