| Author |
insert data into SQL from text file
|
|
| Dabbler 2006-03-05, 8:24 pm |
| I have data in csv files I need to import into a remote sql server. I can't
use bulk insert (apparently they don't have it enabled on the server).
Does anyone have suggestions for an easy way to do this? I have a lot of
columns so would prefer not to have to handcode this in C#.
Thanks for any suggestions.
| |
| Roy Harvey 2006-03-05, 8:24 pm |
| Assuming you are using SQL Server 2000, in Enterprise Manager use the
Import Data wizard to set up a DTS package. DTS works well with CSV
files. If you are using 2005 you want Integration Services rather
than DTS.
Roy Harvey
Beacon Falls, CT
On Sat, 4 Mar 2006 18:18:27 -0800, Dabbler
<Dabbler@discussions.microsoft.com> wrote:
>I have data in csv files I need to import into a remote sql server. I can't
>use bulk insert (apparently they don't have it enabled on the server).
>
>Does anyone have suggestions for an easy way to do this? I have a lot of
>columns so would prefer not to have to handcode this in C#.
>
>Thanks for any suggestions.
| |
| Dabbler 2006-03-05, 8:24 pm |
| Thanks for your reply Roy. I don't have Enterprise Manager, just SQL Manager
Studio Express and EMS's SQL Manager 2005 Lite (way cool tool!). So DTS and
Integration Services are probably not in the cards. Any other suggestions?
"Roy Harvey" wrote:
> Assuming you are using SQL Server 2000, in Enterprise Manager use the
> Import Data wizard to set up a DTS package. DTS works well with CSV
> files. If you are using 2005 you want Integration Services rather
> than DTS.
>
> Roy Harvey
> Beacon Falls, CT
>
>
> On Sat, 4 Mar 2006 18:18:27 -0800, Dabbler
> <Dabbler@discussions.microsoft.com> wrote:
>
>
| |
| Ben Nevarez 2006-03-05, 8:24 pm |
|
If you do not have DTS or SSIS then you can use the bcp utility or the Bulk
insert T-SQL command. Bulk insert should be "enabled".
Ben Nevarez, MCDBA, OCP
Database Administrator
"Dabbler" wrote:
[color=darkred]
> Thanks for your reply Roy. I don't have Enterprise Manager, just SQL Manager
> Studio Express and EMS's SQL Manager 2005 Lite (way cool tool!). So DTS and
> Integration Services are probably not in the cards. Any other suggestions?
>
> "Roy Harvey" wrote:
>
| |
| Dabbler 2006-03-05, 8:24 pm |
| Thanks for the reply Ben.
Bulk Insert is disabled and I'm not sure how to enable it since the SQL
server I'm using is in a virtual shared hosting environment.
I tried using the BCP utility from my SQL Server 2005 Express installation
and get the following error:
Error = [Microsoft][ODBC SQL Server Driver]Invalid string or buffer length
while trying to create a format file with the following command:
C:\Documents and Settings\mla>bcp partners format partners.txt
-Smysqlserveraddress -Umyuserid -Pmypassword -c
I feel like I'm digging a hole ;)
"Ben Nevarez" wrote:
[color=darkred]
>
> If you do not have DTS or SSIS then you can use the bcp utility or the Bulk
> insert T-SQL command. Bulk insert should be "enabled".
>
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
>
> "Dabbler" wrote:
>
| |
| Steen Persson (DK) 2006-03-06, 7:23 am |
| Dabbler wrote:[color=darkred
]
> Thanks for the reply Ben.
>
> Bulk Insert is disabled and I'm not sure how to enable it since the SQL
> server I'm using is in a virtual shared hosting environment.
>
> I tried using the BCP utility from my SQL Server 2005 Express installation
> and get the following error:
>
> Error = [Microsoft][ODBC SQL Server Driver]Invalid string or buffer length
>
> while trying to create a format file with the following command:
>
> C:\Documents and Settings\mla>bcp partners format partners.txt
> -Smysqlserveraddress -Umyuserid -Pmypassword -c
>
> I feel like I'm digging a hole ;)
> "Ben Nevarez" wrote:
>
What about setting up a linked server? When setting up a linked server
for a text file, you can specify a schema.ini file that describes the
format of the text file.
Regards
Steen
|
|
|
|