Solved

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

Posted on 2004-08-29
10
390 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
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.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
OSQL to execute sql command 26 26
Index and Stats Management-Specific tables 8 23
SQL 2012 clustering 9 13
Amazon RDS Server load 2 17
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

820 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