Import data from text or XLS file to a table on the server 1

[whateveragain]

I want users to be able to upload data from either a text file or XLS file. Below I'm using an example for XLS. Whether I write code to upload a text file or an Excel file. I keep getting the following error:
" ErrorCode=-2146232060
LineNumber=1
Message="Incorrect syntax near 'c:'. in importdirectories"

The filename imports into the stored procedure importdirectories just fine. Both the temporary and permanent tables are created, but data will not import and I get the error mentioned above. Any ideas?

ALTER PROCEDURE importdirectories
(
@Impfilename Char(30), -- user types in file name they want to import from their desktop
@MyId VarChar(50)
)AS
BEGIN

Declare @mydirs varchar(55) = 'mydirectories' + ltrim(rtrim(@MyId))
declare @SQL varchar(1000)

create table #mydirectories -- dump data into a table
(dircode integer)

-- My Excel import statement
set @sql = 'Insert into #mydirectories Select * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database='''+ @Impfilename +''';HDR=YES'',''SELECT * FROM [Sheet1$]'')';
exec(@SQL)

-- My text import statement
set @SQL = 'SELECT * INTO #mydirectories FROM OPENROWSET(BULK N'''+@Impfilename+''', SINGLE_BLOB) as dircode' --no error but doesn't work

-- Another text import statement - same error as above 'incorrect syntax near 'c:'
SET @sql = 'BULK INSERT #mydirectories FROM ' + @Impfilename +' WITH (ROWTERMINATOR = ''' + CHAR(10) + char(13) + ''')'
exec(@SQL)


exec('Select * into ' + @mydirs + ' from #mydirectories') -- copy from temp table to permanent table

 

[djj55]

1) What SQL version are you using
2) What is example value of @Impfilename
3) C: is the SQL Server C: not the user C: - I do not suggest you use the SQL Server as a file server.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[whateveragain]

1) What SQL version are you using - 2008

2) What is example value of @Impfilename - c:\output\Rand.txt or c:\output\Rand.txt is the location. I tried arranging the data different ways as follows:
first try:
12345
12224
12206
12154

second try with end of line marker:
12345'\n'
12224'\n'
12206'\n'
12154'\n'

thrid try:
12345'\n'12224'\n'12206'\n'12206'\n'12154'\n'12047'\n'90210

3) C: is the SQL Server C: not the user C: - I do not suggest you use the SQL Server as a file server. - I'm trying to get a single column of data given by the user and dump it into a table that will be used by the server. What do you suggest?

 

[djj55]

Question 1 helps in knowing what can be suggested.

The reason for the example question (2) was if there are spaces in the path or filename you need qualifiers as you have the single quote to signify the "string". Have you tried a double quote? (Just a guess)

Personally I hate the interface 2008 has for importing Excel. That is just me. To that end I have created a .NET program to import Excel files that is suited to what I need.

I know a few of the sticking points but have not worked with the OPENROWSET that much.

Usually when I answer a question someone comes along with a better answer.


djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[whateveragain]

RE: Ps 23 I need someone to lead me too

I'm open to any way I can dynamically get imported data into a table. Right now, I finanally succeeded with importing the data as a string, but I'm not successful with a dynamic bulk insert (see below).

set @SQL = 'BULK INSERT #mydirectories
FROM ' + @ImpString
exec(@SQL)