Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 411
  • Last Modified:

SQL CSV Query, identify column headers with EXTRA 500 points Total of 1000 points

I am attempting to put together a stored procedure which will read from a .csv file and dump them into a table called "contactsb"

This is my stored procedure

--------------------------------

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


ALTER   PROCEDURE dbo.sp_uploadrecords
      @sessname char(8),
      @filename varchar(255),
      @sqltxtinscol varchar(255),
      @sqltxtvalcol varchar(255),
      @userid int,
      @leadstatus bit,
      @folderid int,
      @campaigntype int,
      @schday int,
      @creationdate char(12),
      @errcount int
AS

Declare @insert varchar(1000),
      @values varchar(1000),
      @txtrecset varchar(1000),
      @tselect varchar(1000)

Set       @tselect = 'Select * from OpenRowset (''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)};
                                    DefaultDir=C:\tempcsvdir\' + LTRIM(@sessname) + ';'',
                                    ''select ' + @sqltxtvalcol + ',
                                          ' + LTRIM(STR(@userid)) + ' AS userid,
                                          ' + LTRIM(@leadstatus) + ' AS leadstatus,
                                          ' + LTRIM(STR(@folderid)) + ' AS folderid,
                                          ' + LTRIM(STR(@campaigntype)) + ' AS campaigntype,
                                          ' + LTRIM(STR(@schday)) + ' AS schday,
                                          ' + @creationdate + ' AS creationdate,
                                          ' + LTRIM(STR(@errcount)) + ' AS errcount
                                    from ' + @filename + ''')'


Set       @insert = 'Insert into contactsb (' + @sqltxtinscol + ')' + @tselect

print @insert
exec(@insert)

SET QUOTED_IDENTIFIER OFF

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

-----------------------------------------------

I am using the following to execute the procedure

------------------------------------------------

sp_uploadrecords '33422901','S33422901R10318534.csv','fname, lname, email, opt_ip, opt_ts, userid, leadstatus, folderid, campaigntype, schday, creationdate, errcount', '1, 3, 1, 9, 10',1,1,10,1,1,'8/29/2004',0

----------------------------------------------

What happens is, I organize the order of the field and the fields in the csv file in a process where the user can MAP the imported fields the fields of the database. With that, above, I build the select queries dynamically.

Now, the stored procedure works for all intensive purposes, but that...
when it imports the values into the database, it makes all the values of the fields the column names that I called them.

It "works" meaning, it imports the right number of records, but all the values are the names of the column headers.

----------------------------------------------

With my dilema, I thought, well, what if I dump the csv file into a temporary table first and then query the headers from the temporary table....

same deal

What happens is, when I execute this on the side to test in Query Analyzer

Select * Into #tempcontactsimp from OpenRowset   ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=C:\tempcsvdir\33422900\;HDR=yes;','select *, 4  AS tid from S33422900R35871274.csv')

Select * from #tempcontactsimp

the Grid view show the column headers... which the columns, (as you might of guessed from the code above) are name 1    2      3       4        5        6
Essentially the numeric number that I assigned to the column
I did that back in the web page to give the columns proper names as many .csv files uploaded may not, so its safer to make my own

Anyways.... but when I try to do
Select 1, 2, 3 from # tempcontacts

it returns all the proper rows, but the headers read [No Column Name] at the header of the grid, and all the values are 1, 2, and 3

I dont understand.

Is it that I shouldnt be naming the columns numberic names?

Help please.... a BONUS 500 points to the person who can help me figure this out and Im liberal with points for assistance.
0
Michael Krumpe
Asked:
Michael Krumpe
  • 4
  • 2
  • 2
  • +1
1 Solution
 
arbertCommented:
What do you get if you do:

select [1],[2],[3] from #tempcontacts?
0
 
arbertCommented:
(also, max points on a question is 500--mods will get you if they catch you doing more than 500)  :)
0
 
Michael KrumpeSolutions ArchitectAuthor Commented:
I get this now from my stored procedure when I execute it

Insert into contactsb (fname, lname, email, opt_ip, opt_ts, userid, leadstatus, folderid, campaigntype, schday, creationdate, errcount)Select * from OpenRowset ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
                                    DefaultDir=C:\tempcsvdir\33422901;',
                                    'select [1], [3], [1], [9], [10],
                                          1 AS userid,
                                          1 AS leadstatus,
                                          10 AS folderid,
                                          1 AS campaigntype,
                                          1 AS schday,
                                          8/29/2004    AS creationdate,
                                          0 AS errcount
                                    from S33422901R10318534.csv')
Server: Msg 7355, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' supplied inconsistent metadata for a column. The name was changed at execution time.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
Michael KrumpeSolutions ArchitectAuthor Commented:
This worked by the way in the side procedure trying the temporary table

Select [1], [2], [3] from #tempcontactsimp
0
 
kselviaRetiredCommented:
Consider this in your sp instead

Set      @tselect = 'Select * from OpenRowset (''Microsoft.Jet.OLEDB.4.0'',''Text;Hdr=NO;
                              DataBase=C:\tempcsvdir\' + LTRIM(@sessname) + ';'',
                              ''select ' + @sqltxtvalcol + ',
                                   ' + LTRIM(STR(@userid)) + ' AS userid,
                                   ' + LTRIM(@leadstatus) + ' AS leadstatus,
                                   ' + LTRIM(STR(@folderid)) + ' AS folderid,
                                   ' + LTRIM(STR(@campaigntype)) + ' AS campaigntype,
                                   ' + LTRIM(STR(@schday)) + ' AS schday,
                                   ''' + @creationdate + ''' AS creationdate,
                                   ' + LTRIM(STR(@errcount)) + ' AS errcount
                              from ' + @filename + ''')'

Added quotes around creationdate.

Invoke with

sp_uploadrecords '33422901','S33422901R10318534.csv','fname, lname, email, opt_ip, opt_ts, userid, leadstatus, folderid, campaigntype, schday, creationdate, errcount', 'F1, F3, F1, F9, F10',1,1,10,1,1,'8/29/2004',0

Used F1, F2 etc instead of 1, 2 for column names.

0
 
Michael KrumpeSolutions ArchitectAuthor Commented:
Insert into contactsb (fname, lname, email, opt_ip, opt_ts, userid, leadstatus, folderid, campaigntype, schday, creationdate, errcount)Select * from OpenRowset ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
                                    DefaultDir=C:\tempcsvdir\33422901;',
                                    'select [1], [3], [1], [9], [10],
                                          1 AS userid,
                                          1 AS leadstatus,
                                          10 AS folderid,
                                          1 AS campaigntype,
                                          1 AS schday,
                                          '8/29/2004   ' AS creationdate,
                                          0 AS errcount
                                    from S33422901R10318534.csv')
Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near '8'.
0
 
kselviaRetiredCommented:
Was that for Bret or me?  My suggestion has that error but your code is not my code.  I should have tested it.  In any case, changing the two single quotes to double quote around the date works for me.

Set      @tselect = 'Select * from OpenRowset (''Microsoft.Jet.OLEDB.4.0'',''Text;Hdr=NO;
                              DataBase=C:\tempcsvdir\' + LTRIM(@sessname) + ';'',
                              ''select ' + @sqltxtvalcol + ',
                                   ' + LTRIM(STR(@userid)) + ' AS userid,
                                   ' + LTRIM(@leadstatus) + ' AS leadstatus,
                                   ' + LTRIM(STR(@folderid)) + ' AS folderid,
                                   ' + LTRIM(STR(@campaigntype)) + ' AS campaigntype,
                                   ' + LTRIM(STR(@schday)) + ' AS schday,
                                   ''' + @creationdate + ''' AS creationdate,
                                   ' + LTRIM(STR(@errcount)) + ' AS errcount
                              from ' + @filename + ''')'
0
 
_Katka_Commented:
Hi, have you tried simple BULK INSERT command ? An advantage here is the fastest import available..

1) if you're able to manage it using direct BULK INSERT (+alternatively triggers to process inserted data)

CREATE PROCEDURE dbo.sp_uploadrecords
    @filename varchar(255)
AS

BULK INSERT contactsb
 FROM 'C:\tempcsvdir\'+@filename
 WITH (
  FIELDTERMINATOR=';',
  ROWTERMINATOR='\n',
  DATAFILETYPE='char',
  CODEPAGE='RAW'
)

GO

2) otherwise you need to bulk insert it to temporary table and then transform it to desired form, but in case of simple CVS this
    shouldn't be needed

regards,
Kate
0
 
_Katka_Commented:
Well that procedure should rather be:

CREATE PROCEDURE dbo.sp_uploadrecords
    @FILENAME varchar(255)
AS

DECLARE @SQL varchar(1000)
SET QUOTED_IDENTIFIER OFF
SET @SQL="BULK INSERT TEST "+
   " FROM 'C:\tempcsvdir\"+@FILENAME+"'"+
   " WITH ("+
   "  FIRSTROW=2,"+
   "  FIELDTERMINATOR=';',"+
   "  ROWTERMINATOR='\n',"+
   "  DATAFILETYPE='char',"+
   "  FORMATFILE='C:\test.fmt',"+
   "  CODEPAGE='RAW')"
EXEC (@SQL)
GO

"FIRSTROW" starts copying on second row (because first row are column definitions mostly)
"FORMATFILE" parameter loads (as the title says) a format file, that is structured like this: http://www.volny.cz/genocide/format.png 

regards,
Kate
0
 
Michael KrumpeSolutions ArchitectAuthor Commented:
It works now!

This is what ended up working in the end;

----------------------------------------------------------

Set       @tselect = 'Select * from OpenRowset (''Microsoft.Jet.OLEDB.4.0'',''Text;Hdr=NO;
                                          DataBase=C:\tempcsvdir\' + LTRIM(@sessname) + ';'',
                                    ''select ' + @sqltxtvalcol + ',
                                          ' + LTRIM(STR(@userid)) + ' AS userid,
                                          ' + LTRIM(@leadstatus) + ' AS leadstatus,
                                          ' + LTRIM(STR(@folderid)) + ' AS folderid,
                                          ' + LTRIM(STR(@campaigntype)) + ' AS campaigntype,
                                          ' + LTRIM(STR(@schday)) + ' AS schday,
                                          ' + LTRIM(@creationdate) + ' AS creationdate,
                                          ' + LTRIM(STR(@errcount)) + ' AS errcount
                                    from ' + @filename + ''')'


Set       @insert = 'Insert into contactsb (' + @sqltxtinscol + ')' + @tselect

print @insert
exec(@insert)

---------------------------------------------------------------
Printing out the following
---------------------------------------------------------------

Insert into contactsb (fname, lname, email, opt_ip, opt_ts, userid, leadstatus, folderid, campaigntype, schday, creationdate, errcount)Select * from OpenRowset ('Microsoft.Jet.OLEDB.4.0','Text;Hdr=NO;
                                          DataBase=C:\tempcsvdir\33422901;',
                                    'select [1], [3], [1], [9], [10],
                                          1 AS userid,
                                          1 AS leadstatus,
                                          10 AS folderid,
                                          1 AS campaigntype,
                                          1 AS schday,
                                          8/29/2004  AS creationdate,
                                          0 AS errcount
                                    from S33422901R10318534.csv')

(111 row(s) affected)

-----------------------------------------------

I would like to split/award the points for helping me with this to kselvia and to arbert


arber, please reply at http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21112201.html to pick up your points there.


Thanks again for all your help, this really got me out of a bind.

Michael
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now