Importing flat files (e.g.) Excel directly into SQL Database

Click For Summary

Discussion Overview

The discussion revolves around methods for importing flat files, such as Excel, directly into an SQL database. Participants explore various approaches, tools, and programming options for handling this task, with a focus on efficiency and the suitability of different methods based on the volume of data.

Discussion Character

  • Exploratory
  • Technical explanation
  • Debate/contested

Main Points Raised

  • One participant inquires about alternatives to Microsoft's import/export Wizard for importing legacy files into an SQL database.
  • Another participant suggests using Microsoft's wizard for a small number of files but recommends writing a custom program for larger datasets.
  • A participant mentions that BULK INSERT can import files wholesale, noting their experience with CSV files but not confirming other supported formats.
  • There is a question regarding whether the database schema needs to match the flat files for the import process to succeed.
  • One participant proposes writing a Java application for the import, estimating it could be a short program depending on the complexity of the data differences.
  • Another participant suggests that Groovy could also be used for this purpose, highlighting its advantages over Java in terms of flexibility and ease of use.
  • Further discussion touches on the merits of Groovy as a scripting language and its potential for prototyping developer tools.

Areas of Agreement / Disagreement

Participants express differing opinions on the best approach to importing files, with some advocating for existing tools like Microsoft's wizard and others favoring custom programming solutions. The discussion remains unresolved regarding the necessity of schema similarity for successful imports.

Contextual Notes

There are assumptions about the volume of files and the complexity of differences between file formats and database schemas that are not fully explored. The discussion also reflects varying levels of familiarity with programming languages and tools.

WWGD
Science Advisor
Homework Helper
Messages
7,806
Reaction score
13,120
Hi all,
I have a bunch of legacy files I am trying to import directly into an .sql database. Is there a way of doing this other than by using Microsoft's import/export Wizard?
 
Technology news on Phys.org
What do you mean by a bunch? If it's a dozen or so, then use Microsoft's wizard. If it's a lot more than that, I would write a custom program.
 
  • Like
Likes   Reactions: WWGD
If memory serves BULK INSERT imports files wholesale. No idea what file formats it supports, except I used it for CSV.
 
  • Like
Likes   Reactions: WWGD
Thank you all, does the database schema need to be similar to the flat files for the import to go through?
 
Borg said:
What do you mean by a bunch? If it's a dozen or so, then use Microsoft's wizard. If it's a lot more than that, I would write a custom program.
Thanks, what type of program would that be?
 
WWGD said:
Thanks, what type of program would that be?
Since I'm a Java programmer, it would be a Java application. Probably no more than a hundred lines of code depending on how complex the differences are between the files and the database.
 
Borg said:
Since I'm a Java programmer, it would be a Java application. Probably no more than a hundred lines of code depending on how complex the differences are between the files and the database.

It could be Groovy too. Its a lot cooler than Java and all its formalism. Just sayin...
 
jedishrfu said:
It could be Groovy too. Its a lot cooler than Java and all its formalism. Just sayin...
Like I don't have enough problems at work with the last cool thing that someone decided to use. :cool:
 
  • #10
No really, groovy is something you'd like its java as a scripting language. It has some things in it that you wished java had but doesn't. I've used it to protoype developer tools that used our project libraries but were easily reconfigurable.

But I digress... so as not to derail the thread in other paths...
 
  • #11
jedishrfu said:
No really, groovy is something you'd like its java as a scripting language. It has some things in it that you wished java had but doesn't. I've used it to protoype developer tools that used our project libraries but were easily reconfigurable.

But I digress... so as not to derail the thread in other paths...
Sounds like it might make a good Insight article. :oldwink:
 
  • Like
Likes   Reactions: jedishrfu

Similar threads

How can I access and use free databases from Murach's book?
  • · Replies 8 ·
Replies
8
Views
3K
How to disable access to old data with newest version of software?
  • · Replies 8 ·
Replies
8
Views
1K
Simpler alternatives to SQL-like databases
  • · Replies 50 ·
2
Replies
50
Views
9K
Python Query runs fine in MySQL, but returns empty list in Python
  • · Replies 7 ·
Replies
7
Views
5K
JavaScript How do I save JSON files to off-line database?
  • · Replies 8 ·
Replies
8
Views
2K
Is MySQL or PostgreSQL more ACID compliant for database projects?
  • · Replies 1 ·
Replies
1
Views
2K
Python Simple Python Database Tutorial & Example
  • · Replies 6 ·
Replies
6
Views
3K
Python Most efficient way to randomly choose a word from a file with a list of words
  • · Replies 35 ·
2
Replies
35
Views
8K
Can't find my Localhost Database Engine for SQL Server
  • · Replies 3 ·
Replies
3
Views
3K
Speeding Up SQL Query: Grouping & Joining
  • · Replies 51 ·
2
Replies
51
Views
6K