Link to home
Create AccountLog in
Avatar of kat50
kat50

asked on

Is there a way to have the last 8 digets of a file name populate a field using bulk insert?

Is there a way to have the last 8 digets of a file name populate a field when using bulk insert?  I have a text file (without headers) that has a date incorporated in the file name (the same date (8 characters) is in the first line of my file starting with position 24).  I would like to have this value passed to a column when I use bulk insert to populate my table.
Avatar of Emes
Emes
Flag of United States of America image

can you set a constraint and then turn it off when you do the load.
Avatar of kat50
kat50

ASKER

I cannot visualize how that would pull in the date information that I need.
Avatar of Ryan McCauley
Are you doing this bulk insert from the command line, or from a SQL script that's running in either SSMS or from SQLCMD?
Avatar of kat50

ASKER

sql script
BULK INSERT #tmp_Test
FROM 'C:\Documents and Settings\My\Desktop\Test_Import.txt'
WITH
(
ROWTERMINATOR = '\n'
 )
Can you please post an example that has the filename as you describe, with the date in it? Or is it just the last 8 characters?

Are you running this script to automatically pick up every file in a folder, or is it the situation where you give it a date, and it fetches and imports the file for that date?
can you save the file into the table then take the contents of the table and reformat it into what you wish into the new table.

This sounds like you can load the buld table into storage area then move it from there into your working datgabase into the proper format.  This is a normal process to move data into a database. first into a stage then into the working database.
Avatar of kat50

ASKER

the file name will be similar to MyFeed12112010.txtx
Avatar of kat50

ASKER

I am trying avoid having to edit my sql each week, so I can automate my process.  I can create a field within my table but would like to have a prompt for this information at the least
ASKER CERTIFIED SOLUTION
Avatar of Ryan McCauley
Ryan McCauley
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account