SQL import 10000+ .csv files

Question

Unfortunately I have had issues with my storage and was forced to reacquire data. However, this came in many .csv files and don't know how to import all of them without doing it one by one. I would like to have the 10000+ .csv files into one table and would like help with coding all imports one time.

All of the files have the same schema:

'Symbol' (varchar(15))
'Date' (Date)
'Open' (Float)
'High' (Float)
'Low' (Float)
'Close' (Float)
'Volume' (Int)

Also: All files will have the same structure for their naming:

XXXXXX_YYYYMMDD 

(XXXXXX is the name of the market; I have 7 unique names)

Create Table [investment data 1].dbo.AA 
(
    Symbol varchar(15),
    [Date] Date,
    [Open] Float,
    High Float,
    Low Float,
    [Close] Float,
    Volume Int
)

At this point I do not know how to generate a loop that will look at all files in the "Investment Data" folder; the below example is the sample code for one .csv file. If there is a better way than "bulk insert" then I will modify the statement below.

bulk insert [investment data 1].dbo.AA
    from 'R:\Investment Data\NASDAQ_20090626.csv'
with
(
    firstrow=2
    ,rowterminator = '\n'
    ,fieldterminator = ','
)

Any help is appreciated; if I can be more clear please let me know. Thanks for your time.

Answer 1

Does what you wrote (for that one file) work ?

Great.

• Open a dos prompt
• Navigate to the folder with your 10,000 files

• type DIR /b >c:\temp\files.txt

• Now install a decent text editor, like Notepad++ (these instructions are for notepad ++)

• Open c:\temp\files.txt in that editor

• Open the find/replace dialog, place a tick next to "Extended (\n, \r..." - this makes it match newlines, and support newlines in replacements

• Put this in Find: \r\n

• Put this in Replace: ' with(firstrow=2,rowterminator = '\\n',fieldterminator = ',');\r\nbulk insert [investment data 1].dbo.AA from 'R:\Investment Data\

This will make your list of files that used to look like this:

a.txt
b.txt
c.txt
d.txt

Look like this:

a.txt' with(firstrow=2,rowterminator = '\n',fieldterminator = ',')
bulk insert [investment data 1].dbo.AA from 'R:\Investment Data\b.txt' with(firstrow=2,rowterminator = '\n',fieldterminator = ',');
bulk insert [investment data 1].dbo.AA from 'R:\Investment Data\c.txt' with(firstrow=2,rowterminator = '\n',fieldterminator = ',');
bulk insert [investment data 1].dbo.AA from 'R:\Investment Data\d.txt' with(firstrow=2,rowterminator = '\n',fieldterminator = ',');
bulk insert [investment data 1].dbo.AA from 'R:\Investment Data\

Now just clean up the first and last lines so it's a proper SQL. Paste and run in SSMS