Just use something else beside commas to delimit your file. I have a
file that I need to regularly import that has commas, tabs and returns
in the data. I delimit the fields with ^^ and the records with ~~.
You can tell MySQL what the delimiters are when you import the data.
On Feb 20, 2004, at 3:45 PM, Chris Fossenier wrote:
> What is the best way to deal with white space in a mysql database?
> What I'm talking about is trailing spaces in larger fields. We receive
> in fixed width format, so when you import it, you get a bunch of
> white spaces that take up space. Multiply these white spaces by
> records and it adds up.
> I wrote a C program to parse out the file and eliminate white space
> and put
> the data into a comma-delimited format. Now I have a new problem. I
> that there are some commas in the data and that is resulting in an
> innacurate load (the commas in the data through off the LOAD INFILE
> I'm guessing).
> So..if I stick with the fixed field, my load gets all records but I
> get a
> bunch of extra data in the form of spaces.
> if I parse the file and put it into comma-delimited, I miss some data
> because of commas in the data.
> Any tips are appreciated.
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments