CODEWITHSUNDEEP
mysqlfile-upload
mcastel
mcastel

Reputation: 121

How to load data into a MySQL table without spaces?

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)?

Upvotes: 2

Views: 3952

Answers (2)

ChoNuff
ChoNuff

Reputation: 822

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.

Upvotes: 2

bouvierr
bouvierr

Reputation: 3781

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.

Upvotes: 0

Related Questions