Importing multiple .txt files into one table - no SSIS
-
I wish to import multiple text files into one table in SQL Server 2008 but I don't have SSIS capability. I wish for the format of the file to stay the same (no formatting or modification of data) and I wish for each file to be dumped into its own row under one column heading (e.g. 'Data')
The link below is along the right lines of what I wish to do but I cannot work out how to take out the part that delimits the data as I'm fairly new to this.
http://www.nigelrivett.net/SQLTsql/ImportTextFiles.html
Any help greatly appreciated.
-
it's fairly easy; you would want to use BULK INSERT to bring the text files into a SQL table...but you have to know the layout of the data, and create teh destination table (whether it's a "staging" table or the real table) before you do anything else.
you also need to know how the file is delimtied...comma delimited , tab delimited, or fixed width, or some custom delimiter.
the example you pointed out is pretty good; can you be more specific about where you are having trouble?
can you give more specifics about your text files, how they are delimited, etc?
what table have you created for it so far?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
here's an example i've posted lots of times.
this inserts allt eh data into a table named "BULKACT, that has a single column "RAWDATA"
it imports all the files in four different directories, if htey end in .txt.
the field delimiter is something I'm sure doesn't exist in my data: four pipe characters in a row.
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 + '*.txt /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 + '*.txt /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 + '*.txt /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 + '*.txt /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 '%.txt%'
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 = 'BULK INSERT BULKACT FROM ''' + @path + @filename + ''' '
+ ' WITH (
DATAFILETYPE = ''char'',
FIELDTERMINATOR = ''||||'',
ROWTERMINATOR = ''\n'',
FIRSTROW = 1
) '
print @sql
exec (@sql)
fetch next from c1 into @path,@filename
end
close c1
deallocate c1
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I've been trying to use BULK INSERT but I'm getting this error (which I'm in the process of trying to solve)
Cannot bulk load because the file "C:\users\xxx" could not be opened. Operating system error code 5(Access is denied.).
The text files I'm trying to import are of various lengths split by pipes (|) but I wish for the data to not be changed (still want pipes included, no delimiting at all) so I only have one column in my table called 'Data' (varchax(MAX)) that I am dumping it into.
The trouble I'm having with the code on the link is that it delimits the data which I do not wish to do but I can't work out which bit to take out to stop it from delimiting, as I don't fully understand the code. It would work perfectly for what I want though, so I'd like to stick with it if possible.
-
for the delimiter issue, use something you KNOW will not exist in the data; a tilde ~ for example.
then everything will come in as asingle column of wide data.
For the permissions issue,
to access resources outside of a SQL database, (local files and folders, network shares, etc, , SQL uses a DIFFERENT account than the one you are logged in with/would typically expect.
SQL Server uses either the account set up as the proxy account, or if that is left blank(the default) it uses account it starts with to try and access the resource:
or if the above was blank, the account in services:
That account is often an account which has never logged into the domain, and was never assigned permissions to get to the local disk or network share.
As a result, you usually need to create a domain account in Active Directory, specifically grant it share access if it doesn't inherit it from Domain\Users or Domain\AuthenticatedUsers and change the account SQL Server starts with to that account.
Once that is done, and you stop and start the SQL service to make it use that account instead of old running values, your linked server/xp_cmdshell would work.
you can prove this is the issue by simply putting in your credentials, with your domain account and password, and confirm the external object you were trying to access/use works when SQL is run your credentials, so you'd know you need a domain account to access the resource.
[/quote]Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks, that's really helpful. I'm giving it a go today.
-
Hi ..
I have tried the code. But the whichfile column doesnt populate the file name. Its showing access denied.
Can you please help ?
-
koyyatrouser (5/26/2014)
Hi ..I have tried the code. But the whichfile column doesnt populate the file name. Its showing access denied.
Can you please help ?
Access denied is typically because the account in the servicesndoesnt have access.... examples are when you put files in c:\users\specificperson\desktop or my documents and the account cannot access. Move the files to a non private directory would fix it.
If the account does have access then itnmight be because thenfile is open in excel at the same time this process isntrying to open it as well.
If you give the exact error and path to files we could help further.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Make sure where SQL thinks c:\users\xxx is as it could be looking at the SQL server not the system where you are running the code.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply