loading txt file into sql table

[Pattycake245]

I have a txt file where each valuein each line is separated by commas. I am trying to load this into an already created sql table using <cfstoredproc> and each value separated by a comma represents a field in the table. I have been trying to use an array to load in each line and send it to the stored procedure, but can't get my head around how to do that. Any help would be appreciated. Here is an example of the data I'm working with:

e0920410,20040921,40144,0,0,0,0,1,0,1,0,1,0,0,0,0,0,0

Tim

 

[TruthInSatire]

what kind of db and where is the file?

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)

 

[philhege]

Hi Tim,

If you have MSSQL, I'd create a DTS package for this, and call it by creating a SQL Server Agent job. The DTS environment is tailor-made for this sort of operation.

<deep breath> However, if you don't want to go that route or you don't have access to such tools, here's the approach:

I assume your text file is carriage-return delimited, and that you've loaded the contents of the text file into a variable, which I'll call "INSRECS".

<cfloop index="thisrow" list="#INSRECS#" delimiters="#chr(13)#">
<cfset passdata=thisrow>
<Cfstoredproc dbname="IIMAK" datasource="sql" procedure="gensp_HandleCDL">
<cfprocparam type="IN" cfsqltype="CF_SQL_LONGVARCHAR" dbvarname="@p_vdatarow" value="#passdata#">
<cfprocresult name="result">
</cfstoredproc>
<cfloop>

<MSSQL-biased solution>
Now, the solution lies in using dynamic SQL within your SP to create and issue the INSERT statement. Using the example call above:

use MyDB
go
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'gensp_HandleCDL' AND type = 'P')

DROP PROCEDURE gensp_HandleCDL
GO
CREATE PROCEDURE gensp_HandleCDL
@p_vdatarow varchar(1024)
AS




set nocount on
declare @l_vSQL varchar(2048)
set @l_vSQL = 'INSERT INTO Mytable (--field names here, corresponding to the passed info)
VALUES ('+@p_vdatarow+')'

exec(@l_vSQL)

RETURN @@ERROR
</MSSQL-biased solution>

To handle varying datatypes (obviously you have text in there), do an inner loop inside the main CFLOOP based on the datarow list, and set a counter. Reconstruct the list with single quote marks in the right places:
<Cfset counter=0>
<cfset returnval=""
<cfloop index="thisval" list="#thisrow#">
<cfset counter=counter+1>
<cfif counter is 1 or counter is 2>
<cfset returnval=returnval&"'"&#thisval#&"',">
<cfelse>
<cfset returnval=returnval&#thisval#&",">
</cfif>
</cfloop>

<cfset passdata = mid(returnval,1,len(returnval)-1>

Enjoy.






Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
A priest, a lawyer, and a rabbi walk into a bar. The bartender looks up and says "What is this, a joke?

 

[Pattycake245]

philhege,

I was able to apply parts your techniques and have gotten it to work using an array, except for one thing. Some of the rows of data, specifically at position 4 in each row, will be blank. It seems when this happens that row is only read as a four part array. I have tried numerous things to populate this with any value so the stored procedure will run. Is there any way to get around this problem?

i.e.:

e0920410,20040921,41313,,retry

Tim

 

[Pattycake245]

Never mind, I figured it out

 

[Pattycake245]

New Problem,

In the file I'm being passed, there may be some rows with one or more values missing, so the file will look like sonething below:

d0921461,20040920,13096,,dead,168,,,913156892113

How do I read this in so that the ",," are counted as an element and not thrown out? In other words so it shows a length of 9 elements not 6.

thanks, Tim

 

[philhege]

Empty list elements are ignored, as you've discovered. You'll have to "fill" them with something you can use as a placeholder.

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
A priest, a lawyer, and a rabbi walk into a bar. The bartender looks up and says "What is this, a joke?