CSV File & Normalization


Hi Guys ..
I encounter some process that I didn't where should I start .
My problem is :
I have tables .. this tables is in a normalize form
so I just thinking how can I import a CSV file from this tables ??
If I try to perform many queries.. I guess this can create a performance issue :frowning: ..


Either use JOIN in your query to denornalize the data and write the denornalized data to the CSV, or create one CSV per table.


I didn't get it ..
First I write a query to and join those values ??
then create new ?
then insert it to the normalized table?


No you write a query that JOINs multiple tables, and then you write the result to a file.


I would create a temporary table that represents the exact csv file structure. I would then populate that table through x number of queries as needed. Once the table had all the data export/write it to a csv file which would be trivial once all the data is in the temp table. In terms of multiple values these are three approaches I have taken in the past.

1.) For simple values use a delimiter and stuff everything in a single column in the csv file.



2.) Allocate a number of columns that will never be exceeded.

color_1 | color_2 | color_3 | color_4

red . | blue . | black . | green

3.) For complex relational data using a separate csv might be best.


product_id | color

123 . | . red
123 . | blue
123 . | black
123 . | green

When I'm asked to generate a csv file like this I always tend to question whether something else would be a better solution. It all depends on the circumstance. Better approaches can include a service layer (sharing data with vendors) or perhaps even a json file instead of csv. Everyone seems to always default to a csv file when there might be other solutions are that are more appropriate.


Thank you so much for this kind of Idea ..


MySQL can also export CSV directly (SELECT ... INTO OUTFILE ...): https://dev.mysql.com/doc/refman/8.0/en/select-into.html


This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.