2

I have a text file to be imported in a MySQL table. The columns of the files are comma delimited. I set up an appropriate table and I used the command:

load data LOCAL INFILE 'myfile.txt' into table mytable FIELDS TERMINATED BY ‘,’;

The problem is, there are several spaces in the text file, before and after the data on each column, and it seems that the spaces are all imported in the tables (and that is not what I want). Is there a way to load the file without the empty spaces (other than processing each row of the text file before importing in MySQL)?

Taz
  • 3,718
  • 2
  • 37
  • 59
mcastel
  • 121
  • 1
  • 5
  • possible duplicate of [Mysql: How can I use RTRIM in my LOAD DATA INFILE query?](http://stackoverflow.com/questions/4162362/mysql-how-can-i-use-rtrim-in-my-load-data-infile-query) – David Dec 27 '10 at 15:45

2 Answers2

2

As far as I understand, there's no way to do this during the actual load of the data file dynamically (I've looked, as well).

It seems the best way to handle this is to either use the SET clause with the TRIM function

("SET column2 = TRIM(column2)")

or run an update on the string columns after loading, using the TRIM() function.

You can also create a stored procedure using prepared statements to run the TRIM function on all columns in a specified table, immediately after loading it.

You would essentially pass in the table name as a variable, and the sp would use the information_schema database to determine which columns to upload.

Tom
  • 3,009
  • 1
  • 18
  • 23
ChoNuff
  • 814
  • 6
  • 12
0

If you can use .NET, CSVReader is a great option(http://www.codeproject.com/KB/database/CsvReader.aspx). You can read data from a CSV and specify delimiter, trimming options, etc. In your case, you could choose to trim left and right spaces from each value. You can then either save the result to a new text file and import it into the database, or loop through the CsvReader object and insert each row into the database directly. The performance of CsvReader is impressive. Hope this helps.

bouvierr
  • 3,563
  • 3
  • 27
  • 32