Need to add date during bulk import from csv

I'm downloading an wly retailer statement from our state's website in csv
format so I can import it into a SQL database and then use the data for some
custom reports. I've been able to successfully write my first bcp command to
import the file into an existing SQL database. However, I just realized that
the csv file I'm importing doesn't have any date information so I can select
database records based on a date. I've tried adding a Timestamp column at
the end of my database thinking it wouldn't interfere with the bulk import
but after I did such the import no longer worked. Does anyone have any idea
of how I can add a date field to each record during the import. Would BULK
INSERT allow me anymore flexibility? I could setup a user form where the
desired date is entered and then included with each record. Any suggestions
would be greatly appreciated.
Here's my bcp command:
bcp mydatabase.dbo.mytable in
c:\RetailerStatement.csv -c -t, -r\n -F3 -L9 -Smyservername -U -P
Thanks,
Barryyou want to create a datetime column with the default defined as getdate().
e.g.
alter table <tb> add crdate default getdate()
-oj
"BCS" <bswedeen@.tayloroil.com> wrote in message
news:kxikg.29114$JW5.5867@.southeast.rr.com...
> I'm downloading an wly retailer statement from our state's website in
> csv
> format so I can import it into a SQL database and then use the data for
> some
> custom reports. I've been able to successfully write my first bcp command
> to
> import the file into an existing SQL database. However, I just realized
> that
> the csv file I'm importing doesn't have any date information so I can
> select
> database records based on a date. I've tried adding a Timestamp column at
> the end of my database thinking it wouldn't interfere with the bulk import
> but after I did such the import no longer worked. Does anyone have any
> idea
> of how I can add a date field to each record during the import. Would BULK
> INSERT allow me anymore flexibility? I could setup a user form where the
> desired date is entered and then included with each record. Any
> suggestions
> would be greatly appreciated.
> Here's my bcp command:
> bcp mydatabase.dbo.mytable in
> c:\RetailerStatement.csv -c -t, -r\n -F3 -L9 -Smyservername -U -P
> Thanks,
> Barry
>|||No go. Here's the error I get:
#@. Row 1, Column 22: Invalid character value for cast specification @.#
The error repeats for all rows.
Barry
"oj" <nospam_ojngo@.home.com> wrote in message
news:O%23MGDlRkGHA.3588@.TK2MSFTNGP02.phx.gbl...
> you want to create a datetime column with the default defined as
getdate().
> e.g.
> alter table <tb> add crdate default getdate()
>
> --
> -oj
>
> "BCS" <bswedeen@.tayloroil.com> wrote in message
> news:kxikg.29114$JW5.5867@.southeast.rr.com...
command
at
import
BULK
>|||Try creating a view that selects all columns except
the (new) datetime column with the default date.
create view myview
as
select col1,col2,...
from mytable
Change your BCP command to import into this view instead.
bcp mydatabase.dbo.myview in c:\RetailerStatement.csv -c -t, -r\n -F3
-L9 -Smyservername -U -P|||1. my sample alter tb is missing 'datetime' datatype for the added column.
hopefully, you've caught that.
2. if your source cvs does not contain data for the datetime column, you
will need to exclude it by using a format file. you can, of course, use a
view as suggested by markc600.
-oj
"BCS" <bswedeen@.tayloroil.com> wrote in message
news:Hgxkg.29152$JW5.10677@.southeast.rr.com...
> No go. Here's the error I get:
> #@. Row 1, Column 22: Invalid character value for cast specification @.#
> The error repeats for all rows.
> Barry
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:O%23MGDlRkGHA.3588@.TK2MSFTNGP02.phx.gbl...
> getdate().
> command
> at
> import
> BULK
>|||My cvs file does not contain the datetime data and apparently the bcp
command won't work if I try to plug a default value in a datetime column in
my table or a timestamp column.
I've decided to import the csv file into an Excel workbook using a macro,
adding the date value, and then doing an INSERT into my database. I'm sure
using a VIEW as Mark suggested would have accomplished the same thing. The
Excel route gave me an opportunity to allow the user to verify the data
before making the final INSERT.
Thanks everyone!
Barry
"oj" <nospam_ojngo@.home.com> wrote in message
news:eiCqTuWkGHA.2200@.TK2MSFTNGP05.phx.gbl...
> 1. my sample alter tb is missing 'datetime' datatype for the added column.
> hopefully, you've caught that.
> 2. if your source cvs does not contain data for the datetime column, you
> will need to exclude it by using a format file. you can, of course, use a
> view as suggested by markc600.
> --
> -oj
>
> "BCS" <bswedeen@.tayloroil.com> wrote in message
> news:Hgxkg.29152$JW5.10677@.southeast.rr.com...
for
realized
column
any
>

Need tech information about DTS on data warehousing

Hello, everone:
Anybody can offer technical information about DTS appied in data warehousing? Website, tutorial, forum and books will be great. Thanks.
ZYThttp://search.msn.com/pass/results.aspx?q=DTS+SQL+SERVER&FORM=SMCRT

Need suggestions on text file parsing into database

I have a website, where people upload tab delimited text files of their product inventories, which the site parses and inserts into a database table. Here's the catch: Instead of insisting that each user use a standardized format, each user can upload the file in whatever column order they want, they just have to let the site know through a GUI which column is in which order. And, they may upload columns that if not mapped, will be ignored. Right now, I am doing all of this in code and it runs slow, I was thinking of offloading this to either a stored procedure, ssis, or bulk upload. But, with the varying format of the uploaded text file, I am not sure how I could do that. Any suggestions?

Thanks!

Hi keggdirdle,

As far as I can see, we cannot make the database recognize tab delimited text files automatically.

Whatever technology you use (stored procedure, ssis, or bulk upload), they all depends on fixed format for data.

In this case, I don't think it is possible without your own code.

Need help, error with store proc

I am trying to do an insert with a SPand I get the following error: "The name 'A2LA Website' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted."
When iParentID=30 and strTexte="A2LA Website"

What an I doing wrong?

Table:
ID int(identity)
ParentID int
Text nvarchar(50)
Valeur nvarchar(50) Allow Null
Ordre int Allow Nulls

 Public Sub addItemToDdSelection(ByVal strTable As String, ByVal iParentID As Int16, ByVal strTexte As String)
 Dim connect As New SqlConnection(strConnection)
 Dim cmdSelect As New SqlCommand("AddDropDownContent", connect)
 Dim paramReturnValue As SqlParameter
 Dim strError As String
 cmdSelect.CommandType = CommandType.StoredProcedure
 'PARAM
 cmdSelect.Parameters.Add("@.intParentID", iParentID)
 cmdSelect.Parameters.Add("@.strTexte", strTexte)
 Try
 connect.Open()
 cmdSelect.ExecuteNonQuery()
 connect.Close()
 Catch ex As Exception
 strError = ex.Message
 Finally
 If connect.State = ConnectionState.Open Then
 connect.Close()
 End If
 End Try
 End Sub
ALTER PROCEDURE dbo.AddDropDownContent
(
@.intParentID int,
@.strTexte varchar(50)
)
AS
EXEC ('INSERT INTO DropDownMenus (ParentID, Texte) VALUES(' + @.intParentID + ',"' + @.strTexte + '")')

(1) when you add parameters, specify their type/lengths too
xample :

cmdSelect.Parameters.Add("@.intParentID", iParentID)

should be

cmdSelect.Parameters.Add(New SqlParameter("@.intParentID",SqlDbType.int))
cmdSelect.Parameters("@.intParentID").Value = Trim(iParentID)

and
(2) you dont need dynamic sql to insert. you can modify your insert stmt as

Create Procedure ...
AS
SET NOCOUNT ON
 INSERT INTO DropDownMenus (ParentID, Texte) VALUES ( @.intParentID ,@.strTexte )
SET NOCOUNT OFF

hth|||Thanks for your reply this is really helping. A few questions,
-Trim(iParentID) is used in case the id is enter from a text box I guess?
-What is the role of the line "SET NOCOUNT ON" and "SET NOCOUNT OFF"?|||>>-Trim(iParentID) is used in case the id is enter from a text box I guess?

yes. you might also want to do a

Convert.ToInt16(Trim(iParentId))

to make sure you are sending in an integer type value. Its a good idea to validate user input.

>>What is the role of the line "SET NOCOUNT ON" and "SET NOCOUNT OFF"?

read up BOL (Books On Line)

hth