Database Discussion Boards

Re: BULK INSERT into SQL Server from CSV with apostrophes instead the strings

T210230-Mar-10 23:22

30-Mar-10 23:22

Okay, then I will make all of my strings varchar for this table. Are there any problems with loading of doubles or big ints that you are aware of?

Re: BULK INSERT into SQL Server from CSV with apostrophes instead the strings

R. Giskard Reventlov30-Mar-10 23:11

R. Giskard Reventlov

30-Mar-10 23:11

Firstly, why do you need to remove the apostrophes? Are they not part of the text you are importing or are they superfluous characters that shouldn't have been there?

Either way you might consider opening the csv file in code and then either doubling the apostrophes or removing them, something like (and this is a very simplistic example):

string filePath = "full_path_to_the_csv_file";
string text = File.ReadAllText(filePath);

// Either:
text = text.Replace("'", "''");
// or
text = text.Replace("'", string.Empty);

File.WriteAllText(filePath, text);

You'll need to adjust to suit but it should get you started.

me, me, me

"The dinosaurs became extinct because they didn't have a space program. And if we become extinct because we don't have a space program, it'll serve us right!"
Larry Niven

Re: BULK INSERT into SQL Server from CSV with apostrophes instead the strings

T210230-Mar-10 23:28

T2102

30-Mar-10 23:28

The apostrophes are part of the text. Appreciate the help. I will this later today as well and see how well it works. I am trying to load a couple hundred csv files.

Re: BULK INSERT into SQL Server from CSV with apostrophes instead the strings

Mycroft Holmes31-Mar-10 1:27

Mycroft Holmes

31-Mar-10 1:27

I remove the single quotes, we regularly export the data as csv file at some point and text identifiers are not supported by SSIS (I think, one of MS core technologies does not support text qualifiers "" therefor cannot deal with single quotes in the data, astonishing). So we get a cleaner result and the our users don't give a rats ass about single quotes.

Never underestimate the power of human stupidity
RAH

Re: BULK INSERT into SQL Server from CSV with apostrophes instead the strings

R. Giskard Reventlov31-Mar-10 5:09

R. Giskard Reventlov

31-Mar-10 5:09

Mycroft Holmes wrote:
users don't give a rats ass

Would have both said it all and been very precise Smile | :)

me, me, me

"The dinosaurs became extinct because they didn't have a space program. And if we become extinct because we don't have a space program, it'll serve us right!"
Larry Niven

Re: BULK INSERT into SQL Server from CSV with apostrophes instead the strings

T21021-Apr-10 3:25

T2102

1-Apr-10 3:25

Given the double quote issue in the data set, I cannot use the ADO .NET csv file reading capability directly. Instead I tried using ReadAllText and WriteAllText to remove the single quotes and double quotes.

I keep getting an out of memory error after a few minutes of inserts. I was able to load the datasets (before discovering the double quote issue)without any memory issues. I have 4 GB of physical memory and a maximum 16 GB virtual memory setting on my laptop. My aggregate datasets should take no more than 6GB of space in SQL Server. I've tried rebooting a couple of times and that did not help. Any ideas?

void Cleanse_File(System::String ^Full_Path_To_File,
long & ErrorCode) {
try {
System::String ^text = System::IO::File::ReadAllText(Full_Path_To_File);
text = text->Replace("'", "");
text = text->Replace("\"", "");
System::IO::File::WriteAllText(Full_Path_To_File, text);
}
catch(System::Exception ^e) {
System::Console::WriteLine(e->Message);
}
}

At this point, I am thinking of using a different csv file reader and hoping that it can handle the double quotes without mismapping columns.

Re: BULK INSERT into SQL Server from CSV with apostrophes instead the strings

R. Giskard Reventlov1-Apr-10 4:04

R. Giskard Reventlov

1-Apr-10 4:04

If the files are large you may need to read them in a line at a time.

me, me, me

"The dinosaurs became extinct because they didn't have a space program. And if we become extinct because we don't have a space program, it'll serve us right!"
Larry Niven

Re: BULK INSERT into SQL Server from CSV with apostrophes instead the strings [modified]

T210231-Mar-10 19:07

T2102

31-Mar-10 19:07

I am going to start working on this shortly. I decided to remove single and double quotes, so that I would get the same results if I ran a script on the file again. If I did not have the double quote issue and several other formatting problems, then I could have called a bulk load or copy afterwards.
I still might make sense to clean the full data first and then do a bulk load.

My problem with my data sources files is that the first field is sometimes split into several columns and the last few columns are sometimes missing. So I have a gross C++ program to remap the data to try to get the correct table structure.

I noticed that the results are incorrect for Rows when double quotes are utilized.
I am specifying FMT=Delimited(,) in my connection string for a csv file.
My query is SELECT * FROM C:\X.csv

Suppose a row contained Generic 1st "LCD" Monitor, 1, 2, 3. The query results are coming back as "Generic 1st", Null, Null, Null.

modified on Thursday, April 1, 2010 2:47 AM

Deducting data vs saving it explicitly

Alivemau530-Mar-10 11:11

Alivemau5

30-Mar-10 11:11

Hi,

I'm kind of confused on this.
If I have data on tables, and I'd like to know something which is deducted from that data, but requires several tables and calculations.. should I make that "something" an explicit data (table, attribute, who knows)?

For example:
I have a table of "goals", "matches" and "teams" of some football tournament.

and I want to know the number of win matches of some team, then I would have to calculate them by comparing the number of goals, etc etc.

But if Id put a column of "WonGames" in the table of teams, then this wouldnt be needed

What is the correct approach?

Thanks

Re: Deducting data vs saving it explicitly

PIEBALDconsult30-Mar-10 12:13

PIEBALDconsult

30-Mar-10 12:13

Alivemau5 wrote:
deducted

Deduced?

With some such things, you might want to do both; have a detailed transaction table and a summary table.

Re: Deducting data vs saving it explicitly

Garth J Lancaster30-Mar-10 15:51

Garth J Lancaster

30-Mar-10 15:51

continuuing from PIEBALDconsult, this is why a good deal of time usually goes into data analysis (and sometimes even then it takes multiple evolutions to get it right)

If dynamically recalculating the result(s) each time starts getting onerous or you need them elsewehere and dont wish to use temp tables for example, you may need to maintain a transaction table as he suggests

'g'

Re: Deducting data vs saving it explicitly

PIEBALDconsult30-Mar-10 17:48

PIEBALDconsult

30-Mar-10 17:48

P.S.

Alivemau5 wrote:
put a column of "WonGames" in the table of teams

Then I think you could only track one season at a time; you may need a season table -- which might be a good place to store such summary data.

Re: Deducting data vs saving it explicitly

Eddy Vluggen31-Mar-10 0:36

Eddy Vluggen

31-Mar-10 0:36

Alivemau5 wrote:
What is the correct approach?

Determining the "cost" of recalculating the results. If it takes a lot of time to reproduce the data, then store it Smile | :)

I are Troll Suspicious | :suss:

farokhian30-Mar-10 9:44

farokhian

30-Mar-10 9:44

Hi all
I install microsoft sql 2005 and it is in windows authentication mode;
but now i want to set password on my database;I mean that when i want to have connection to my database i should enter password;
What should i do?
what differences is necessary to do this?
I try some ways but i can't get answer!?

Re: login

netJP12L30-Mar-10 10:13

netJP12L

30-Mar-10 10:13

change from windows authentication mode to sql server authentican mode

Re: login

farokhian30-Mar-10 10:42

farokhian

30-Mar-10 10:42

Thanks alot for your attention;
I change server authentication form windows authentication mode to sql server and windows authentication mode;and after that i create new login and i set password and i enabled login .
but these changes doesn't done and when i want to ok these changes , an error occured:(what should i do)

TITLE: Microsoft SQL Server Management Studio
------------------------------

Create failed for Login ''. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Login&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name. (Microsoft SQL Server, Error: 1038)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=1038&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Re: login

Don Burton30-Mar-10 10:21

Don Burton

30-Mar-10 10:21

Have you gone to Security/Logins to Add/Change a User/Login? Here's a link that might help: http://www.sqlservercentral.com/articles/SQL+Server+2005+-+Security/sqlserver2005logins/2474/[^]

table join

netJP12L30-Mar-10 8:15

netJP12L

30-Mar-10 8:15

Hi guys.
i want join the results this table below with matching records of another table. How could i do it. I have tried using the right join on class table but seems like not getting the right results. could you hlep me joining the class table with all all those records are only in class table.
Thanks

select ucv.cf_value+', '+ucv2.cf_value as FullName
from users u
left join CUSTOM_FIELDS_VALUE ucv on ucv.cf_id=3
left join CUSTOM_FIELDS_VALUE ucv2 on ucv2.cf_id = 2
right join class on class.instructorName= ucv.cf_value+', '+ucv2.cf_value
where u.uid=ucv.uid and u.uid=ucv2.uid
order by ucv.cf_value+', '+ucv2.cf_value asc

Re: table join

Mycroft Holmes30-Mar-10 22:56

Mycroft Holmes

30-Mar-10 22:56

Oh dear have you got a problem. Looks like you are using the instructors name as a key field for joining tables - WRONG. Each table should have an ID field (usually and int IDENTITY(1,1)) as the primary key and then a foreign key to maintain data integrity.

Your join structure is wrong. A left join to UCV2 and THEN a right join the to class means that you will get records, wait I'm have no idea what you would get but it would be a mess.

Take a look at this Visual Representation of SQL Joins[^] for some clarification

Never underestimate the power of human stupidity
RAH

Help with T-SQL Statement??

ostinoh30-Mar-10 5:02

ostinoh

30-Mar-10 5:02

Hello -

I'm in the process of using a script that will help with re-indexes. I keep getting the following errors.

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'ELSE'.

My database name is Backdrop and the table is dbo.PJTRAN and the index is pjtran0. Here is the script if someone can help me understand why I keep getting those errors.

IF EXISTS (SELECT [object_id],[index_id],[avg_fragmentation_in_percent] FROM sys.dm_db_index_physical_stats
(DB_ID(N'Backdrop'), OBJECT_ID(N'PJTRAN.PJTRAN0'), NULL, NULL , 'DETAILED')
WHERE [avg_fragmentation_in_percent] < 30)

BEGIN

ALTER INDEX ALL ON PJTRAN REORGANIZE

END
ELSE

ALTER INDEX ALL ON PJTRAN REBUILD

Thank you..

Re: Help with T-SQL Statement??

Don Burton30-Mar-10 10:23

Don Burton

30-Mar-10 10:23

Have you executed the script in Query Analyzer?

Re: Help with T-SQL Statement??

ostinoh30-Mar-10 15:21

ostinoh

30-Mar-10 15:21

This is for a SQL 2005 database. I'm using SSMS.

Re: Help with T-SQL Statement??

Mycroft Holmes30-Mar-10 23:03

Mycroft Holmes

30-Mar-10 23:03

If what exists, the structure does not make sense, you are selecting a row and then ask if something exists. Change the script to:

DECLARE
	@Count INT
	
SELECT @Count = COUNT(*) 
FROM sys.dm_db_index_physical_stats
(DB_ID(N'Backdrop'), OBJECT_ID(N'DBO.table'), NULL, NULL , 'DETAILED')
WHERE [avg_fragmentation_in_percent] < 30

IF @Count > 0

Never underestimate the power of human stupidity
RAH

Dynamic stored procedure returns error and can't find culprit

DeepToot30-Mar-10 3:52

DeepToot

30-Mar-10 3:52

I have a stored procedure that I am doing dynamically and for some reason the error thrown is

Msg 102, Level 15, State 1, Line 4<br />
Incorrect syntax near '.'.<br />

The stored procedure is this:

ALTER PROCEDURE [dbo].[proc_AgencySearchDarrell] 	<br />
	@Mortgagee varchar(50) = NULL,<br />
	@ClosingDateBegin datetime = NULL,<br />
	@ClosingDateEnd datetime = NULL,<br />
	@MortgageId int = NULL,<br />
	@County varchar(50) = NULL	<br />
AS<br />
<br />
SET NOCOUNT ON<br />
<br />
DECLARE @SQL Nvarchar(4000)<br />
<br />
<br />
SELECT @SQL = 'SELECT DISTINCT M.MortgageId, <br />
				T1.Agent, <br />
				T1.Agency, <br />
				T3.InsCompanyUW,BS1.FirstName' + ' ' + 'BS1.LastName,<br />
				BS2.FirstName' + ' ' + 'BS2.LastName,<br />
				L1.BrokerageBus, <br />
				L2.Originator, <br />
				L3.Mortgagee,<br />
				RE1.Appraiser, <br />
				RE2.BuyersBroker, <br />
				RE3.BuyersSalesperson,<br />
				P1.StreetAddress, <br />
				P2.City,<br />
				CONVERT(varchar,M.TransClosingDate,101) AS TransClosingDate,<br />
				P3.County<br />
FROM Mortgage M LEFT OUTER JOIN TitleInfo T1<br />
		ON M.MortgageId = T1.MortgageId <br />
	LEFT OUTER JOIN TitleInfo T2<br />
		ON M.MortgageId = T2.MortgageId <br />
	LEFT OUTER JOIN TitleInfo T3<br />
		ON M.MortgageId = T3.MortgageId <br />
	LEFT OUTER JOIN BuyerSellerInfo BSI1 <br />
						ON M.MortgageId = BSI1.MortgageId<br />
						LEFT OUTER JOIN BuyerSeller BS1 ON BSI1.BuyerId = BS1.BuyerSellerId <br />
							<br />
	LEFT OUTER JOIN BuyerSellerInfo BSI2 <br />
						ON M.MortgageId = BSI2.MortgageId<br />
						LEFT OUTER JOIN BuyerSeller BS2 ON BSI2.SellerId = BS2.BuyerSellerId							<br />
	LEFT OUTER JOIN LoanInfo L1<br />
		ON M.MortgageId = L1.MortgageId <br />
	LEFT OUTER JOIN LoanInfo L2<br />
		ON M.MortgageId = L2.MortgageId <br />
	LEFT OUTER JOIN LoanInfo L3<br />
		ON M.MortgageId = L3.MortgageId <br />
	LEFT OUTER JOIN RealEstateInfo RE1<br />
		ON M.MortgageId = RE1.MortgageId <br />
	LEFT OUTER JOIN RealEstateInfo RE2<br />
		ON M.MortgageId = RE2.MortgageId <br />
	LEFT OUTER JOIN RealEstateInfo RE3<br />
		ON M.MortgageId = RE3.MortgageId <br />
	LEFT OUTER JOIN PropertyInfo P1<br />
		ON M.MortgageId = P1.MortgageId <br />
	LEFT OUTER JOIN PropertyInfo P2<br />
		ON M.MortgageId = P2.MortgageId <br />
	LEFT OUTER JOIN PropertyInfo P3<br />
		ON M.MortgageId = P3.MortgageId <br />
 WHERE 1=1 '<br />
<br><br />
	IF @MortgageId IS NOT NULL<br />
		SELECT @SQL = @SQL + ' AND (M.MortgageId = COALESCE(@MortgageId,M.MortgageId)) '<br />
<br><br />
	IF @Mortgagee IS NOT NULL        <br />
		SELECT @SQL = @SQL + ' AND (L3.Mortgagee = COALESCE(@Mortgagee,L3.Mortgagee)) '<br />
    <br><br />
	IF @County IS NOT NULL<br />
        SELECT @SQL = @SQL + ' AND (P3.County = COALESCE(@County,P3.County))'<br />
<br><br />
	 IF @ClosingDateBegin IS NOT NULL<br />
		SELECT @SQL = @SQL + ' AND (M.TransClosingDate BETWEEN @ClosingDateBegin AND @ClosingDateEnd)'<br />
	    <br />
<br><br />
<br><br />
Exec sp_executesql @SQL<br />
<br />

Could it be the concatenated name in my FROM clause?

Also note that when it wasn't in dynamic sql it worked fine. We were fighting with the WHERE clause and needed that to be dynamic. I just couldn't find a way to make the Statement (SELECT AND FROM) static and the WHERE dynamic and have it work.

Any suggestions?

Thanks

Re: Dynamic stored procedure returns error and can't find culprit

R. Giskard Reventlov30-Mar-10 4:09

R. Giskard Reventlov

30-Mar-10 4:09

Without attempting to run it myself if I get these errors with dynamic sql I always do a quick print @sql and see what I've got. Copy it into a new query window and run it. That should give you more of an idea.

me, me, me

"The dinosaurs became extinct because they didn't have a space program. And if we become extinct because we don't have a space program, it'll serve us right!"
Larry Niven

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.