1

I have loaded data from a .csv file and am inserting it into the database using the LoadData method.

The below query is working for me:

LOAD DATA LOCAL INFILE '"$FILE1"' 
INTO TABLE inventory FIELDS 
TERMINATED BY ','
ENCLOSED BY '\"' 
LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES (@ignore ,StockType ,StockNo ,ServiceVIN ,YearMake ,CMake ,SModel ,BodyType ,Color ,Interiorcolor ,@ignore ,EngineSize ,@ignore ,Transmission ,Miles ,List ,@ignore ,@ignore ,Drive ,@ignore ,Details ,@ignore ,ModelType ,@ignore ,@ignore ,@ignore ,Trim) 
SET CreatedAt = NOW();"

But I need to insert the data with the ascending order in the List Column. Is this possible?

I have tried orderby in this query But it is not working.

Working query:

LOAD DATA LOCAL INFILE '"$FILE1"' 
INTO TABLE inventory FIELDS TERMINATED BY ',' 
ENCLOSED BY '\"' 
LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES (@ignore ,StockType ,StockNo ,ServiceVIN ,YearMake ,CMake ,SModel ,BodyType ,Color ,Interiorcolor ,@ignore ,EngineSize ,@ignore ,Transmission ,Miles ,List ,@ignore ,@ignore ,Drive ,@ignore ,Details ,@ignore ,ModelType ,@ignore ,@ignore ,@ignore ,Trim) 
SET CreatedAt = NOW();"
Philipp
  • 745
  • 2
  • 7
  • 20
Varun P V
  • 1,092
  • 1
  • 12
  • 30

1 Answers1

0

If data volume is not too big, you can use the result of a SELECTstatement as the data source for an INSERT statement.

Step by step:

LOAD DATA LOCAL INFILE '"$FILE1"' 
INTO TABLE inventory FIELDS TERMINATED BY ',' 
ENCLOSED BY '\"' 
LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES (@ignore ,StockType ,StockNo ,ServiceVIN ,YearMake ,CMake ,SModel ,BodyType ,Color ,Interiorcolor ,@ignore ,EngineSize ,@ignore ,Transmission ,Miles ,List ,@ignore ,@ignore ,Drive ,@ignore ,Details ,@ignore ,ModelType ,@ignore ,@ignore ,@ignore ,Trim) 
SET CreatedAt = NOW();"

INSERT INTO inventory(column_list)
SELECT 
   select_list 
FROM inventory_tmp
ORDER BY column1

You can read more details up here: http://www.mysqltutorial.org/mysql-insert-into-select/

Option 2: You can also sort the text file before importing to Database. Follow this guide: https://stackoverflow.com/a/2100384/12263045

NOhs
  • 2,780
  • 3
  • 25
  • 59
madaki
  • 1
  • 1