Help! Friday deadline for CHARINDEX problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help! Friday deadline for CHARINDEX problem

Author

Topic

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-03-03 : 16:10:16

Hi, I'm working with CHARINDEX to pull specific columns from a text file that is first read into a single column staging table (#test_staging, below), after which I wish to extract only columns 1,2 and 6.

This code was written by sqlteam folks, much thanks. I have modified for use with Bulk Insert. Despite repeated attempts I can’t figure out the proper CHARINDEX/SUBSTRING syntax to return the 6th column of data within the structure/syntax of this specific code example. The code below returns columns 1 and 2, while also checking for NULLs which is crucial since two null rows at the end of the text file were causing the original trouble with BULK INSERT. Deadline is Friday, would very much appreciate any help.

=============================================================

USE testdb
GO

CREATE TABLE #test_staging(Col1 varchar(8000))
GO

BULK INSERT #test_staging FROM '\\server\scripts\bulk_test.txt'
WITH (DATAFILETYPE = 'char', FIRSTROW=10, KEEPNULLS)
GO

SELECT * FROM #test_staging
GO

CREATE TABLE #test_power (
testid int,
SampleName varchar (100),
CallText varchar (100)

)
GO

INSERT INTO #test_power(testid, SampleName, CallText)

SELECT CASE WHEN CHARINDEX(CHAR(9),Col1)-1 > 0 THEN
LEFT(Col1,CHARINDEX(CHAR(9),Col1)-1) ELSE NULL
END AS testid

, CASE WHEN (CHARINDEX(CHAR(9),Col1,(CHARINDEX(CHAR(9),Col1)+1))-1)-((CHARINDEX(CHAR(9),Col1))) > 0 THEN
SUBSTRING(Col1
,(CHARINDEX(CHAR(9),Col1)+1)
,(CHARINDEX(CHAR(9),Col1,(CHARINDEX(CHAR(9),Col1)+1))-1)-((CHARINDEX(CHAR(9),Col1)))
)
ELSE NULL
END AS SampleName

/* need CHARINDEX/substring code to return column 6, which is CallText */

FROM #test_staging

WHERE SUBSTRING(Col1,1,4) <> 'test' AND Col1 IS NOT NULL
GO

SELECT * FROM #test_power
GO

DROP TABLE #test_power
DROP TABLE #test_staging

GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-03 : 16:16:44

Did you try Jay's solution? He provided a solution, you just have to make it work for your environment. Run his solution in Query Analyzer and see what needs to be modified to fit your needs.

Tara

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-03-03 : 16:32:28

Tara,

Yes, I should have mentioned, I did try it out but the col 1,2, and 6 results at the end deliver "mixes" of data values, i.e. some col1 values are in col2, some col2 values in col6.

My idea was to stick with Brett's original approach/syntax since he knew of the original issue with blank rows at end of text file.

thx.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-03 : 16:45:32

In order to help, we would need to see the data in \\server\scripts\bulk_test.txt. Not all of the data, but rather all of the data that represents the different mixes that you have.

Tara

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-03-03 : 16:53:53

here's the first row of data in the text file (actual data modified slightly). I need to extract bolded columns. thx.

1/C89/text junk/1.567/0.5968/Trial1/456/textjunk/textjunk/5869.9

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-03 : 16:56:18

steelkit,

I am not opposed to Bretts solution. I only provided an alternative to allow you to easily extract any column you choose given a 10 column row of data. But, blank rows are not the root issue with the solution as you can easily alter the insert into #stage table to prevent nulls by adding a WHERE clause. If you are experiencing off-set columns using my method it is a result of 2 potential issues:

1) Either one or more data rows has more or less than 10 columns and/or
2) One or more rows has more than one concurrent tab delimiting a field.

Both of these issues can be handled with code modifications.

Do you understand what the solution I posted it doing?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32772

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-03 : 16:56:45

Well Jay's solution will work for that row, right?. We need to see all of the different mixes of data that you will have.

Tara

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-03-03 : 17:26:22

Here are 10 rows with representative data possibilities, slashes are placeholders for actual delimiters. sorry for the mess:

1/BA1/entry here same 1.405/1.7449601/0.9499827/Number1/100/ShopManual/Unknown/2948.0393
2/BA2/entry here same 1.406/1.7282908/0.993669/Number2/100/ShopManual/Unknown/2837.6997
3/BA3/entry here same 1.407/1.7071426/0.9889237/Number3/100/ShopManual/Unknown/3030.478
4/BA4/entry here same 1.408/1.7773948/0.9684681/Number4/100/ShopManual/Unknown/3051.9348
5/BA5/entry here same 1.409/1.7020409/0.9650211/Number5/100/ShopManual/Unknown/3031.8945
6/BA6/entry here same 1.410/1.7183716/0.95735645/Number6/100/ShopManual/Unknown/3256.0017
7/BA7/entry here same 1.411/1.944667/1.0661993/Number7/100/ShopManual/Unknown/946.79785
8/BA8/entry here same 1.412/0.73822814/0.793366/UnknownData//ShopManual/Unknown/2898.603
9/BA9/entry here same 1.413/1.7752647/0.9761452/AFG/100/ShopManual/Unknown/3231.1985
10/BA10/entry here same 1.414/1.6473658/0.9714832/AFG/100/ShopManual/AFG/3243.8984

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-03-04 : 12:03:45

All,

thx for the assists. I think I'm going to re-trench on this, take it from the top, and just learn it from scratch. I'll figure it out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources