Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
CSV to SQL (Read 4153 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
14.10.08 at 20:47:08
Converting a CSV file to SQL insert statements is done to import new data into a database. In a CSV file all fields are separated by a comma (Comma Separated Values) but the same procedures to handle these files applies to any flat file format. Converting the CSV file to SQL is not the end goal of the exercise. Inserting the data into the database is. In this topic I will explain how to achieve the CSV to SQL conversions or data loading into the database.  
Load into Excel and use SQL*XL
Excel is excellent in dealing with CSV files. You can just use File-Open in Excel to read the file in. If Excel thinks the file is a flat file it will load the file into column A. If your text file is read in like that select column A and use the Data-Text to Columns function to split up the fields in column A. Excel has different ways it can assist.
Once the data is in Excel SQL*XL can be used to insert the data into the database. No programming or SQL required! Simply select the data and use the multiple rows insert function. SQL*XL can create a new database table if required to store the data.
If SQL insert statements are required, SQL*XL can help too. An Excel worksheet function SQL*XL introduces called SQLCreateInsertStatement can be used to make the insert scripts.  
You can also use an ODBC connection to your CSV file or text file. The SQL*XL can be used to query the text file. Once the data is in Excel the multiple rows insert function or the insert statement creation as described above can be used again.
Why not give SQL*XL a try today.
SQL*XL: SQL Excel software

Download SQL*XL now!

Use a database specific data loader
Databases usually come with a data load utility. If you use Oracle you can e.g. use SQL*LOader, for SQL Server there is the import wizard or DTS. If you use another database type, see whether there is a data load utility available. Sometimes these are run on the server only or there is a script file to build before you can run the import.
Use external tables or a linked server
Oracle has the ability to define external tables. Similarly in SQL Server you can define a linked server. In both cases you need to add some configuration to your database so the text file is seen as an external table that can be accessed through SQL. Actually this technique is similar to what is described above with the ODBC coupling using SQL*XL. This time however it is a server based approach. SQL*XL will connect to the file on the client using ODBC and an external table or linked server will be the server centered alternative.
Back to top
« Last Edit: 08.01.09 at 09:47:37 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1