Create a SQL database from csv files

Discussion in 'Programming' started by lordoftrades, Aug 21, 2012.

  1. Hello

    I have worked a lot on VBA in Excel, and would like to test how the result would be if I used SQL instead of hundreds of .csv files.

    But I have no clue on how to create a SQL database from all my .csv files. I have searched up and down without finding any answers that is relevant.

    Does anyone have suggestions on how to create a database in SQL from all the .csv files. I guess they have to be separated into EOD and Intraday?

    Kind regards
  2. 2rosy


  3. Worx


    Agree with 2rosy.
    If you are using Microsoft SQL, it has utility for creating database from Excel file. Sheets are converted individually. Subsequently you can convert Microsoft SQL to other database format. Ideally you'll merge similar CSV files to minimize number of databases.
  4. I think the SQL server version of bulk import is called bulkdata. It comes with the program and runs on the command prompt. It's pretty fast too.

    dhRichClient: Excel - SQLite library

    Checkout their star schema sample.
  6. If using Microsoft SQL Express 2012 (free), use Import/Export Data (xx-bit) tool to bring csv straight into your database.

    and yes, you will have seperate tables for intraday and EOD. Then you can use SQL queries through VBA and/or outright SQL queries to do what you want with the data...
  7. Thank you very much guys!! That cleared out a lot.

    I will try to use the import/export on my SQL Express.

    BTW: Is it true that the full version of SQL costs thousands of dollars?? Does the express version work when I want to create large databases from stockquotes?

    Kind regards
  8. Mcat11


    The easiest and fastest way to move your CSV to SQL is to import the CSV into Access and then use the include Upscale tool to move it to SQL. Literally takes seconds, of all of the software is installed on your PC.

    If you use SQL Express you will need to be certain that you install the Import/Export component to use the CSV import within SQL EXPRESS.
  9. Bob111


    if you need speed-flat binary file is way faster than SQL and csv. because it's load differently into array. you don't have to go thru each line. as for SQL-open MS Access and click blank database. done :)
  10. Brighton


    I know the original post is old, but it's still timely for me. Here's the question:

    I bought a zipped file from CRB/BarChart that contains one tiny csv (10kb or so) for each month of corn options going back 20+ years. Let's say there are 240 files. There are no column headers in the files and there are only four columns: contract name (symbol), date, historical volatility, implied volatility.

    Is there a way to run a script or something and get all those files into one Access table? I would like a Date Column, Symbol Column, HV Column, and IV Column and that would repeat many times, although numerous Date Columns would not be necessary (or they could be hidden).

    Edit to add: I think there would also need to be a way to automatically insert column headers so I know what month the HVs and IVs refer to (this could eliminate the need for a separate symbol column).

    I don't want to manually import 200+ individual files and then create 200+ queries to make one master data table. If I can automate the process I would like to buy more commodity histories.

    I'm out of my element here. Is the task able to be done by someone who's not that great with Access? Alternatively, is the task simple enough that the necessary scripts/instructions/macros could be written by someone I find on one of the freelancer sites? I don't mind paying if I could re-use the the "program" for additional commodities in the future and for updates to my current corn history (~1984-2012).
    #10     May 28, 2014