Dump values to table from file name

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

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)

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)

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

see
http://www.nigelrivett.net/ImportTextFiles.html

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

Did you try my approach? I s there a problem with that?

KM

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?  

@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*

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' .

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' .

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

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 .

@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*