I need to import the "National Do Not Call" database into SQL server.
It is in XML format (*.XML) and I understand it consists of 2 fields: area
code and phone number and about 50 million records.
What is the best way to get this file into a SQL Server table?One option would be to look at using SQLXML and XML Bulk
Load. You can find more information, links and a download
link at:
http://msdn.microsoft.com/nhp/Default.asp?contentid=28001300
-Sue
On Thu, 18 Sep 2003 16:56:48 -0700, "DaveF"
<davef@.comcast.net> wrote:
>I need to import the "National Do Not Call" database into SQL server.
>It is in XML format (*.XML) and I understand it consists of 2 fields: area
>code and phone number and about 50 million records.
>What is the best way to get this file into a SQL Server table?
>
Showing posts with label code. Show all posts
Showing posts with label code. Show all posts
Friday, March 30, 2012
Wednesday, March 21, 2012
importing LongText fields
Hi all,
I've got a large db I downloaded in a .SQL format that came originally from
MySQL. I'm trying to alter the code to allow me to run it under MS-SQL.
I've got it mostly translated over, but I'm running into a problem with
MySQL's LONGTEXT data type. I created a unique data type (using SQL 2K5)
called LONGTEXT that I assigned to a VARCHAR(8000), and I'm still running
into problems with fields being too long to enter.
I guess the crux of my question is this: is there any way to go over the
8000-character limitation?
Regards,
Scott McNair
"Scott McNair" <scott.mcnair@.sfmco.takethispartout.com> wrote in message
news:Xns95DAA939F3E2Dsfmco@.207.46.248.16...
> Hi all,
> I've got a large db I downloaded in a .SQL format that came originally
> from
> MySQL. I'm trying to alter the code to allow me to run it under MS-SQL.
> I've got it mostly translated over, but I'm running into a problem with
> MySQL's LONGTEXT data type. I created a unique data type (using SQL 2K5)
> called LONGTEXT that I assigned to a VARCHAR(8000), and I'm still running
> into problems with fields being too long to enter.
> I guess the crux of my question is this: is there any way to go over the
> 8000-character limitation?
> Regards,
> Scott McNair
Take a look at text and ntext. You have a 2GB/1GB limit respectively.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||"Rick Sawtell" <quickening@.msn.com> wrote in news:ODEu8w#9EHA.2568
@.TK2MSFTNGP10.phx.gbl:
> Take a look at text and ntext. You have a 2GB/1GB limit respectively.
Perfect. I created a UDDT associating "longtext" with text, and it imports
more-or-less perfectly.
|||My apology for btting in this reply but I have the same issue where I need to
store +- up to 16000 characters in one field and I tried text or ntext but
the max size value is 16, can you tell me how to set up that field to accept
the 16000 i need or more?
Thanks
Xaviervp
"Scott McNair" wrote:
> "Rick Sawtell" <quickening@.msn.com> wrote in news:ODEu8w#9EHA.2568
> @.TK2MSFTNGP10.phx.gbl:
>
> Perfect. I created a UDDT associating "longtext" with text, and it imports
> more-or-less perfectly.
>
|||As Rick stated, the max text/ntext/image size is 2GB. You don't need to do
anything special.
The reported 16-byte length is the default text-in-row length. The default
length of 16 will hold the pointer to the separately stored value.
Hope this helps.
Dan Guzman
SQL Server MVP
"xaviervp" <xaviervp@.discussions.microsoft.com> wrote in message
news:66C5708A-B2F2-44ED-BC46-727AFD090EEB@.microsoft.com...[vbcol=seagreen]
> My apology for btting in this reply but I have the same issue where I need
> to
> store +- up to 16000 characters in one field and I tried text or ntext but
> the max size value is 16, can you tell me how to set up that field to
> accept
> the 16000 i need or more?
> Thanks
> Xaviervp
> "Scott McNair" wrote:
I've got a large db I downloaded in a .SQL format that came originally from
MySQL. I'm trying to alter the code to allow me to run it under MS-SQL.
I've got it mostly translated over, but I'm running into a problem with
MySQL's LONGTEXT data type. I created a unique data type (using SQL 2K5)
called LONGTEXT that I assigned to a VARCHAR(8000), and I'm still running
into problems with fields being too long to enter.
I guess the crux of my question is this: is there any way to go over the
8000-character limitation?
Regards,
Scott McNair
"Scott McNair" <scott.mcnair@.sfmco.takethispartout.com> wrote in message
news:Xns95DAA939F3E2Dsfmco@.207.46.248.16...
> Hi all,
> I've got a large db I downloaded in a .SQL format that came originally
> from
> MySQL. I'm trying to alter the code to allow me to run it under MS-SQL.
> I've got it mostly translated over, but I'm running into a problem with
> MySQL's LONGTEXT data type. I created a unique data type (using SQL 2K5)
> called LONGTEXT that I assigned to a VARCHAR(8000), and I'm still running
> into problems with fields being too long to enter.
> I guess the crux of my question is this: is there any way to go over the
> 8000-character limitation?
> Regards,
> Scott McNair
Take a look at text and ntext. You have a 2GB/1GB limit respectively.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||"Rick Sawtell" <quickening@.msn.com> wrote in news:ODEu8w#9EHA.2568
@.TK2MSFTNGP10.phx.gbl:
> Take a look at text and ntext. You have a 2GB/1GB limit respectively.
Perfect. I created a UDDT associating "longtext" with text, and it imports
more-or-less perfectly.
|||My apology for btting in this reply but I have the same issue where I need to
store +- up to 16000 characters in one field and I tried text or ntext but
the max size value is 16, can you tell me how to set up that field to accept
the 16000 i need or more?
Thanks
Xaviervp
"Scott McNair" wrote:
> "Rick Sawtell" <quickening@.msn.com> wrote in news:ODEu8w#9EHA.2568
> @.TK2MSFTNGP10.phx.gbl:
>
> Perfect. I created a UDDT associating "longtext" with text, and it imports
> more-or-less perfectly.
>
|||As Rick stated, the max text/ntext/image size is 2GB. You don't need to do
anything special.
The reported 16-byte length is the default text-in-row length. The default
length of 16 will hold the pointer to the separately stored value.
Hope this helps.
Dan Guzman
SQL Server MVP
"xaviervp" <xaviervp@.discussions.microsoft.com> wrote in message
news:66C5708A-B2F2-44ED-BC46-727AFD090EEB@.microsoft.com...[vbcol=seagreen]
> My apology for btting in this reply but I have the same issue where I need
> to
> store +- up to 16000 characters in one field and I tried text or ntext but
> the max size value is 16, can you tell me how to set up that field to
> accept
> the 16000 i need or more?
> Thanks
> Xaviervp
> "Scott McNair" wrote:
importing LongText fields
Hi all,
I've got a large db I downloaded in a .SQL format that came originally from
MySQL. I'm trying to alter the code to allow me to run it under MS-SQL.
I've got it mostly translated over, but I'm running into a problem with
MySQL's LONGTEXT data type. I created a unique data type (using SQL 2K5)
called LONGTEXT that I assigned to a VARCHAR(8000), and I'm still running
into problems with fields being too long to enter.
I guess the crux of my question is this: is there any way to go over the
8000-character limitation?
Regards,
Scott McNair"Scott McNair" <scott.mcnair@.sfmco.takethispartout.com> wrote in message
news:Xns95DAA939F3E2Dsfmco@.207.46.248.16...
> Hi all,
> I've got a large db I downloaded in a .SQL format that came originally
> from
> MySQL. I'm trying to alter the code to allow me to run it under MS-SQL.
> I've got it mostly translated over, but I'm running into a problem with
> MySQL's LONGTEXT data type. I created a unique data type (using SQL 2K5)
> called LONGTEXT that I assigned to a VARCHAR(8000), and I'm still running
> into problems with fields being too long to enter.
> I guess the crux of my question is this: is there any way to go over the
> 8000-character limitation?
> Regards,
> Scott McNair
Take a look at text and ntext. You have a 2GB/1GB limit respectively.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||"Rick Sawtell" <quickening@.msn.com> wrote in news:ODEu8w#9EHA.2568
@.TK2MSFTNGP10.phx.gbl:
> Take a look at text and ntext. You have a 2GB/1GB limit respectively.
Perfect. I created a UDDT associating "longtext" with text, and it imports
more-or-less perfectly.|||My apology for btting in this reply but I have the same issue where I need t
o
store +- up to 16000 characters in one field and I tried text or ntext but
the max size value is 16, can you tell me how to set up that field to accept
the 16000 i need or more?
Thanks
Xaviervp
"Scott McNair" wrote:
> "Rick Sawtell" <quickening@.msn.com> wrote in news:ODEu8w#9EHA.2568
> @.TK2MSFTNGP10.phx.gbl:
>
> Perfect. I created a UDDT associating "longtext" with text, and it import
s
> more-or-less perfectly.
>|||As Rick stated, the max text/ntext/image size is 2GB. You don't need to do
anything special.
The reported 16-byte length is the default text-in-row length. The default
length of 16 will hold the pointer to the separately stored value.
Hope this helps.
Dan Guzman
SQL Server MVP
"xaviervp" <xaviervp@.discussions.microsoft.com> wrote in message
news:66C5708A-B2F2-44ED-BC46-727AFD090EEB@.microsoft.com...[vbcol=seagreen]
> My apology for btting in this reply but I have the same issue where I need
> to
> store +- up to 16000 characters in one field and I tried text or ntext but
> the max size value is 16, can you tell me how to set up that field to
> accept
> the 16000 i need or more?
> Thanks
> Xaviervp
> "Scott McNair" wrote:
>
I've got a large db I downloaded in a .SQL format that came originally from
MySQL. I'm trying to alter the code to allow me to run it under MS-SQL.
I've got it mostly translated over, but I'm running into a problem with
MySQL's LONGTEXT data type. I created a unique data type (using SQL 2K5)
called LONGTEXT that I assigned to a VARCHAR(8000), and I'm still running
into problems with fields being too long to enter.
I guess the crux of my question is this: is there any way to go over the
8000-character limitation?
Regards,
Scott McNair"Scott McNair" <scott.mcnair@.sfmco.takethispartout.com> wrote in message
news:Xns95DAA939F3E2Dsfmco@.207.46.248.16...
> Hi all,
> I've got a large db I downloaded in a .SQL format that came originally
> from
> MySQL. I'm trying to alter the code to allow me to run it under MS-SQL.
> I've got it mostly translated over, but I'm running into a problem with
> MySQL's LONGTEXT data type. I created a unique data type (using SQL 2K5)
> called LONGTEXT that I assigned to a VARCHAR(8000), and I'm still running
> into problems with fields being too long to enter.
> I guess the crux of my question is this: is there any way to go over the
> 8000-character limitation?
> Regards,
> Scott McNair
Take a look at text and ntext. You have a 2GB/1GB limit respectively.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||"Rick Sawtell" <quickening@.msn.com> wrote in news:ODEu8w#9EHA.2568
@.TK2MSFTNGP10.phx.gbl:
> Take a look at text and ntext. You have a 2GB/1GB limit respectively.
Perfect. I created a UDDT associating "longtext" with text, and it imports
more-or-less perfectly.|||My apology for btting in this reply but I have the same issue where I need t
o
store +- up to 16000 characters in one field and I tried text or ntext but
the max size value is 16, can you tell me how to set up that field to accept
the 16000 i need or more?
Thanks
Xaviervp
"Scott McNair" wrote:
> "Rick Sawtell" <quickening@.msn.com> wrote in news:ODEu8w#9EHA.2568
> @.TK2MSFTNGP10.phx.gbl:
>
> Perfect. I created a UDDT associating "longtext" with text, and it import
s
> more-or-less perfectly.
>|||As Rick stated, the max text/ntext/image size is 2GB. You don't need to do
anything special.
The reported 16-byte length is the default text-in-row length. The default
length of 16 will hold the pointer to the separately stored value.
Hope this helps.
Dan Guzman
SQL Server MVP
"xaviervp" <xaviervp@.discussions.microsoft.com> wrote in message
news:66C5708A-B2F2-44ED-BC46-727AFD090EEB@.microsoft.com...[vbcol=seagreen]
> My apology for btting in this reply but I have the same issue where I need
> to
> store +- up to 16000 characters in one field and I tried text or ntext but
> the max size value is 16, can you tell me how to set up that field to
> accept
> the 16000 i need or more?
> Thanks
> Xaviervp
> "Scott McNair" wrote:
>
importing LongText fields
Hi all,
I've got a large db I downloaded in a .SQL format that came originally from
MySQL. I'm trying to alter the code to allow me to run it under MS-SQL.
I've got it mostly translated over, but I'm running into a problem with
MySQL's LONGTEXT data type. I created a unique data type (using SQL 2K5)
called LONGTEXT that I assigned to a VARCHAR(8000), and I'm still running
into problems with fields being too long to enter.
I guess the crux of my question is this: is there any way to go over the
8000-character limitation?
Regards,
Scott McNair"Scott McNair" <scott.mcnair@.sfmco.takethispartout.com> wrote in message
news:Xns95DAA939F3E2Dsfmco@.207.46.248.16...
> Hi all,
> I've got a large db I downloaded in a .SQL format that came originally
> from
> MySQL. I'm trying to alter the code to allow me to run it under MS-SQL.
> I've got it mostly translated over, but I'm running into a problem with
> MySQL's LONGTEXT data type. I created a unique data type (using SQL 2K5)
> called LONGTEXT that I assigned to a VARCHAR(8000), and I'm still running
> into problems with fields being too long to enter.
> I guess the crux of my question is this: is there any way to go over the
> 8000-character limitation?
> Regards,
> Scott McNair
Take a look at text and ntext. You have a 2GB/1GB limit respectively.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||"Rick Sawtell" <quickening@.msn.com> wrote in news:ODEu8w#9EHA.2568
@.TK2MSFTNGP10.phx.gbl:
> Take a look at text and ntext. You have a 2GB/1GB limit respectively.
Perfect. I created a UDDT associating "longtext" with text, and it imports
more-or-less perfectly.|||My apology for btting in this reply but I have the same issue where I need to
store +- up to 16000 characters in one field and I tried text or ntext but
the max size value is 16, can you tell me how to set up that field to accept
the 16000 i need or more?
Thanks
Xaviervp
"Scott McNair" wrote:
> "Rick Sawtell" <quickening@.msn.com> wrote in news:ODEu8w#9EHA.2568
> @.TK2MSFTNGP10.phx.gbl:
> > Take a look at text and ntext. You have a 2GB/1GB limit respectively.
> Perfect. I created a UDDT associating "longtext" with text, and it imports
> more-or-less perfectly.
>|||As Rick stated, the max text/ntext/image size is 2GB. You don't need to do
anything special.
The reported 16-byte length is the default text-in-row length. The default
length of 16 will hold the pointer to the separately stored value.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"xaviervp" <xaviervp@.discussions.microsoft.com> wrote in message
news:66C5708A-B2F2-44ED-BC46-727AFD090EEB@.microsoft.com...
> My apology for btting in this reply but I have the same issue where I need
> to
> store +- up to 16000 characters in one field and I tried text or ntext but
> the max size value is 16, can you tell me how to set up that field to
> accept
> the 16000 i need or more?
> Thanks
> Xaviervp
> "Scott McNair" wrote:
>> "Rick Sawtell" <quickening@.msn.com> wrote in news:ODEu8w#9EHA.2568
>> @.TK2MSFTNGP10.phx.gbl:
>> > Take a look at text and ntext. You have a 2GB/1GB limit respectively.
>> Perfect. I created a UDDT associating "longtext" with text, and it
>> imports
>> more-or-less perfectly.sql
I've got a large db I downloaded in a .SQL format that came originally from
MySQL. I'm trying to alter the code to allow me to run it under MS-SQL.
I've got it mostly translated over, but I'm running into a problem with
MySQL's LONGTEXT data type. I created a unique data type (using SQL 2K5)
called LONGTEXT that I assigned to a VARCHAR(8000), and I'm still running
into problems with fields being too long to enter.
I guess the crux of my question is this: is there any way to go over the
8000-character limitation?
Regards,
Scott McNair"Scott McNair" <scott.mcnair@.sfmco.takethispartout.com> wrote in message
news:Xns95DAA939F3E2Dsfmco@.207.46.248.16...
> Hi all,
> I've got a large db I downloaded in a .SQL format that came originally
> from
> MySQL. I'm trying to alter the code to allow me to run it under MS-SQL.
> I've got it mostly translated over, but I'm running into a problem with
> MySQL's LONGTEXT data type. I created a unique data type (using SQL 2K5)
> called LONGTEXT that I assigned to a VARCHAR(8000), and I'm still running
> into problems with fields being too long to enter.
> I guess the crux of my question is this: is there any way to go over the
> 8000-character limitation?
> Regards,
> Scott McNair
Take a look at text and ntext. You have a 2GB/1GB limit respectively.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||"Rick Sawtell" <quickening@.msn.com> wrote in news:ODEu8w#9EHA.2568
@.TK2MSFTNGP10.phx.gbl:
> Take a look at text and ntext. You have a 2GB/1GB limit respectively.
Perfect. I created a UDDT associating "longtext" with text, and it imports
more-or-less perfectly.|||My apology for btting in this reply but I have the same issue where I need to
store +- up to 16000 characters in one field and I tried text or ntext but
the max size value is 16, can you tell me how to set up that field to accept
the 16000 i need or more?
Thanks
Xaviervp
"Scott McNair" wrote:
> "Rick Sawtell" <quickening@.msn.com> wrote in news:ODEu8w#9EHA.2568
> @.TK2MSFTNGP10.phx.gbl:
> > Take a look at text and ntext. You have a 2GB/1GB limit respectively.
> Perfect. I created a UDDT associating "longtext" with text, and it imports
> more-or-less perfectly.
>|||As Rick stated, the max text/ntext/image size is 2GB. You don't need to do
anything special.
The reported 16-byte length is the default text-in-row length. The default
length of 16 will hold the pointer to the separately stored value.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"xaviervp" <xaviervp@.discussions.microsoft.com> wrote in message
news:66C5708A-B2F2-44ED-BC46-727AFD090EEB@.microsoft.com...
> My apology for btting in this reply but I have the same issue where I need
> to
> store +- up to 16000 characters in one field and I tried text or ntext but
> the max size value is 16, can you tell me how to set up that field to
> accept
> the 16000 i need or more?
> Thanks
> Xaviervp
> "Scott McNair" wrote:
>> "Rick Sawtell" <quickening@.msn.com> wrote in news:ODEu8w#9EHA.2568
>> @.TK2MSFTNGP10.phx.gbl:
>> > Take a look at text and ntext. You have a 2GB/1GB limit respectively.
>> Perfect. I created a UDDT associating "longtext" with text, and it
>> imports
>> more-or-less perfectly.sql
Monday, March 19, 2012
importing excel column with multiple values separated by '/'
I would like to import two columns from an excel file into a sql server
table as an area code - time zone look up.
The area code column sometimes has multiple area codes in the area code
cell. eg. 207/208/209.
What is a good way to import those two columns so that 3 table rows are
created for each of those Excel rows that contain these multiple values
separated by the '/' character?
Thank you,
GregOn Wed, 1 Mar 2006 07:32:52 -0800, hazz wrote:
>I would like to import two columns from an excel file into a sql server
>table as an area code - time zone look up.
>The area code column sometimes has multiple area codes in the area code
>cell. eg. 207/208/209.
>What is a good way to import those two columns so that 3 table rows are
>created for each of those Excel rows that contain these multiple values
>separated by the '/' character?
Hi Greg,
Some useful techniques are disccussed at
http://www.sommarskog.se/arrays-in-sql.html
Hugo Kornelis, SQL Server MVP|||Thank you Hugo, I'll take a look !
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:spdc0210aq8daomldhm1lmbpviqf7hp4gi@.
4ax.com...
> On Wed, 1 Mar 2006 07:32:52 -0800, hazz wrote:
>
> Hi Greg,
> Some useful techniques are disccussed at
> http://www.sommarskog.se/arrays-in-sql.html
> --
> Hugo Kornelis, SQL Server MVP
table as an area code - time zone look up.
The area code column sometimes has multiple area codes in the area code
cell. eg. 207/208/209.
What is a good way to import those two columns so that 3 table rows are
created for each of those Excel rows that contain these multiple values
separated by the '/' character?
Thank you,
GregOn Wed, 1 Mar 2006 07:32:52 -0800, hazz wrote:
>I would like to import two columns from an excel file into a sql server
>table as an area code - time zone look up.
>The area code column sometimes has multiple area codes in the area code
>cell. eg. 207/208/209.
>What is a good way to import those two columns so that 3 table rows are
>created for each of those Excel rows that contain these multiple values
>separated by the '/' character?
Hi Greg,
Some useful techniques are disccussed at
http://www.sommarskog.se/arrays-in-sql.html
Hugo Kornelis, SQL Server MVP|||Thank you Hugo, I'll take a look !
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:spdc0210aq8daomldhm1lmbpviqf7hp4gi@.
4ax.com...
> On Wed, 1 Mar 2006 07:32:52 -0800, hazz wrote:
>
> Hi Greg,
> Some useful techniques are disccussed at
> http://www.sommarskog.se/arrays-in-sql.html
> --
> Hugo Kornelis, SQL Server MVP
Monday, March 12, 2012
Importing Data Throught ASP
Hello,
I've been trying to work with code to import data from a "flat file" (.csv)
into a SQL 2000 table.
I'm not that well versed in SQL and was wondering is it was possible using
OPENROWSET or something similar?
I've tried using OPENROWSET and encountered the error "Ad hoc access to OLE
DB provider 'MSDASQL' has been denied. You must access this provider through
a linked server."
Any help or resources to explain the process would be fantastic.
-SteveIf you're trying to upload through an ASP page you don't want to use
OPENROWSET. That's really for accessing OLE DB data sources. For an example
of uploading a file via ASP check out:
http://msdn.microsoft.com/library/d...br />
0900.asp
Mike O.
"Anubis" <anubis2003@.bluestreem.com.au> wrote in message
news:ucZJcoO1DHA.208@.TK2MSFTNGP12.phx.gbl...
(.csv)
OLE
through
No, actually I wanted to upload data from a flat file (.csv) into a SQL
Database like to following
Flat File (x.csv) contains:
Col1, Col2, Col3
Value 0, Value1, Value 2,
Value 0, Value1, Value 2
Then do a command something like the following:
INSERT INTO dbo.table
SELECT Col1, Col2, Col3
FROM x.csv
Basically I want to open a rowset to insert into a SQL table but I cannot
open the rowset from the .csv source through an ASP command.
Any idea's?
Thanks
-Steve
"Michael Otey" <mikeo@.teca.com> wrote in message
news:eEOx90a1DHA.2336@.TK2MSFTNGP09.phx.gbl...
example
http://msdn.microsoft.com/library/d...tml/asp0900.asp
test file then probably the simplest thing is to use the BULK INSERT
statement something like the following:
BULK INSERT mydb..mytable FROM 'c:\mycsvfile.csv'
WITH (DATAFILETYPE = 'char',FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')
Mike O.
"Anubis" <anubis2003@.bluestreem.com.au> wrote in message
news:%23eXL8Kb1DHA.2396@.TK2MSFTNGP09.phx.gbl...
http://msdn.microsoft.com/library/d...tml/asp0900.asp
I've been trying to work with code to import data from a "flat file" (.csv)
into a SQL 2000 table.
I'm not that well versed in SQL and was wondering is it was possible using
OPENROWSET or something similar?
I've tried using OPENROWSET and encountered the error "Ad hoc access to OLE
DB provider 'MSDASQL' has been denied. You must access this provider through
a linked server."
Any help or resources to explain the process would be fantastic.
-SteveIf you're trying to upload through an ASP page you don't want to use
OPENROWSET. That's really for accessing OLE DB data sources. For an example
of uploading a file via ASP check out:
http://msdn.microsoft.com/library/d...br />
0900.asp
Mike O.
"Anubis" <anubis2003@.bluestreem.com.au> wrote in message
news:ucZJcoO1DHA.208@.TK2MSFTNGP12.phx.gbl...
quote:
> Hello,
> I've been trying to work with code to import data from a "flat file"
(.csv)
quote:
> into a SQL 2000 table.
> I'm not that well versed in SQL and was wondering is it was possible using
> OPENROWSET or something similar?
> I've tried using OPENROWSET and encountered the error "Ad hoc access to
OLE
quote:
> DB provider 'MSDASQL' has been denied. You must access this provider
through
quote:|||Hi Mike,
> a linked server."
> Any help or resources to explain the process would be fantastic.
> -Steve
>
No, actually I wanted to upload data from a flat file (.csv) into a SQL
Database like to following
Flat File (x.csv) contains:
Col1, Col2, Col3
Value 0, Value1, Value 2,
Value 0, Value1, Value 2
Then do a command something like the following:
INSERT INTO dbo.table
SELECT Col1, Col2, Col3
FROM x.csv
Basically I want to open a rowset to insert into a SQL table but I cannot
open the rowset from the .csv source through an ASP command.
Any idea's?
Thanks
-Steve
"Michael Otey" <mikeo@.teca.com> wrote in message
news:eEOx90a1DHA.2336@.TK2MSFTNGP09.phx.gbl...
quote:
> If you're trying to upload through an ASP page you don't want to use
> OPENROWSET. That's really for accessing OLE DB data sources. For an
example
quote:
> of uploading a file via ASP check out:
>
http://msdn.microsoft.com/library/d...tml/asp0900.asp
quote:|||If you're running this on the SQL Server system and just want to import the
[
color=darkred]
> Mike O.
>
> "Anubis" <anubis2003@.bluestreem.com.au> wrote in message
> news:ucZJcoO1DHA.208@.TK2MSFTNGP12.phx.gbl...
> (.csv)
using[QUOTE]
> OLE
> through
>
test file then probably the simplest thing is to use the BULK INSERT
statement something like the following:
BULK INSERT mydb..mytable FROM 'c:\mycsvfile.csv'
WITH (DATAFILETYPE = 'char',FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')
Mike O.
"Anubis" <anubis2003@.bluestreem.com.au> wrote in message
news:%23eXL8Kb1DHA.2396@.TK2MSFTNGP09.phx.gbl...
quote:
> Hi Mike,
> No, actually I wanted to upload data from a flat file (.csv) into a SQL
> Database like to following
>
> Flat File (x.csv) contains:
> Col1, Col2, Col3
> Value 0, Value1, Value 2,
> Value 0, Value1, Value 2
> Then do a command something like the following:
> INSERT INTO dbo.table
> SELECT Col1, Col2, Col3
> FROM x.csv
> Basically I want to open a rowset to insert into a SQL table but I cannot
> open the rowset from the .csv source through an ASP command.
> Any idea's?
> Thanks
> -Steve
>
> "Michael Otey" <mikeo@.teca.com> wrote in message
> news:eEOx90a1DHA.2336@.TK2MSFTNGP09.phx.gbl...
> example
>
http://msdn.microsoft.com/library/d...tml/asp0900.asp
quote:
[
color=darkred]
> using
to[QUOTE]
>
Friday, February 24, 2012
Importing csv to sql database
Hi All,
Here is my code
Dim strCon As String = System.Configuration.ConfigurationSettings.AppSettings("OwnerTrader")
con = New SqlConnection(strCon)
con.Open()
Dim strselect As String = ""
Try
strselect = "INSERT INTO tbl_CSV(CSV_TIME,CSV_SIZE,CSV_LOCATION,CSV_COUNTRY,CSV_LAT,CSV_LON,CSV_COMMENTS)"
strselect = strselect & " SELECT * FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=C:\UploadFiles;Extensions=CSV;','SELECT * FROM TestNew.csv')"
cmd = New SqlCommand(strselect, con)
cmd.ExecuteNonQuery()
con.Close()
i have defined connection string in web.config file and my csv file is inside C:\UploadFiles with the name TestNew.csv file.Can u please check it out the code?is it correct or not.I am getting this error
"SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online."
Regards
I've moved this to the SQL forum because this is not a FAQ. And the message you got isn't an error. It's simply a statement of what happened, why and how to fix it. Follow the instructions and you'll get past this message.
Jeff
Subscribe to:
Posts (Atom)