SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Importing multiple .txt files into one table - no SSIS


Importing multiple .txt files into one table - no SSIS

Author
Message
sqlrd22
sqlrd22
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 721
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.
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28078 Visits: 39926
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!

Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28078 Visits: 39926
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!

sqlrd22
sqlrd22
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 721
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.
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28078 Visits: 39926
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!

sqlrd22
sqlrd22
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 721
Thanks, that's really helpful. I'm giving it a go today.
koyyatrouser
koyyatrouser
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 66
Hi ..

I have tried the code. But the whichfile column doesnt populate the file name. Its showing access denied.
Can you please help ?
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28078 Visits: 39926
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!

djj
djj
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2758 Visits: 3448
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search