Loading Multiple CSV files into Multiple tables in MSSQL

Question

I have stocks data as .CSV (total 500 CSVs). My objective is to create a database with multiple tables named as 'A', 'B' where A & B refer to two different stocks. I've a table already loaded in database which contains a column with all 500 symbols.

I want to iterate over each row such that I can read CSV and create a table for that stock in my database. For example if the first row in the symbols table is 'A' then I want to create a table in my database named A by reading a csv file which would be named as '/path/A.csv'

All the tables have same structure. There are four columns "Date", "Open", "High", "Low", "Close", "Volume"

I've created a table for one stock. For example in case of stock A by using following code

create table A(
  [Date] datetime2(7) NOT NULL,
  [Open] float NULL,
  High float NULL,
  Low float NULL,
  [close] float NULL,
  Volume float NULL
  )
  BULK INSERT A
    FROM 'D:\ASX\A.csv'
    WITH
    (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row

    TABLOCK
    )

Since I can not manually change the name from A to B and to C till it reaches 500 therefore I would like to know how can I iterate 500 rows from my symbols table such that for each iteration my above code changes from A to B to C and so on.

Do you have the list of list of symbols or is derived only from the files in the directory? It seems to be it would be better to add a symbol column and import all the files into a single table. That single entity is more appropriate for a relational database. — Dan Guzman, Jun 23 '18 at 09:54
I have a another table named symbols containing a single column named Ticker with 500 rows, where each row is a Ticker. For example the first row would be named as 'A'. — , Jun 23 '18 at 10:04
No. do not create a table per stock. Create one table with an additional column containing the stock code — Nick.Mc, Jun 23 '18 at 10:14
I have separate CSV's for each stock so even if I create one data table with an additional column of stock code I need to run a loop while reading CSV. How would I do that? — , Jun 23 '18 at 10:28
@f_hashim, I added an example of the normalized table design to my answer. — Dan Guzman, Jun 23 '18 at 10:51
If you have 500 CSV's you will need a while loop regardless of how you store it. Here's one way to import every file in a folder: https://stackoverflow.com/questions/16076309/import-multiple-csv-files-to-sql-server-from-a-folder — Nick.Mc, Jun 24 '18 at 10:23

Dan Guzman · Accepted Answer · 2018-06-23T10:50:43.660

Below is one method to create and import data for each ticker symbol in your symbols table with your existing design.

DECLARE @Path nvarchar(255) = 'D:\ASX';
DECLARE @SQL nvarchar(MAX);

SET @SQL = (SELECT N'
CREATE TABLE dbo.' + QUOTENAME(ticker) + N'(
  [Date] datetime2(7) NOT NULL,
  [Open] float NULL,
  High float NULL,
  Low float NULL,
  [close] float NULL,
  Volume float NULL
  );
BULK INSERT ' + QUOTENAME(ticker) + N'
    FROM ''' + @Path + N'\' + ticker + N'.csv''
    WITH
    (
    FIRSTROW = 2,
    FIELDTERMINATOR = '','',  --CSV field delimiter
    ROWTERMINATOR = ''\n'',   --Use to shift the control to next row
    TABLOCK
    );'
FROM dbo.symbols
FOR XML PATH(''), TYPE).value('.',N'nvarchar(MAX)');
EXECUTE sp_executesql @SQL;
GO

I suggest you reconsider this design and create a single table with a ticker symbol column and import all files into a single table. Below is an example of this method:

CREATE TABLE dbo.all_tickers(
  ticker_symbol varchar(32) NOT NULL,
  [Date] datetime2(7) NOT NULL,
  [Open] float NULL,
  High float NULL,
  Low float NULL,
  [close] float NULL,
  Volume float NULL,
  PRIMARY KEY (ticker_symbol, [Date])
);
GO

DECLARE @Path nvarchar(255) = 'D:\ASX';
DECLARE @SQL nvarchar(MAX);
CREATE TABLE #ticker_staging(
  [Date] datetime2(7) NOT NULL,
  [Open] float NULL,
  High float NULL,
  Low float NULL,
  [close] float NULL,
  Volume float NULL
  );

SET @SQL = (SELECT N'
TRUNCATE TABLE #ticker_staging;
BULK INSERT #ticker_staging
    FROM ''' + @Path + N'\' + ticker + N'.csv''
    WITH
    (
    FIRSTROW = 2,
    FIELDTERMINATOR = '','',  --CSV field delimiter
    ROWTERMINATOR = ''\n'',   --Use to shift the control to next row
    TABLOCK
    );
INSERT INTO dbo.all_tickers WITH (TABLOCKX)(
  ticker_symbol,
  [Date],
  [Open],
  High,
  Low,
  [close],
  Volume
) 
SELECT
  ''' + ticker + N''',
  [Date],
  [Open],
  High,
  Low,
  [close],
  Volume
FROM #ticker_staging;'
FROM dbo.symbols
FOR XML PATH(''), TYPE).value('.',N'nvarchar(MAX)');
EXECUTE sp_executesql @SQL;
GO

To create one single table do i need to have a single CSV containing all the symbols data with an additional column or a single table can be achieved by having data in multiple CSVs? — , Jun 23 '18 at 10:48
@f_hashim, no need for a single, although that would be desirable to avoid the dynamic SQL. I added an example for the single target table and multiple source files. — Dan Guzman, Jun 23 '18 at 10:53
Appreciated. I am going through the your example to build a better understanding and I believe a single table would be more feasible. — , Jun 23 '18 at 10:57

Loading Multiple CSV files into Multiple tables in MSSQL

1 Answers1