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

In summary, the conversation discusses different options for importing legacy files into a SQL database. Suggestions include using Microsoft's import/export wizard for a smaller number of files, or writing a custom program for a larger number. The use of BULK INSERT for CSV files is also mentioned. The group also discusses the benefits of using Groovy as a scripting language for this task.
  • #1
WWGD
Science Advisor
Gold Member
7,019
10,579
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
  • #2
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 WWGD
  • #3
If memory serves BULK INSERT imports files wholesale. No idea what file formats it supports, except I used it for CSV.
 
  • Like
Likes WWGD
  • #5
Thank you all, does the database schema need to be similar to the flat files for the import to go through?
 
  • #6
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?
 
  • #7
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.
 
  • #8
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...
 
  • #9
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 jedishrfu

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

What is the process for importing Excel files into a SQL database?

The process for importing Excel files into a SQL database involves first saving the Excel file as a CSV (comma-separated values) file. Then, using a SQL management tool, the CSV file can be imported into a table in the database. The tool will prompt for the file location and mapping of columns to corresponding database fields.

Can any type of Excel file be imported into a SQL database?

No, only Excel files saved as CSV files can be directly imported into a SQL database. Other Excel file formats such as .xlsx or .xls are not compatible with SQL databases and will need to be converted to CSV before importing.

What are the benefits of importing flat files directly into a SQL database?

Importing flat files directly into a SQL database allows for easy and efficient data management and analysis. It also eliminates the need for manual data entry, reducing the risk of errors and saving time.

Is there a limit to the size of the flat file that can be imported into a SQL database?

Yes, there is a limit to the size of the flat file that can be imported into a SQL database. This limit varies depending on the specific database and its settings, but it is typically several gigabytes. If the file exceeds this limit, it may need to be split into smaller files or imported using a different method.

Are there any potential issues or challenges when importing flat files into a SQL database?

Yes, there can be potential issues or challenges when importing flat files into a SQL database. These may include formatting or compatibility issues, mapping errors, and file size limitations. It is important to carefully review and prepare the data before importing and to troubleshoot any issues that may arise during the process.

Similar threads

Simpler alternatives to SQL-like databases
    • Programming and Computer Science
2
Replies
50
Views
4K
Query runs fine in MySQL, but returns empty list in Python
    • Programming and Computer Science
Replies
7
Views
492
How to disable access to old data with newest version of software?
    • Programming and Computer Science
Replies
8
Views
708
How can I access and use free databases from Murach's book?
    • Programming and Computer Science
Replies
8
Views
2K
Is MySQL or PostgreSQL more ACID compliant for database projects?
    • Programming and Computer Science
Replies
1
Views
1K
Python Most efficient way to randomly choose a word from a file with a list of words
    • Programming and Computer Science
Replies
21
Views
623
Speeding Up SQL Query: Grouping & Joining
    • Programming and Computer Science
2
Replies
51
Views
4K
What does it mean that MySQL is a server application?
    • Programming and Computer Science
Replies
16
Views
2K
JavaScript How do I save JSON files to off-line database?
    • Programming and Computer Science
Replies
8
Views
2K
Simple Python Database Tutorial & Example
    • Programming and Computer Science
Replies
6
Views
2K

Hot Threads

Recent Insights

Back
Top