new request

[rhock]

I have the following table:

CREATE TABLE bus_list (
bl_id smallint(5) NOT NULL auto_increment,
bl_business varchar(128) NOT NULL default '',
bl_address varchar(128) default NULL,
bl_city varchar(128) default NULL,
bl_zip varchar(10) default NULL,
bl_email varchar(128) default NULL,
bl_www varchar(128) default NULL,
bl_phone text NOT NULL,
PRIMARY KEY (bl_id),
UNIQUE KEY id (bl_id),
KEY id_2 (bl_id),
KEY bl_id (bl_id),
KEY bl_id_2 (bl_id)
) TYPE=MyISAM;

---

Can someone please show me(EXACTLY) how the following "listing" needs to be edited...so when entered into bus_list all the righ data will be entered into the right columns/rows...?

Business Name
Street address
city, ST, ZIPCODE
www address
phone number

please show me an edited version of this "listing" WITH exact syntax of the text file to insert into phpmyadmin...?

Thanks in advance...

Richard

[theeper]

Ideally, you will convert your data file to something like this, assuming city and state go in the same field since there is no column in the table for state ...

null;Business Name;Street address; city ST;ZIPCODE;www address;phone number;
null;Business Name;Street address; city ST;ZIPCODE;www address;phone number;
null;Business Name;Street address; city ST;ZIPCODE;www address;phone number;

Then, if you are going to use phpMyAdmin to load it, it should default to semicolon for field terminator and \r\n for line terminator.

[rhock]

I have over 40,000 of the business listings like I described....
if I have to edit each one as you have shown I will be much better off entering the data individually with a form or manually through phpmyadmin... either way it is not feasable or cost effective... it would take months to edit the lists...

I was under the impression there was an easier way to enter text file into databases... if there is not I see no real advantage of usin a database... I can just use a searchable directory on the web server...

any imput is welcome...

tia
richard...

[rhock]

Ideally, you will convert your data file to something like this, assuming city and state go in the same field since there is no column in the table for state ...

null;Business Name;Street address; city ST;ZIPCODE;www address;phone number;
null;Business Name;Street address; city ST;ZIPCODE;www address;phone number;
null;Business Name;Street address; city ST;ZIPCODE;www address;phone number;

Then, if you are going to use phpMyAdmin to load it, it should default to semicolon for field terminator and \r\n for line terminator.<<

why is there 3 sets ?

null;Business Name;Street address; city ST;ZIPCODE;www address;phone number;
null;Business Name;Street address; city ST;ZIPCODE;www address;phone number;
null;Business Name;Street address; city ST;ZIPCODE;www address;phone number;

[theeper]

I put three sets to show you what the entire file should look like. You have to put in delimiters, otherwise the DB engine doesn't know where one field ends and the next begins, nor does it know when one row ends and the next begins.

This could be done with a simple search and replace in MS Word or some other text editor. Obviously you won't edit them all by hand, I didn't expect you to.

For instance, you may replace all instances of \r\n with a semicolon. This would give you a file that looks something like this ...

Business Name;Street address;city, ST, ZIPCODE ;www address ;phone number;;Business Name;Street address;city, ST, ZIPCODE ;www address ;phone number;;Business Name;Street address;city, ST, ZIPCODE ;www address ;phone number;

Notice the two semicolons before each "Business Name", this is because there was a blank line between each "row" of data. Use this to make up for the fact that you have a different number of data fields in your text file than your table has columns. Do a replace of ";;" (two semicolons) with ";\n\rnull;". This would make your data look something like this, wiht the null column there for your auto_increment column ...

null;Business Name;Street address;city, ST, ZIPCODE ;www address ;phone number;
null;Business Name;Street address;city, ST, ZIPCODE ;www address ;phone number;
null;Business Name;Street address;city, ST, ZIPCODE ;www address ;phone number;

The last issue you have is the commas that seperate city, ST and ZIPCODE. I would replace commas with a semicolon so your data looks something like this ...

null;Business Name;Street address;city; ST; ZIPCODE ;www address ;phone number;
null;Business Name;Street address;city; ST;ZIPCODE ;www address ;phone number;
null;Business Name;Street address;city;ST; ZIPCODE ;www address ;phone number;

Now, since you don't have a field for ST, you can handle it a couple ways. The easiest in my view would be to use a comma seperated list of columns to add only the columns you need(phpMyAdmin supports this), or to add a temporary field for ST in the table, import the data and then delete the extra column.

[rhock]

ok I am starting to catch on a little...
I would much rather restructure the tables that create extra work in editing the data before it can be entered...

I am still completely in the dark about the structure of the text file need to enter into the "Insert textfiles into table" function of phpmyadmin...

if I create a csv file from my listings ( hardly worth the effort with 40,000 listings!) (which I have no idea how to do )

do I past something like the following to the top of it ?

null;Business Name;Street address; city ST;ZIPCODE;www address;phone number;
null;Business Name;Street address; city ST;ZIPCODE;www address;phone number;
null;Business Name;Street address; city ST;ZIPCODE;www address;phone number;

followed by the CSV data ?

like I said from the beginning, I wanted help creating the tables to accept the data I alerady have in the format it is in... I understand it will require some editing ( seperating lines/rows)

Thanks

[theeper]

You can't create a table to put that data in the way it is, there is no proper delimiters in place. Like I said, the DB engine doesn't know how to seperate that data. You have to make it proper by replacing certain characters. You need a field delimiter in place and a row delimiter in place. How you get them there is up to you. Since most of your fields are on one line each, they are easy. You know that they have a newline at the end of them. The problem is twofold.

1. You don't have a field for the ID number at all.
2. You need to seperate the fields that contain city, ST an ZIPCODE into pieces.

So, if you replace every instance of newline with a semicolon, you get one semicolon after each field and two semicolons at the end of each row. Then replace each instance of double semicolons with a newline character. Add the extra field for ST in the table. Do your LOAD DATA query and then drop the extra field.

Of course all this depends on the data you have. If some fields are likely to already contain a semicolon, then use a different character for a delimiter.

[theeper]

Those three lines are the data, this is what I mean your file should look like (only 3 lines excerpted) ...

null;Jim's Car Lot;123 Main St;Oakland;CA;12345;www.jimscars.com;555-5555;
null;
Joe's Bowling Alley;456 Main St; LA ;CA;90021;www.joes.com;444-4444;
null;Fred's Homes;789 Test St; Chigaco;Il;55464;www.fredshomes.org;777-7777;

[rhock]

I am trying to make this as simple as possible and it just gets more complex...!

I will restructrue the format of my test so it is EXACTLY like what is shown below...:

business name
street address
zip code
phone #
www address

I will create a NEW table with 5 fields... can you show me what is required?

CREATE TABLE bus_list (

);

[theeper]

For that, all you need is this schema.

CREATE TABLE bus_list (
bus_name VARCHAR(64) NOT NULL,
bus_street VARCHAR(36) NOT NULL,
bus_zip VARCHAR(16) NOT NULL,
bus_phone VARCHAR(24) NOT NULL,
bus_www VARCHAR(64) NOT NULL
)

There is no column for an ID number though, which could make managing the data a real pain.

[rhock]

ok... now to get data into the tabel... from the very top of the file... what is the exact wording (entire file) needed to load the data...?

doees it startt something like : " load data" etc? and then

null;Business Name;Street address;city, ST, ZIPCODE ;www address ;phone number;
null;Business Name;Street address;city, ST, ZIPCODE ;www address ;phone number;

why is the word "null" needed ?

thanks again... I am getting more out of this exchange than all the online tutorials ( that none of them work using my config so they are worthless to me ) put together...!

don't stop now if you help me through this I will be more than greatful...

Richard...

[theeper]

You just need the data in the file, that is it. The data should look like this ...

business name
street address
zip code
phone #
www address

business name
street address
zip code
phone #
www address

etc..

Then you do a LOAD DATA query, something like this ...

LOAD DATA INFILE "data.txt" INTO TABLE bus_list FIELDS TERMINATED BY '\n' LINES TERMINATED BY '\n\n'

If you do it wth phpMyAdmink, you must specify your delimiters.

Go read this.

http://www.mysql.com/doc/en/LOAD_DATA.html

The null value was there because I assumed you would want to put a unique ID column that was auto_increment. If you don't want that, then leave it out, but it may make your data more difficult to manage depending on what you plan to do with it.

[rhock]

using phpmyadmin all I needed to do was place a ; at the end of each line, actually it could all be on the same line... fields seperated with the ;

Thanks you so much for taking the time to help...

I am far from through this process yet but I am much farther along today than I was before...

do you see any problem ( without the int field) for me ( the next thing I need help with) creating HTML query forms to sort and dislpay the data by any of the field names ?

thanks so much for getting me this far...

Richard...

[theeper]

Imagine that you happen to have two businesses named "Joe's Store", and you write a script to delete, edit and add businesses ... what will happen when you do a query to update Joe's phone number that looks like this ...

UPDATE bus_list set bus_phone = "555-555" WHERE bus_name = "Joe's Store";

.. now you have updated both businesses named Joe's Store. With an auto increment ID field, you can then have a unique identifier for rows of data. Then you can say something like ...

UPDATE bus_list set bus_phone = "555-555" WHERE bus_id= "12";

know what I mean ?