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
>