View Full Version : How do I change a regular text file into a database??
Hi,
I have a text file formatted like this:
884 225 LIGHTED ROSE BASKET 1 EA 29.95
1402 168 3 PC BRASS BUTTERFLY WALL SET 1 SET 18.95
Is it possible to input that into a MySQL database without doing it by hand? What is the PHP used to do this?
Thanks!
Luke Spragg
Dylan Leblanc
10-09-2002, 11:35 AM
You need to create a table with fields for each kind of data you have there.
Then to insert the data, one way would be to write a PHP script to read a line from your file, pick out the different pieces of data, and insert them in to the database with the INSERT command.
Alekz
10-09-2002, 11:37 AM
HI,
do You want to inser in the database the text or the file itself?
Alex
I want to insert the text into the database without doing it one by one.
Dylan Leblanc
10-09-2002, 12:10 PM
Are they separated something like this:
884 -- 225 -- LIGHTED ROSE BASKET -- 1 EA -- 29.95
1402 -- 168 -- 3 PC BRASS BUTTERFLY WALL SET -- 1 SET -- 18.95
?
Alekz
10-09-2002, 12:46 PM
I think You'll have to read the file line by line, but then You can concatenate everything in a string variable and insert it into a LONGTEXT field for example - it depends on how large the initial file is...
Alex
Dylan Leblanc
10-09-2002, 12:53 PM
I think X-MC wants to separate his data into rows and columns corresponding to the rows and data bits in the file.
Alekz
10-09-2002, 01:30 PM
Hi Dylan,
Where did You read this?
wants to separate his data into rows and columns corresponding to the rows and data bits in the file
It seems logical to do it like that, You are right, but it was not requested, or probably i missed some posts?
Alex
Dylan Leblanc
10-09-2002, 01:34 PM
It is common thing to do, changing a file based system into a database one. Usually files are delimited with commas or some other character.
I could be completely wrong though, he might want the entire file in the databse as one row.
I want to input the file I have into a mysql database with seperate columns and rows for each item.
Dylan Leblanc
10-09-2002, 09:08 PM
Yes, are they separated like this?
884 -- 225 -- LIGHTED ROSE BASKET -- 1 EA -- 29.95
1402 -- 168 -- 3 PC BRASS BUTTERFLY WALL SET -- 1 SET -- 18.95
?
No, there are just spaces like you saw in my example before...the spaces in the actual file are a lot larger though.
884 225 LIGHTED ROSE BASKET 1 EA 29.95
1402 168 3 PC BRASS BUTTERFLY WALL SET 1 SET 18.95
Dylan Leblanc
10-09-2002, 09:24 PM
ok, so your file is delimited with tabs? or multiple spaces? What separates the fields?
Dylan Leblanc
10-09-2002, 09:28 PM
How many spaces?
You could use the explode() function in PHP to turn one line from the file into an array of values, then implode() to format them for the database query.
Post a sample of the file contents using the [ code ] tag in the forum here.
The thing is, I'm new and don't know much about php or mysql. Do you think if I sent the file to you then you could try it for me?
Dylan Leblanc
10-09-2002, 09:37 PM
sure. nalyd@skyscraperpage.com
You will need a design for the database. Do you know how to do that? SQL and all?
I was doing it all one by one with Microsoft Access. I would just need these columns:
ProductID
ProductNumber
ProductName
ProductDescription
ProductQuantity
ProductPrice
or something similar to those.
Dylan Leblanc
10-09-2002, 09:48 PM
ok, you *might* want to go with something like this:
CREATE TABLE `Product` (
`productID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`productNumber` INT,
`name` VARCHAR(100),
`description` TEXT,
`quantity` VARCHAR(20),
`price` DECIMAL(4,2)
);
If you have access though, and you are not to familar with databases, you might want to stick with it. Access has a lot of built-in things that make it easier for novices to run a database. With MySQL you will need to know more about how database are designed and work.
I prefer MySQL a heck of a lot more.
I know Access does have some wizards that can import your data files for you. You just give Access the file, tell it what separates the fields, what the field names are, and bang you have your table.
Here is another example:
884 225 LIGHTED ROSE BASKET 1 EA 29.95
1402 168 3 PC BRASS BUTTERFLY WALL SET 1 SET 18.95
1405 166 MUS. OIL DERRICK SCULP. 1 EA 21.95
Here is the comma-delimited one:
884,225,LIGHTED ROSE BASKET,1 EA,29.95
1402,168,3 PC BRASS BUTTERFLY WALL SET,1 SET,18.95
1405,166,MUS. OIL DERRICK SCULP.,1 EA,21.95
Dylan Leblanc
10-09-2002, 10:05 PM
ok thanks I got your email.
In the file, what are the names of each bit of data?
Something like this?
884 = product id
225 = product number
LIGHTED ROSE BASKET = product name
1 EA = product quantity
29.95 = product price
?
I don't want the second column of numbers which you thought was the product number. You got everything else right though. I also want a catagory ID column for the database.
Dylan Leblanc
10-09-2002, 10:14 PM
Ok.
I noticed that some of the product ID's have an x in them:
3202, 232, INDOOR/OUTDOOR THERMOMETER, 1 DZ, 2.99
X3202, 232, THERMOMETER IN/OUT, 1 EA, 2.95
Do you want to discard those ones?
Also, you are dropping the product number, would it be ok to reorder the product ID's? This would change all of the product ID's to sequential ordering.
The X is like a different model of the number after it. Those other numbers are not the product numbers because they have alot that are repeated on different products. They might be catagory ID numbers.
Dylan Leblanc
10-09-2002, 10:23 PM
What were you using to create these files in the first place?
I didn't make those files. My supplier made them for my store.
Do you have any instant messengers Dylan Leblanc?
Alekz
10-10-2002, 07:34 AM
Or preg_replace to recursively replace all spaces with some uniform delimiter, for example ;
Ant then explode() the resulting string...
Alex
vBulletin® v3.8.2, Copyright ©2000-2012, Jelsoft Enterprises Ltd.