Dear Team, Every month, I am downloading the billing data from SAP in Excel and importing it into the MySQL database (not all the columns from Excel). For one month, the downloaded file size was 17 MB, and it has over 31000 rows in Excel. So the file is not getting imported into the database. To reduce the number of rows, I planned to group the data in the columns Material (11) and Sales Office (45), like we are using in the MySQL query (group by Material). So that the rows will be reduced while inserting into the database. I am attaching my dump, PHP, and Excel at the below link. https://drive.google.com/file/d/1fTS-mkTKe93ZtxMmn8afkK6L2lUSn7PH/view?usp=sharing Please suggest how to do this.z

I'd do it like this... ################################################################################ # CREATE TEMPORARY STAGING TABLE # ################################################################################ $pdo->exec("CREATE TEMPORARY TABLE tempdata ( id int not null auto_increment primary key, sales_doc_type varchar(10), billing_date date, material varchar(10), gross_amount int, sales_office varchar(10), plant varchar(10) ) "); ################################################################################ # READ CSV, # # EXTRACT REQUIRED DATA, # # STORE IN ARRAY FOR INSERTION TO TEMP STAGING TABLE # ################################################################################ $fp = fopen('test1.csv', 'r'); $req_cols = [ 0 => 'sales_doc_type', 2 => 'billing_date', 11 => 'material', 25 => 'gross_amount', 45 => 'sales_office', 47 => 'plant' ]; $import_data = []; while ($allrow = fgetcsv($fp)) { $row = array_intersect_key($allrow, $req_cols); $randdays = rand(5,30); $row[2] = date('Y-m-d', strtotime($row[2])); $row[25] = str_replace(',', '', $row[25]); $import_data[] = vsprintf("('%s','%s','%s','%s','%s','%s')", $row); } fclose($fp); ################################################################################ # ADD RECS FROM ARRAY TO TEMP TABLE - 2000 AT A TIME # ################################################################################ $chunks = array_chunk($import_data, 2000); foreach ($chunks as $ch) { $sql = "INSERT INTO tempdata (sales_doc_type, billing_date, material, gross_amount, sales_office, plant) VALUES " . join(',', $ch); $pdo->exec($sql); } ###################################################################################################################### # # # Now we have the data from the csv stored in a temporary staging table # # which makes it easy to manipulate the data with queries to update our # # database tables with efficient queries rather than running multiple # # queries inside loops. # # # # NOTE - as I never received a reply to my question about the origin of the # # data used toupdate the members table I have had to omit that part # # of the processing. The UPDATE query will be similar to the INSERT # # below except it will update where there is a matching record # # # ###################################################################################################################### ################################################################################ # INSERT TEMPDATA REC INTO billing WHERE NO MATCHING billing_date IN members # ################################################################################ $pdo->exec("INSERT INTO billing (sales_doc_type, billing_date, gross_amount, sales_office, plant, material) SELECT t.sales_doc_type , t.billing_date , t.gross_amount , t.sales_office , t.plant , t.material FROM tempdata t LEFT JOIN members m ON DATE(t.billing_date) = m.billing_date WHERE m.billing_date IS NULL ");

Jump to content

Existing user? Sign In
Sign In

Remember me Not recommended on shared computers

Forgot your password?
Sign Up

Browse
- Forums
- Staff
- Leaderboard
- Guidelines
- Terms of Service
- More
Activity
- All Activity
- Search
- More
Join our Discord!
More
- More

PHP Coding Help

Import data from excel to databse

By Senthilkumar
November 3, 2023 in PHP Coding Help

Share

https://forums.phpfreaks.com/topic/317412-import-data-from-excel-to-databse/

Followers 4

Go to solution Solved by Barand, November 4, 2023

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

× Pasted as rich text. Restore formatting

Only 75 emoji are allowed.

× Your link has been automatically embedded. Display as a link instead

× Your previous content has been restored. Clear editor

× You cannot paste images directly. Upload or insert images from URL.

Insert image from URL

×

Share

https://forums.phpfreaks.com/topic/317412-import-data-from-excel-to-databse/

Followers 4

Go to topic listing

×

Browse
- Back
- Forums
- Staff
- Leaderboard
- Guidelines
- Terms of Service
Activity
- Back
- All Activity
- Search
Join our Discord!

×

Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.

Sign In

Import data from excel to databse

Recommended Posts

Senthilkumar

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information