0

I want to export the selected data from specific columns to the new columns in CSV file for my Magento website.

the current website is using different e-commerce system and I want to export the products data in (Magento compatible) CSV file so I can easily import all the data to Magento

for example:

Old tables / columns:

[old_table_1]

[old_col_pid] (primary), [old_col_1], pold_col_2]
1, abc, xyz

[old_table_2]

[old_col_pid] (primary), [old_col_fid] (foreign), [old_col_3], [old_col_4]
1, 1, 123, 456

New columns in CSV (for Magento):

new_col_1, new_col_2, new_col_3, new_col_4
abc, xyz, 123, 456

Is there any way if I can write a query to match the old columns name to new columns name first and then export the data to the CSV.

orbnexus
  • 737
  • 1
  • 9
  • 38
  • 1
    What is the relationship between the two tables? For the trivial example you gave, a cross join would work, but it would fail for more than one row. – Tim Biegeleisen Jan 02 '19 at 23:57
  • @TimBiegeleisen, I am sorry i didnt add the relationship table. there is a primary / foreign IDs. i have updated my comment – orbnexus Jan 02 '19 at 23:58
  • 2
    If you are interested in generating a csv file with headers, then the question has akready been answered here on SO: https://stackoverflow.com/questions/262924/how-to-export-dump-a-mysql-table-into-a-text-file-including-the-field-names-a (you can set the field names in the header). However, as @TimBiegeleisen has already wrote: I do not get how you want to produce the output from the two tables and whether this is part of the question at all. – Shadow Jan 03 '19 at 00:01
  • @Shadow, the answer you put in the comment, is just exporting the data with old columns. what i am trying to do is to allocate data from existing columns to the new columns – orbnexus Jan 03 '19 at 00:04
  • In the topuc I linked you can set the the column names in the export file to whatever you want. In the first answer use aliases, in the second answer just use a different string for field names – Shadow Jan 03 '19 at 00:13
  • Also added another one that just provides the outputted field names as plain strings. – Shadow Jan 03 '19 at 00:19
  • @Shadow it is not duplicated question as i need to export data to new columns can you please unmark my question to not duplicate? – orbnexus Jan 03 '19 at 00:20
  • It is a duplicate, since you can specify your column aliases to be used in the export file in any of the methods used in all the answers for the duplicate questions. – Shadow Jan 03 '19 at 00:22
  • 1
    Try This: select 'column1','column2','column3','column4' union all select t1.old_col_1 as new_col_1, t1.old_col_2 as new_col_2, t2.old_col_3 as new_col_3, t2.old_col_4 as new_col_4 INTO OUTFILE 'E:/result.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY "\n" from old_table_1 t1 inner join old_table_2 t2 on t1.old_col_pid = t2.old_col_fid; – Tarreq Jan 03 '19 at 00:26
  • @Tarreq, Thanks for your answer but where to write this? in a PHP file? – orbnexus Jan 03 '19 at 17:34
  • 1
    @orbnexus you can run this directly in any MySql client, like phpMyAdmin, or MySql workbench or anything, its just a MySql statements. Try it and tell me – Tarreq Jan 03 '19 at 17:43
  • @Tarreq It is throwing this error when i put this line "NTO OUTFILE 'E:/result.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY "\n"" the error is: #1045 - Access denied for user 'username'@'%' (using password: YES) – orbnexus Jan 03 '19 at 19:04
  • try another path , c:/file.csv for example, or try giving required permission to that user, its not an error related to the query – Tarreq Jan 03 '19 at 21:44

0 Answers0