I edited my php.ini file to allow for large files on my localhost.
I have a 30mb file with 150000 rows of data with 14 columns (nothing extreme, mainly names and addresses)
It’s been generating the table for over an hour and only has 42,000 rows generated. Is there something I can do to speed this up?
my host files have the:
in it.
it doesn’t matter if i use https://localhost or https://127.0.0.1, they both perform VERY slowly. Any advice?
Yes, don’t do that! Sorry, that was a joke. First, 150k of items is NOT very much these days. I have one
with about 2.5 million and it is very fast to access it.
The problem is in the browser if you are attempting to display a large number at a time. It really depends
on what you mean by “generating” a row. You mentioned generating the table for an hour. 30 megs is NOT
a very large file. I have used much larger ones. BUT, if you are creating a database from a text file, that is
most likely your problem. Since servers are extremely slow when accessing text files, if it is a text file, it
could be that issue. If so, you can speed it up by breaking down the file into smaller chunks and doing one
at a time. Servers have issues with large text files.
Well, there are a hundred other questions to ask, perhaps if you show us some code, we might be able to
help you further.
Oh, also, if it is an excel file, you can use a CSV library that might speed it up. More info please!
One more comment on text files. If you are scanning a line of text, there are built-in functions that work
much better than others. Some are very slow, some work quickly. It really matters which you use. For
example, if the data is broken into comma delimited versions such as a CSV file, you can just use the
EXPLODE() function and grab the entire line all at once. Much faster than parsing thru a line to figure out
the data in it.
I’m using xampp portable as my localhost on my windows 7 16gb system. I’m importing a .csv through phpmyadmin. My browser is Chrome. I’d be happy to show you any code I have, can you be more specific on what part of my code you’d like to see?
So, as I said my 30mb file has 150,00 rows with 14 columns. I’m having trouble understanding why it’s taking 2+ hours to import (generate rows) into my table so slowly. As of this message i’m at 74,517 of 150,000.
The problem is that I have still have more files to import, some with 40mb 250,000 rows.
So, are you importing them with PHPmyAdmin? Or actually running code to do this? If importing, you must
remember that you are running a code page that constantly checks items and validates things and has a lot
of behind-the-scenes code that runs during your processes. If you are using PHP to do this, I found this code
that took the writer of it about 20 minutes to parse thru a 120-meg file of data. Something like this might do
the job for you. (Untested, just copied the basics from a post.)
[php]
if(($handle = fopen(‘data.csv’, ‘r’)) !== false)
{
// get the first row, which contains the column-titles (if necessary)
$header = fgetcsv($handle);
// loop through the file line-by-line
while(($data = fgetcsv($handle)) !== false)
{
// resort/rewrite data and insert into DB here
// try to use conditions sparingly here, as those will cause slow-performance
// Here you would need to store the array $data into the fields in $header...
// Not sure if the next line is needed...
unset($data);
}
fclose($handle);
}
[/php]
As you see the PHP code is simple. In the middle section, you need to either know the order of the array
data inside of $data variable or you can use the $header array to point to the correct fields to store it in.
The less processing in the inside loop is better, so hard-coding the query is best…
Hope that helps!
aha! very cool… Thanks!
Well, Lejjae, I did some further research and found that you should be able to just do a BULK-INSERT if your
PHP Admin panel allows them. Here is a sample of one:
BULK INSERT [Company].[Transactions]
FROM “C:\somefolder\datafile.csv”
WITH
(
FIELDTERMINATOR = ‘|’,
ROWTERMINATOR = ‘\n’,
MAXERRORS = 0,
DATAFILETYPE = ‘widechar’,
KEEPIDENTITY
)
GO
It appears that some admin’s do not allow this command.
They also say on a lot of programming sites that you can just use a small PHP script to create a huge SQL
INSERT query from your CSV file. Then, you just run that QUERY and it does the insert at the server’s speed
which would be the fastest way to go. The above way was number two in speed from what I found so far.
Okay, I love a good programming puzzle… Are you using MySQL ??? If so, there appears to be a solution
that is extremely easy. You just need to use the MySQL coding as it has a function to handle this exact issue
already built into it… Here is the link to it’s page that explains it. If you read it a little and then look down
on the page to the examples, you will see some ways to do it with your file.
From what I read on other pages and this one, it appears to be one of the fastest ways to handle huge file
inputs. Might be the best way to go. Not sure! You basically ignore 1 line so it does not include the first of
the lines which is the CSV’s headers for the fields and then import the rest… Should work great for you.
yes I’m using XAMPP which has the Apache server and MySQL server built in
My CSV file doesn’t have \n at the end of the lines, it just breaks and begins a new line
Name Address Phone
Name Address Phone
Name Address Phone
Well, that is not true. If it “breaks” and moves to a new line, it is encoded with a line-break character.
You just need to know what it is. What are you viewing the file in? For standard CSV files, it is “\n” which
is the code for NEWLINE. This is basically a 0x0A or a decimal 10. But, if you view the file inside a text editor,
you will not see that code as it just moves to the next line for you. Hope that makes sense…
There are many different hidden codes used in various ways depending on what editor you are using or what
code you are using to access the text with. Here is a link to explain it in WikiPedia:
https://en.wikipedia.org/wiki/Newline
Doesn’t really mean much for this thread, but… Did you read the last post about the MySQL code function to
use which is very fast? Best way to go…
PM’d… and after rereading your post, I’m a dumbass…
Okay, for those following, Lejjae showed me a few lines of his live and private data. It is just standard
computer data. Therefore, it has a hidden line-break code inside it. Now, back to the code issues under
MySQL to pull all the data into a database. You just use this code from the link I posted which is the MySQL
site’s page about how to do this. Here is the code as far as I can see from what I read so far on that page:
[php]
LOAD DATA INFILE ‘your_data_filename.txt’ INTO TABLE your_table_name
FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘"’
LINES TERMINATED BY ‘\r\n’
IGNORE 1 LINES;
[/php]
Then, you just execute the query in your admin panel under SQL OR you can place it into code and run the
query from your PHP program. Either should work. This code skips the first line which is usually the line
that tells what fields the data goes into. If that is not in place remove the IGNORE option. It also does
expect the data enclosed by quotes. If not, remove that option. If you need to remove those two options,
the command would look like this:
[php]
LOAD DATA INFILE ‘your_data_file.txt’ INTO TABLE your_table_name
FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\r\n’;
[/php]
AND, lastly, if the CSV file was created by a program and not EXCEL or the PHP admin panel, the \r\n might
be something different… Try it and let us know… One thing is if there are duplicate entries, you might get
an error message and the process might stop. I guess normally, you would place the data into a temporary
table and then use PHP code to process the table checking for duplicates before committing the data as live.
Hope that helps!
Talk about shortcut of the century! That worked like a charm!!! Thanks ErnieAlex!!! 
;D ;D ;D
Glad I could help !
So, for others who read this, how fast did it import the entire 40megs of data? Just curious!
I will mark this one solved!
The 40mb was seconds. It took about 2-3 minutes to do 1 million lines from my full 125mb file
SWEET ! Great and very fast! I will remember that one as others will, too. Thanks for the timing notes…
[member=43746]ErnieAlex[/member] , I added your informative post to the code snippets section. It looks like it could be very useful, as long as column mapping is not a requirement it should fill a need quite well.
[member=72272]astonecipher[/member] ,
On this thought, yes, it is very helpful. Especially if you are getting large public databases and need to
load them into your own database. It is so very fast! Really a nice find! Glad I located it…
Speaking of that, I will have to visit the code snippets section soon, have not been there in months and months and months… Where DID that time go to???