One more problem with importing csv files

Hi,

I got one more problem with importing csv files using .net. The problem is that the csv file contains double-quotation marks (""). For example, the record looks like:

...,Bearing Double "D" Flange,...

And the result is: ... | Bearing Double | null (all following columns are null)

The code is as following:

string strCsvConn =@."Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\;Extended Properties='text;HDR=Yes;FMT=Delimited(,)';";

using (OleDbConnection cn =newOleDbConnection(strCsvConn))

{

string strSQL ="SELECT * FROM " + strFileName;

OleDbCommand cmd =newOleDbCommand(strSQL, cn);

cn.Open();

using (OleDbDataReader dr = cmd.ExecuteReader())

{

while (dr.Read())

{

string str =Convert.ToString(dr[8]);

}

// Bulk Copy to SQL Server

//using (SqlBulkCopy bulkCopy = new SqlBulkCopy(strSqlConn))

//{

// bulkCopy.DestinationTableName = strSqlTable;

// bulkCopy.WriteToServer(dr);

//}

}

}

Any idea is highly appreciated.

shz

Hello shz,

I would recomend using a TextReader for processing a .csv file.

Hope this helps

regards,

G

|||

Thanks Gonzo. However, TextReader should be the last thing I'd like to use.

shz

sql

One for the advanced programmers!

I have a string column but I need to check if it contains any numeric
data ( in a WHERE Clause).
I did the following without any success
WHERE CONVERT(int,geocode) > 0
Gives an error as soon as it finds any non-numeric data.
Thanks> I have a string column but I need to check if it contains any numeric
> data ( in a WHERE Clause).
Try:
WHERE geocode LIKE '%[0-9]%'
Hope this helps.
Dan Guzman
SQL Server MVP
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1140448043.080918.304840@.g47g2000cwa.googlegroups.com...
> I have a string column but I need to check if it contains any numeric
> data ( in a WHERE Clause).
> I did the following without any success
> WHERE CONVERT(int,geocode) > 0
> Gives an error as soon as it finds any non-numeric data.
> Thanks
>|||S Chapman wrote:
> I have a string column but I need to check if it contains any numeric
> data ( in a WHERE Clause).
> I did the following without any success
> WHERE CONVERT(int,geocode) > 0
> Gives an error as soon as it finds any non-numeric data.
> Thanks
WHERE geocode NOT LIKE '%[^0-9]%'
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi
CREATE TABLE Sometable (currencyvalues VARCHAR(20) PRIMARY KEY)
INSERT INTO Sometable VALUES ('xnl 23.15')
INSERT INTO Sometable VALUES ('xnl')
INSERT INTO Sometable VALUES ('-23.15')
INSERT INTO Sometable VALUES ('muu')
SELECT * FROM Sometable WHERE currencyvalues LIKE '%[0-9]%'
--or
SELECT * FROM Sometable WHERE PATINDEX('%[0-9]%',currencyvalues)>0
Be aware that SQL Server will not probably use an index on the columnn ( if
you have one)
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1140448043.080918.304840@.g47g2000cwa.googlegroups.com...
> I have a string column but I need to check if it contains any numeric
> data ( in a WHERE Clause).
> I did the following without any success
> WHERE CONVERT(int,geocode) > 0
> Gives an error as soon as it finds any non-numeric data.
> Thanks
>|||S wrote on 20 Feb 2006 07:07:23 -0800:

> I have a string column but I need to check if it contains any numeric
> data ( in a WHERE Clause).
> I did the following without any success
> WHERE CONVERT(int,geocode) > 0
> Gives an error as soon as it finds any non-numeric data.
Are you look for numbers within the column, or a column that is just a
number? If the latter then you can using this:
WHERE ISNUMERIC(geocode) = 1
Dan|||Daniel
It will resturns wrong result as well as ISNUMERIC function is inaccurate
http://www.aspfaq.com/show.asp?id=2390
INSERT INTO Sometable VALUES ('xnl 23.15')
INSERT INTO Sometable VALUES ('.')
SELECT * FROM Sometable WHERE ISNUMERIC(currencyvalues)=1
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:%237y6iGjNGHA.740@.TK2MSFTNGP12.phx.gbl...
>S wrote on 20 Feb 2006 07:07:23 -0800:
>
> Are you look for numbers within the column, or a column that is just a
> number? If the latter then you can using this:
> WHERE ISNUMERIC(geocode) = 1
>
> Dan
>

one control loop with Substring to correct first normal column violation

given a table from an excel spreadsheet that contains a column that violates
first normal ;
Area Code TimeZone
787/939 4
212/646/718/917 5
I would like to create one procedure that loops through n possible values of
the area codes and creates a separate row for each value.
The Substring(s) below would have to start from positions
1,5,9,13,17,21,25,29 to capture the 3 character codes.
Right now I am manually creating each table with separate Substring
select/inserts with a union of all the tables and a select distinct.
The 65,000 rows of the original table end up deduped into about 245 rows of
distinct area codes per time zones.
Here are the first two sets of queries (there are 7 total) followed by the
consolidation with UNION ALL
-- create row with only first area code of '/' delimited values
CREATE TABLE first_areacode
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO first_areacode
(AreaCode, TimeZone)
select SUBSTRING(AreaCode, 1, 3) AS AreaCode,TimeZone
from AreaCodeTimeZone
-- create row with only second area code of '/' delimited values
CREATE TABLE second_areacode
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO second_areacode
(AreaCode, TimeZone)
select SUBSTRING(AreaCode, 5, 3) AS AreaCode,TimeZone
from AreaCodeTimeZone
insert into combined_areacode
SELECT * FROM first_areacode
UNION ALL
SELECT * FROM second_areacode
UNION ALL
SELECT * FROM third_areacode
UNION ALL
etc.
.
Thank you for ideas to help automate this,
-Greghazz (hazz@.sonic.net) writes:
> I would like to create one procedure that loops through n possible
> values of the area codes and creates a separate row for each value. The
> Substring(s) below would have to start from positions
> 1,5,9,13,17,21,25,29 to capture the 3 character codes. Right now I am
> manually creating each table with separate Substring select/inserts with
> a union of all the tables and a select distinct. The 65,000 rows of the
> original table end up deduped into about 245 rows of distinct area codes
> per time zones.
> Here are the first two sets of queries (there are 7 total) followed by the
> consolidation with UNION ALL
> -- create row with only first area code of '/' delimited values
> CREATE TABLE first_areacode
> (AreaCode varchar(50),
> TimeZone varchar(50))
> INSERT INTO first_areacode
> (AreaCode, TimeZone)
> select SUBSTRING(AreaCode, 1, 3) AS AreaCode,TimeZone
> from AreaCodeTimeZone
As long as you can rely on that the area codes are exact three
characters, no extra spaces etc, this could work:
SELECT substring(AreaCode, n, 3), TimeZone
FROM tbl
CROSS JOIN (SELECT n = 1 UNION ALL SELECT 5 UNION ALL SELECT 9 UNION ALL
SELECT 13 UNION ALL SELECT 17 UNION ALL SELECT 21 UNION ALL
..) AS n
WHERE len(AreaCode) <= n
By the way, which version of SQL Server are you using?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||SQL 2005.
Thank you Erland !
-Greg
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns977F34D4AFD2Yazorman@.127.0.0.1...
> hazz (hazz@.sonic.net) writes:
> As long as you can rely on that the area codes are exact three
> characters, no extra spaces etc, this could work:
> SELECT substring(AreaCode, n, 3), TimeZone
> FROM tbl
> CROSS JOIN (SELECT n = 1 UNION ALL SELECT 5 UNION ALL SELECT 9 UNION
> ALL
> SELECT 13 UNION ALL SELECT 17 UNION ALL SELECT 21 UNION ALL
> ...) AS n
> WHERE len(AreaCode) <= n
> By the way, which version of SQL Server are you using?
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||When I used this, the AreaCode column was blank although the TimeZone column
was populated.
I just reposted this with working sql that I would still like to condense
into an algorithm. Thanks, -Greg

> SELECT substring(AreaCode, n, 3), TimeZone
> FROM tbl
> CROSS JOIN (SELECT n = 1 UNION ALL SELECT 5 UNION ALL SELECT 9 UNION
> ALL
> SELECT 13 UNION ALL SELECT 17 UNION ALL SELECT 21 UNION ALL
> ...) AS n
> WHERE len(AreaCode) <= n

On DELETE On UPDATE Cascade syntax error

Hello

I need to be able to regularly, update or delete data from my parent table and subsequent child tables from A to Z, each table contains data. However, I have having problems.

I have already created the tables with primary keys on each table and foreign keys linking each table to the next.

I tried to delete a row from the parent table and was given this error:

DELETE FROM [dbo].[DomNam]
WHERE [DomNam]=N' football '

Error: Query(1/1) DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_DomNam'. The conflict occurred in database 'DomDB', table 'Dom_CatA', column 'DomNam'.

I tried to insert an alter table query:

ALTER TABLE dbo.DomNam
ADD CONSTRAINT FK_Dom_ID

REFERENCES dbo.Dom_CatA (Dom_ID)

ON DELETE CASCADE ON UPDATE CASCADE

But on Execute I saw this error:

Error]
Incorrect syntax for definition of the 'TABLE' constraint

What is wrong with the above syntax?

Or would it be better if I used a trigger instead because I already have foreign keys set within the tables?
If so please give an example of the syntax for the trigger I would need to update and cascade data from all tables.

I would be grateful for any advice. Thanks.

Hi

Have you missed something like this:

ALTER TABLE dbo.DomNamADD CONSTRAINT FK_Dom_IDFOREIGN KEY (Dom_ID)REFERENCES dbo.Dom_CatA (Dom_ID)ON DELETE CASCADE ON UPDATE CASCADE

Trigger could result such an error( DELETE statement conflicted with COLUMN REFERENCE constraint ..... ) either.

|||

(I need to be able to regularly, update or delete data from my parent table and subsequent child tables from A to Z, each table contains data.)

The above means what you are looking for require a trigger because Cascade On DELETE and UPDATE is DRI(declarative referential integrity), that means if A references B then B must exist, it is very clean simple relational Algebra, anything outside that you need a trigger. But you may have some columns that qualify for it, try the threads below for how to enable it. Hope this helps.

http://forums.asp.net/thread/1315554.aspx

http://forums.asp.net/thread/1120122.aspx

|||

Hi

Thanks guys for your reply and help.

I think that my problem is that I have created a many to many relationship without foreign key restraints.

On the parent table I have used the primary key from the first child table "A" as the foreign key.

The child tables are from A to Z.

With the first child table "A" I have used the Primary key from the parent as the foreign key.

With the child table "B" I have used the Primary key from the previous "A" table as the foreign key in "B" table. I have used the primary key as the foreign key in all subsequent tables without declaring referential integrity.

I know that in the very near future I will need to update and delete much of the records in these tables.

How do I remove the foreign keys so that I might be able to create a junction table, which I have since found that I need for a many to many table relationship. Or is there another easier way to do this?

Thanks

Lynn

|||

Hi guys

I have since learned how to delete the foreign keys.

I am viewing my SQL 2000 database using Aqua Data,

* Click on the last sibbling table.

* Click Alter table

* Select constrains tab

* Select FK + delete + OK

I have kept my Primary Keys in tact.

Now I need to relink my tables and ensure that I am able to cascade update or delete. All tables contain similar content, which will require updating or deleting often. Tables are from A to Z.. What is the best method to do this?

Thanks

Lynn

|||I have told you that all you can do is one to one referencing for DRI(declarative referential integrity) to work so if you have figured out the tables that qualify for if A references B then B must exist at the top of Enterprise Manager you will see enable relationship. And those that did not qualify you need a trigger. I hope I am very clear it is not DRI for A to Z but A to B. Hope this helps.|||

I just remembered I had a conversation with a database person who did not understand DRI(declarative referential integrity) so let me explain you have tables A to Z meaning 26 tables it means you can have only 13 DRIs that is 13 Cascade DELETE and Cascade UPDATE because it is a location based way to delete and update related data. So although foreign key is required to enable DRI not all foreign keys can be DRI.

You said the data is related that is DDL(data definition language) where relationship is determined by Upper and Lower bound Cardinality while DRI is DML(data manipulation language) keeping track of child data through one to one mapping if A references B then B must exist. I hope this makes it clear. You have to be careful with the ALTER TABLE ADD or DROP CONSTRAINT in 2000 because most ALTER must be done with Enterprise Manager or SQL Server 2000 will tell you it is wrong. One more thing you can use trigger to do it the way you want but triggers are resource intensive and may not fire all the time. Hope this helps.

http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part10/c3761.mspx?mfr=true

|||

Hi Caddre

Thanks for the reply, as you have gathered I am not a sql database expert, I am trying to understand DRI.

The whole think would be easier if I had everything in the one table (but it would be too wide to manage) because the same column of data is repeated on every table A to Z., therefore data row of "John Smith" exists in all tables. But in the child tables "A" table for example "John Smith" has extra columns e.g. art, apples, auto, etc.,

Consequently if I remove or insert a row in parent table, the children need the same edit.

Would it be just as complicated if I created a new table "juntion table" and include all the primary key's from the parent and children tables as foreign keys in the junction table. Or would that not work?

Thanks

Lynn

|||

I am sorry I did not come to this thread to confuse you but what you are saying now is not relational, so I have found you two solutions you can add to valid DRI. I also think it is Microsoft documentation that confused you because in 2005 multiple Cascade is allowed but one table cannot be repeated twice that is the same thing I am saying. Hope this helps.

http://support.microsoft.com/kb/142480

http://msdn2.microsoft.com/en-us/library/aa224818(sql.80).aspx