CSV File & Normalization


#1

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: ..


#2

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


#3

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?


#4

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


#5

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.

colors


red;blue;black;green

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.

prodtcu_colors.csv

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.


#6

Thank you so much for this kind of Idea ..
:slight_smile:


#8

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


#9

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