?
Solved

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

Posted on 2004-08-29
10
Medium Priority
?
407 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
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 2000 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

719 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