Home>

Thanks for your attention.
In the title, I want to upload a CSV with over 200 items from local and save it in MySQL.

Although there are multiple

csv formats, only one table can be stored, so the CSV items and table columns do not match.

Easy insert statement
INSERT INTO TableName ('0&apos ;,''.......
However, since the number of columns including the amount of data (number of records) is as large as 200 or more, what kind of mechanism is efficient? I asked if you could tell me the flow.

The language is PHP (7.1), created with mysqli.

I'm sorry for your busy schedule, but I would appreciate it if you could give me more advice.
Thanks for your consideration.

■ Addendum ----------------------------------------------------------- -----------

We heard that CSV data with different formats should be stored in separate tables.
I want to avoid wrong thoughts, but what kind of configuration should I do specifically? I can't get an image.
The following is the current configuration and the reason for it.

The data I want to handle is real estate information,

・ For sale properties (on sale)
・ Sale property (sales contract completed)
・ Rental property (under recruitment)
・ Rental property (contracted)

It is divided into four categories such as

, and CSVs in local are sorted by this category.

Because it is real estate information, if it is a property for sale, the "sale price" is the first, if it is a condominium "exclusive area", and if it is a detached house, "land area" or "building area" etc. Yes,

There are items for each category and property category, such as "sale price" for restricted property information and "monthly rent" and "common service" for rented properties. The contents of CSV are as follows.

・ For sale properties (on sale)
"Property number","Data classification","Property classification","Sales price","Expiration date"
"10010111","01","03","49800000","2018-8-30"
"10010121","01","02","85600000","2018-8-30"

-Property for sale (contract sold)
"Property number","Data classification","Property classification","Sale price","Sale date"
"10010132","02","02","69800000","2018-8-30"
"10010139","02","04","28600000","2018-8-30"

・ Rental property (under recruitment)
"Property Number","Data Classification","Property Classification","Monthly Rent","Expiration Date"
"10010212","03","03","98000","2018-8-30"
"10010245","03","02","48000","2018-8-30"

・ Rental property (contracted)
"Property number","Data classification","Property classification","Sales price","Sale date"
"10010111","04","02","128000","2018-8-30"
"10010121","04","02","88000","2018-8-30"

These CSVs (4 types) are stored in a database on the web, but since it is necessary to search in a single operation, we decided to save the data in one table as "Property Information". That is the background.

From the above, the table includes columns unique to each category and property category, such as "Sales Price", "Contract Price", "Monthly Rent", "Land Area", "Building Area", and "Exclusive Area". When uploading a CSV file, the image shows the flow of saving while assigning to the appropriate column (since CSV is divided into categories).

  • Answer # 1

      Although there are multiple

    csv formats, only one table can be stored, so the CSV items and table columns do not match.

    At this point, MySQL functionality has beenmostly discarded. If you mix multiple types of data, it's just a place for data.

    And when thinking about how to put data, it ’s more important tohow to use it. If you do n’t know how you want to use it after "put data into the database",nobody can determine the best structure.

  • Answer # 2

    If you want to make a useful system, understand the normalization of the database and design a table for each data, and CSV also separates the data for each table.

  • Answer # 3

    It's still a story that you want to consolidate different forms of interfaces into one form.
    Since the database is good at handling normalized ones, I think that it is best to format the file in a language that can be handled at high speed and then import it into the DB.
    Checks may be poured into a temporary table and then performed.

    Added

    Please refer to the following as a language that can process files at high speed.
    I compared text filters in 8 languages ​​
    If there is not much difference in speed, it is better to select a highly maintainable language that can be processed with a simple description.

  • Answer # 4

    At least data of different types must be put in another table
    If it's not good, just keep the data
    Pour all into one column with text type data