Importing table from text file

[Fife Club]

I have a text file that I successfully uploaded into a mySQL database table. The only problem is that starting with the second line, every row places a space before the first item in the row. This causes a problem because when I sort by the first column ("lastname"), everyrow after the first sorts, then the first row ends up on the bottom of the list (because that's the only one that doesn't end up starting with a space).

Here's an example from the top 3 rows of my text file (I separated the lines more to make it easier to distinguish new lines):

Code: Select all

Addino;;Sam;1996;New York;NY;United States;Freelance;;845-359-1789;staddino@aol.com;;;;;20010901

Albury;;Thomas W.;1991;;;;Assistant Editor;Sony Music Studios;212-724-8915;gotakeahike@hotmail.com;;;;;20011001

Amari;;Vince;1984;Montreal;PQ;Canada;Colorist;Covitec;;vam@bigfoot.com;;;;;


In this example, the sorted list would begin with "Albury" on top, followed by "Amari", and so on.... with "Addino" at the very bottom. Thinking that this may have been caused by not terminating the end of each line, I added a "#" to the end of every single line and tried again, telling phpMyAdmin that lines terminated with "#" (without the quotes). I still ended up with the same results. How do I get all this data into my table but without a space before the first item in every row (except the first row)?
:
Please help. Thank you.

[Jay]

Now that you've got your data in the database, I suppose the easiest and fastest way would be to construct a simple query that pulls each row out, trims all the fields, and updates it in the database!

[Fife Club]

Well if somebody knew why it was happening and how to avoid it, it would be pretty easy for me to reimport the data.

Otherwise, are you saying that I can instruct mySQL to take the first field of every row, remove the space (or the first character), and leave the remaining characters in tact? How is that done in phpMyAdmin(*)

*One important note. When looking at the first field in any of these rows in phpMyAdmin, it does not show me the existance of a space. Not even after I hit "edit". When the results print on the page it doesn't show the space either. The only thing that confirms that there is a space hidden in there is thru my "update" php page. Why does one show the space and the other doesn't... I don't know.

[Fife Club]

Follow Up:

I kinda figured it out - or at least I found a work around. It seems that the spaces in each row after the first were caused by the "return" that brought me to the next line. The way I fixed it was to put notepad in no-wrap and to backspace the beginning of every row so it immediately followed the end of the row above it (which was terminated by the pound symbol). This ended up in one HUGE line of code with no breaks. I reimported the text data and it worked. Thanks for everybody's help.