newdevs:code:import_csv_into_db
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
newdevs:code:import_csv_into_db [2023/02/27 13:16] – tlittle | newdevs:code:import_csv_into_db [2024/06/13 10:52] (current) – more resources sleary | ||
---|---|---|---|
Line 2: | Line 2: | ||
The Concerto database isn't very large, and sometimes you need to test things with more data. If you can pull and anonymize production-level data (but not too much to crash your test server), you can copy it into place with a CSV file. | The Concerto database isn't very large, and sometimes you need to test things with more data. If you can pull and anonymize production-level data (but not too much to crash your test server), you can copy it into place with a CSV file. | ||
+ | |||
+ | ===== Finessing the Data Prior to Import ===== | ||
+ | |||
+ | **Notes:** | ||
+ | * If the table you're wanting to insert into uses an org, you'll want to edit your CSV file first to use the org IDs from Concerto. | ||
+ | * Don't include the column for ID from your production data. | ||
+ | * If there are duplicate key constraints, | ||
+ | |||
+ | < | ||
+ | |||
+ | This assumes that Column A is the no-dupe column (name, in this case) and Column B is the org. So basically, count the number of times that the name in A2 appears with the same org that's in B2. Then if we fill that down the entire column, if you get anything above a 1 you have a dupe that you can filter for and remove. | ||
+ | |||
+ | **TIP:** If you're not sure if your table includes a uniqueness constraint, here's a grep of the Evergreen repo for the word " | ||
+ | ===== Importing the Data ===== | ||
1. Log into the database as the root user. | 1. Log into the database as the root user. | ||
Line 20: | Line 34: | ||
Make sure to include, at minimum, any required fields in your final table. This example is based on acq.provider, | Make sure to include, at minimum, any required fields in your final table. This example is based on acq.provider, | ||
- | 4. Now we're going to populate our starter table from our CSV file. Make sure your CSV file is on the server somewhere you know. | + | 4. Now we're going to populate our starter table from our CSV file. Make sure your CSV file is on the server somewhere you know. In this case, my file is in / |
+ | |||
+ | Note: If you do not have a header in your csv file, don't include the " | ||
< | < | ||
- | Then if you do a select * from tlittle.acqprolist you'll see it's now populated with the data from our CSV file. | + | Then if you do a **select * from tlittle.acqprolist** you'll see it's now populated with the data from our CSV file. |
5. To get that data into our Evergreen table, we need to copy it over. | 5. To get that data into our Evergreen table, we need to copy it over. | ||
Line 44: | Line 60: | ||
So the second half is the SELECT--select all these from tlittle.acqprolist--which we then INSERT with the first portion. | So the second half is the SELECT--select all these from tlittle.acqprolist--which we then INSERT with the first portion. | ||
- | If it's successful, you'll get the INSERT 0 [NUMBER OF ROWS] message. You can then do a select * from acq.provider | + | If it's successful, you'll get the INSERT 0 [NUMBER OF ROWS] message. You can then do a **select * from [YOUR TABLE]** |
+ | |||
+ | ====== More Resources ====== | ||
+ | |||
+ | * [[https:// |
newdevs/code/import_csv_into_db.1677521801.txt.gz · Last modified: 2023/02/27 13:16 by tlittle