Board index   FAQ   Search  
Register  Login
Board index php forum :: Database phpMyAdmin

Importing table from text file

How to use phpmyadmin.... phpmyadmin related arguments. Even phpMyAdmin issues

Moderators: macek, egami, gesf

Importing table from text file

Postby Fife Club » Fri Oct 11, 2002 6:57 am

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.
User avatar
Fife Club
New php-forum User
New php-forum User
 
Posts: 15
Joined: Wed Oct 09, 2002 7:50 pm
Location: Charlotte, NC, USA

Postby Jay » Fri Oct 11, 2002 1:11 pm

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!
Jay
 

Postby Fife Club » Fri Oct 11, 2002 2:02 pm

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.
User avatar
Fife Club
New php-forum User
New php-forum User
 
Posts: 15
Joined: Wed Oct 09, 2002 7:50 pm
Location: Charlotte, NC, USA

Postby Fife Club » Mon Oct 14, 2002 7:53 am

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.
User avatar
Fife Club
New php-forum User
New php-forum User
 
Posts: 15
Joined: Wed Oct 09, 2002 7:50 pm
Location: Charlotte, NC, USA


Return to phpMyAdmin

Who is online

Users browsing this forum: No registered users and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.