If you’re working with Oracle SQL and need to load CSV data into one of your tables, using Oracle SQL Developer makes this process a breeze. Continue reading to see a simple example and learn how…
The Newsletter for PHP and MySQL Developers
Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.
Self-Promotion:
If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!
I have written quite a few blog posts about CSV’s in an SQL context (some in a Python environment as well). Since I’ve started using and learning more about the Oracle Database, I thought to write a blog post on loading CSV file data using SQL Developer. For those unfamiliar, SQL Developer is an IDE for working with Oracle SQL Databases.
CSV Source Data to Import
I have this data stored in a CSV file I want to load into an existing staging table:
The CSV header row shows 6 fields which are the columns in the staging table, STG_WALKING_STATS:
- day_walked
- cal_burned
- miles_walked
- duration
- mph
- shoe_id
An Aside: I am currently having fits storing the duration column data in an Oracle-appropriate datatype to represent an HH:MI:SS format, as in 1 hour, 10 min, 28 seconds. Any help, tips, advice, or resources from the community would be much appreciated. I wrote about this particular columns’ data in the post, SUBSTR() Character Function – Oracle SQL Example, you can read for more information about it.
Load CSV data file using SQL Developer
In 6 steps, you can easily load CSV data into an existing table in Oracle SQL using SQL Developer.
Step 1:
Right-click on the target table you want to load CSV data into and click Import Data
Step 2:
Browse to and choose the CSV file you want to upload. I mostly kept the defaults here. However, notice you can skip rows before or after the header row via the various settings in the interface:
Step 3:
Again, I lean on the defaults. In particular, the Import Method drop-down choices, which I leave set to Insert.
Consider making a small donation on my behalf as I continue to provide valuable content here on my blog. Thank you!
Step 4:
In this step you map out the field headers you want to import from, optionally ordering them in a certain order. I keep them in their original order as they are in the CVS file. Clicking the Next button continues with the import steps:
Step 5:
Now choose the target columns in the existing table CSV data will be loaded into:
Step 6:
Clicking the Finish button initiates the overall import, which is a culmination of the steps shown in the wizard:
Once the import is completed, a success message is displayed:
Discover premium articles, in-depth guides, instructional videos, and much more by joining the “MySQL Learning Tier” membership. You have access to exclusive content unpublished anywhere else with this membership. With new content being added regularly, continue learning MySQL at any level.
Verify imported CSV Data
We can execute the below query and filter for any rows with a day_walked column value greater than 14-APR-21, retrieving all of the recently imported rows:
WHERE DAY_WALKED > '14-APR-21';
As you have seen in the various screenshots throughout this post, there are several settings available for each step of the import process. Where I used the defaults, I’m sure other options work better in other circumstances so be sure and use those that best benefit your data import needs.
Like what you have read? See anything incorrect? Please comment below and thank you for reading!!!
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the βClick To Subscribe!β button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, is performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.
More ways I can help
- Need hosting for your next web application or WordPress site? I highly recommend Hostinger and use them to host my niche bass fishing site. The service is second to none.
- π5 Truths I’ve Come To Realize As a Self-taught Developer
- Desktop and mobile wallpapers, digital downloads, photography services, Shopify and WooCommerce customizations, and content writing – all in one E-commerce Shop. Find your next digital purchase today!
- Take your Laravel applications next level with Battle Ready Laravel by Ash Allen. Learn how to improve the performance, maintainability, and security of your Laravel projects in this e-book.
Disclosure: Some of this blog post’s services and product links are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission.
The Newsletter for PHP and MySQL Developers
Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.
Use the INTERVAL data type for your duration column.
Hi Jeff. Thank you for the information. I will look into the INTERVAL type. Thank you for your comment π