Solved

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

Posted on 2004-08-29
10
386 Views
Last Modified: 2011-09-20
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
Comment
Question by:Michael Krumpe
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 11928556
What do you get if you do:

select [1],[2],[3] from #tempcontacts?
0
 
LVL 34

Expert Comment

by:arbert
ID: 11928557
(also, max points on a question is 500--mods will get you if they catch you doing more than 500)  :)
0
 
LVL 4

Author Comment

by:Michael Krumpe
ID: 11928624
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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 4

Author Comment

by:Michael Krumpe
ID: 11928628
This worked by the way in the side procedure trying the temporary table

Select [1], [2], [3] from #tempcontactsimp
0
 
LVL 12

Accepted Solution

by:
kselvia earned 500 total points
ID: 11928641
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
 
LVL 4

Author Comment

by:Michael Krumpe
ID: 11928653
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
 
LVL 12

Expert Comment

by:kselvia
ID: 11928697
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
 
LVL 10

Expert Comment

by:_Katka_
ID: 11929675
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
 
LVL 10

Expert Comment

by:_Katka_
ID: 11930045
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
 
LVL 4

Author Comment

by:Michael Krumpe
ID: 11931193
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query Optimization 14 42
SQL Server Designer 19 39
Help Required 3 92
CPU high usage when update statistics 2 28
I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question