October 18, 2012 at 11:49 am
Really feel like I should be able to figure this out but I'm somewhat stuck.
We have about 300 csv files that we need to load into the SQL Server 2008 database. I've tried SSIS, BCP, but I need it to make new tables in the database. One table for each csv file. Is there some way I can loop through the csv files and "select into" so we can make tables for the data?
Thanks for reading.
October 18, 2012 at 12:01 pm
Howard do all the files have header data as the first row?
i used to be able to do this with the Jet 32 but engine, creating the table on the fly :
SELECT *
--INTO NewTableName
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Text;Database=C:\Files\CSV;HDR=YES;FMT=Delimited',
'SELECT * FROM example.csv')
now, if we can get the jet engine to work order that WOW64 /exploit for 32 bit drivers i saw in another post, you might be in business.
from there, it's just xp_cmdshell to get the list of files, a cusror to loop thru the list, and assume the filename=new tablename i guess, right?
i bet tehre's an easier way in SSIS, but i'm a TSQl kind of guy;
Lowell
October 18, 2012 at 12:18 pm
ok a tiny bit of research, and i can openquery csv files with this command on either my 2008R2 or 2012 instances:
SELECT *
FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Access Text Driver (*.txt, *.csv)};DefaultDir=C:\data\;',
'SELECT * FROM abc.csv;' )
i've got a query in my snippets i'dd adapt now that will do all the files at once.
Lowell
October 18, 2012 at 12:24 pm
i only had 7 csv files, but this seemed to work just fine:
note i'm assuming multiple directories as well for the csv files.
uncomment out the "--exec(sql) if you are ready to give it a whirl.
/*
SELECT *
FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Access Text Driver (*.txt, *.csv)};DefaultDir=C:\data\;',
'SELECT * FROM abc.csv;' )
*/
--BULK INSERT MULTIPLE FILE
--a table to loop thru filenames drop table ALLFILENAMES
CREATE TABLE ALLFILENAMES(WHICHPATH VARCHAR(255),WHICHFILE varchar(255))
--the source table: yours already exists, but needed for this example.
--CREATE TABLE BULKACT(RAWDATA VARCHAR (8000))
--some variables
declare @filename varchar(255),
@path varchar(255),
@sql varchar(8000),
@cmd varchar(1000)
--get the list of files to process:
--#########################################
SET @path = 'C:\DB\'
SET @cmd = 'dir ' + @path + '*.csv /b'
INSERT INTO ALLFILENAMES(WHICHFILE)
EXEC Master..xp_cmdShell @cmd
UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null
SET @path = 'C:\DB2\'
SET @cmd = 'dir ' + @path + '*.csv /b'
INSERT INTO ALLFILENAMES(WHICHFILE)
EXEC Master..xp_cmdShell @cmd
UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null
SET @path = 'C:\DB3\'
SET @cmd = 'dir ' + @path + '*.csv /b'
INSERT INTO ALLFILENAMES(WHICHFILE)
EXEC Master..xp_cmdShell @cmd
UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null
SET @path = 'C:\DB4\'
SET @cmd = 'dir ' + @path + '*.csv /b'
INSERT INTO ALLFILENAMES(WHICHFILE)
EXEC Master..xp_cmdShell @cmd
UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null
--#########################################
--cursor loop
declare c1 cursor for SELECT WHICHPATH,WHICHFILE FROM ALLFILENAMES where WHICHFILE like '%.csv%'
open c1
fetch next from c1 into @path,@filename
While @@fetch_status <> -1
begin
--bulk insert won't take a variable name, so make a sql and execute it instead:
set @sql = 'SELECT *
INTO [' + REPLACE(@filename,'.csv','') + ']
FROM OPENROWSET(''MSDASQL'',
''Driver={Microsoft Access Text Driver (*.txt, *.csv)};DefaultDir=' + @path + ';'',
''SELECT * FROM ' + @filename + ';'' ) '
print @sql
--exec (@sql)
fetch next from c1 into @path,@filename
end
close c1
deallocate c1
Lowell
October 18, 2012 at 12:50 pm
I really appreciate you guys help. My roadblock is this:
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".
I'm using SQL Server 2008 in Windows Server 2008 R2 64 bit.
October 18, 2012 at 1:02 pm
a couple of prerequisites:
install the AccessDatabaseEngine_x64.exe from microsoft:
http://www.microsoft.com/en-us/download/details.aspx?id=13255
make sure you open an Administrative command prompt window, and run it with the c:\Downloads\AccessDatabaseEngine_x64.exe /passive
command line flag;
this will force the install of the drivers, even if you have 32 bit office installed;
otherwise you get some error about 32 bit Office preventing the install.
i think i also did this as well, but it has been a while since i installed them, and i'm not sure if it made a difference/the reason i did it anymore
EXEC master.dbo.sp_MSset_oledb_prop N'MSDASQL', N'AllowInProcess', 1
EXEC master.dbo.sp_MSset_oledb_prop N'MSDASQL', N'DynamicParameters', 1
Lowell
October 18, 2012 at 1:04 pm
Thank you! I did find this link http://www.microsoft.com/en-us/download/details.aspx?id=13255 and it is working now. Happy day!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply