Solved

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

Posted on 2004-08-29
10
380 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now