| Author |
Topic |
|
vbx
Starting Member
38 Posts |
 Posted - 2007-10-08 : 23:41:47
|
| Hey folks,My first post and I hope not my last!!I am very new to the entire world SQL Server databases. I am starting from scratch.Currently I have a little Website I am doing for myself that is .asp based and will allow users to query some sports boxscores. I hope to create a user interface that will allow folks to seperate team results based on certain criteria...It is just a hobby of mine that I have been doing for year with excel and now hope to let others like me do it aswell.here is what I got.MSSQL 2005 Server with a database.Iam using SQL 2005 Server Express Studio. Therefore, do not have access to SSIS or DTS or anything like that.However, I want to import several hundred records into a db I created (hosted by Crystal tech). Since, I don't have access to the Server root directory, I can't use the BULK INSERT statement.I am looking for a method to query an excel file (or .csv something..) that is stored on my local drive and upload it to the Server db tables.I would like to do this either through SQL with a query. Or I would to add this VB code to the current VB that I use in my Excel file.any ideas..??Thank you for supporting me in advance. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-08 : 23:48:45
|
| You can use bcp.exe which is a command line tool that lets you import and export data. It reads csv files as long as they are in proper csv format.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
vbx
Starting Member
38 Posts |
Posted - 2007-10-09 : 00:39:19
|
| Will it let me insert bulk records? at one time?Can you elaborate a little more on it? That is one I have not heard of. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-09 : 00:42:13
|
| Yes. Look up bcp in SQL Server Books Online for more information.It's been around longer than BULK INSERT, DTS, or SSIS have.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-10-09 : 00:51:20
|
quote:
Originally posted by tkizer
It's been around longer than BULK INSERT, DTS, or SSIS have.
I guess it was inherited from sybase.
elsasoft.org |
|
|
|
vbx
Starting Member
38 Posts |
Posted - 2007-10-09 : 00:52:15
|
| I am not sure what I did..but I just typed BCP.EXP in the command prompt and I got a command window to flash but it will not stay open. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-09 : 00:54:06
|
quote:
Originally posted by jezemine
quote:
Originally posted by tkizer
It's been around longer than BULK INSERT, DTS, or SSIS have.
I guess it was inherited from sybase.
Indeed. When I had to support Sybase a long time ago when the Sybase DBA went on vacation, I was very surprised to find that they worked the same still. The Sybase system was on Unix and my knowledge was on Windows and Novell at the time, but I was easily able to do the work using my SQL Server and vi knowledge.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-09 : 00:55:13
|
quote:
Originally posted by vbx
I am not sure what I did..but I just typed BCP.EXP in the command prompt and I got a command window to flash but it will not stay open.
You have to pass it parameters. Open a cmd window. Type bcp /? and hit enter. That'll show you what to pass it. All of the switches are documented in BOL. Note: do not type bcp.exe from Start..Run as that will flash just like you are seeing.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-09 : 03:53:21
|
"It reads csv files as long as they are in proper csv format"I'd phrase that rather differently It reads csv files as long as they are simply formatting and consistent  No quoting to embed commas, no double-quoting of embedded quotes. Must have correct number of fields on every line. Must have line break on every line. Must no have extra line breaks after final row.At least that's my understanding, and I'll be happy to be enlightened that its better than that!Kristen |
|
|
|
|
Import Excel Data to --> SQL db table