Solved

Dump values to table from file name

Posted on 2003-12-08
15
471 Views
Last Modified: 2011-10-03
I have one file with name  locator_TestID.txt which contains data with fixed length which has to be loaded into table.

I have one table with two columns

create table TestLoad
( Locate VARCHAR(50),
  TestID VARCHAR(50))

Say i have text File   " HYD_NAT1204.TXT".  HYD should be inserted into Locate column and NAT1204 should be inserted into TestID column of table TestLoad.

Thanks in advance.



0
Comment
Question by:king0452
  • 4
  • 4
  • 2
  • +4
15 Comments
 
LVL 2

Expert Comment

by:kmalhotra
ID: 9902374
If you know that the name of the file will always be in the same format. You can do it like this

Dim lstrFileName
'Fetch FileName
'In This case

lstrFileName = " HYD_NAT1204.TXT"
lstrTemp  = Split(lstrFileName, "_")
lstrLoc = lstrTemp [0]

lstrTempTestId =   lstrTemp[1]
lstrTestId = Replace(lstrTempTestId, ".txt", "")

After this you have the 2 parts of the filename into these 2 variabes lstrLoc and lstrTestID

'Now
lstrSQL = insert into TestLoad(locate, testid) values('" & lstrLoc & "','" & lstrTestId & "')"

run this query and you will be OK

Thanks
KM

0
 
LVL 39

Expert Comment

by:appari
ID: 9902515

try this

SELECT substring('HYD_NAT1204.TXT',1,CHARINDEX('_', 'HYD_NAT1204.TXT')-1),
substring('HYD_NAT1204.TXT',CHARINDEX('_', 'HYD_NAT1204.TXT')+1,CHARINDEX('.', 'HYD_NAT1204.TXT')-1)
0
 
LVL 39

Expert Comment

by:appari
ID: 9902531
try this one

SELECT substring('HYD_NAT1204.TXT',1,CHARINDEX('_', 'HYD_NAT1204.TXT')-1),
substring('HYD_NAT1204.TXT',CHARINDEX('_', 'HYD_NAT1204.TXT')+1,CHARINDEX('.', 'HYD_NAT1204.TXT')-CHARINDEX('_', 'HYD_NAT1204.TXT')-1)
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 9902619
Hi king0452,

Your question history shows that you have 14 open questions out of a total of 20.
Sounds like it's cleanup time !

Regards

Hilaire
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 9902634
0
 

Author Comment

by:king0452
ID: 9903927
The link below is similar.
http://www.nigelrivett.net/ImportTextFiles.html 

But i need the file name "HYD_NAT1204.TXT" of which the first three characters
"HYD" should be inserted into Column Locate and  and the next character after underscore "NAT1204" should be inserted into column TestID of table TestLoad



0
 
LVL 2

Expert Comment

by:kmalhotra
ID: 9904209
Did you try my approach? I s there a problem with that?

KM
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Expert Comment

by:arbert
ID: 9904233
kmalhotra, your approach looks like VB script.  Where is the question asker suppose to run this?  Did you want him to create a DTS package?  
0
 
LVL 19

Expert Comment

by:Dexstar
ID: 9905613
@king0452:

> Say i have text File   " HYD_NAT1204.TXT".  HYD should be inserted into Locate
> column and NAT1204 should be inserted into TestID column of table TestLoad.

So you just want to insert the FILENAME into the table, and NOT the data in the file?  Then I would use this SQL code:

      DECLARE @strFileName VARCHAR(50)
      
      SET @strFileName = 'HYD_NAT1204.TXT'
      
      INSERT INTO TestLoad
      SELECT
            SUBSTRING(FileName, 1, UnderscorePos - 1),
            SUBSTRING(FileName, UnderscorePos + 1, DotPos - UnderscorePos - 1)
      FROM (SELECT @strFileName As FileName,
            CHARINDEX('_', @strFileName) As UnderscorePos,
            CHARINDEX('.', @strFileName) As DotPos) AS Data

You could wrap that up into a stored procedure, and make it really easy to work with.

Hope That Helps,
Dex*
0
 

Author Comment

by:king0452
ID: 9910433
Thanks Dexsta,it helps me a lot.

I have the file 'HYD_NAT1204.TXT' in the directory 'C:\DIRX\' . How to map to the directory and the file through script and use the file name, instead of directly assigning to string as
SET @strFileName = 'HYD_NAT1204.TXT' .



0
 

Author Comment

by:king0452
ID: 9910436
Thanks Dexsta,it helps me a lot.

I have the file 'HYD_NAT1204.TXT' in the directory 'C:\DIRX\' . How to map to the directory and the file through script and use the file name, instead of directly assigning to string as
SET @strFileName = 'HYD_NAT1204.TXT' .



0
 
LVL 19

Accepted Solution

by:
Dexstar earned 50 total points
ID: 9913573
Heh?  Wouldn't that make 2 questions?  :P

No, seriously...  Do you want to list ALL the files in that directory?  If not, which files do you want?  If so, it would look like this:

-- Create a temporary table containing all the file names
CREATE TABLE #dir (dirLine VARCHAR(255))
INSERT INTO #dir
EXEC master..xp_cmdshell 'dir /b C:\DIRX\'

-- Process each file name and insert into table
INSERT INTO
      TestLoad
SELECT
      SUBSTRING(FileName, 1, UnderscorePos - 1),
      SUBSTRING(FileName, UnderscorePos + 1, DotPos - UnderscorePos - 1)
FROM (SELECT dirLine As FileName,
      CHARINDEX('_', dirLine + '_') As UnderscorePos,
      CHARINDEX('.', dirLine + '.') As DotPos FROM #dir) AS Data
WHERE
      DotPos > UnderscorePos

-- Remove the Temporary Table
DROP TABLE #dir

-- Display the records
SELECT * FROM TestLoad

How's that?
Dex*
0
 
LVL 19

Expert Comment

by:Dexstar
ID: 9913580
(I stole some of that code from one of ScottPletcher's posts in a different question...   But it sure is slick!)
0
 

Author Comment

by:king0452
ID: 9926310
This is urgent for me.

Thanks Dexstar. I have one more file with name "'HYD_NAT1204_RAJU_RAJU_20031211.txt"
three more columns were added to table

create table TestLoad
( Locate VARCHAR(50),
  TestID VARCHAR(50)
   Username varchar(20),
   password varchar(30),
   testdate datetime
)


Yours code gives the values for  locate and testId. How to populate the same for the other columns  with username as raju, password as raju and testdate as 20031211.

Thanks in advance for your great support .
0
 
LVL 19

Expert Comment

by:Dexstar
ID: 9929464
@king0452 :  I don't mean any disrespect, and I really want to help you out, but the rules are pretty clear about asking more than one question at a time:  http:/help.jsp#hi107

Post another question, and include a link to this question, and I'm sure you'll get an answer really quickly, even if it doesn't come from me.

Dex*
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
T-SQL: Subtracting Amounts from "Among Rows" 3 41
Counting connections to SQL Server through C# 3 24
datetime in sql 6 23
Query / Window function ? 3 13
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now