transfer data from a text file

Question

Post reply

transfer data from a text file

susheereddy

SSC-Addicted

Points: 445
More actions
May 3, 2008 at 11:54 pm

#206946

Hi all
I am pretty much new to the sql server, I have a problem.. Every day i will get around 10 text file from some other source and they are named as deposit followed by the date and time. I have to create job to transfer the data from the text files to one of the table in my server. This job should be run for every six hours. Once the job is done all the data from my text files, which i have got before the execution of job should be dump into my table. can anyone suggest me how to create a job ?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Though shalt not double post... go to your other post and answer the questions that I asked.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

susheereddy SSC-Addicted Points: 445 More actions · Answer 2

Sorry but some one told me the location where i have posted is not the right one.. so i just moved that to here.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Sorry... didn't know that...

Still, I posted some questions on that other thread because I need more info to help you. Any answers on those?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

susheereddy SSC-Addicted Points: 445 More actions · Answer 4

i don't know much about SMO and powershell... i lost my hopes ... if any one can gimme the complete script that would be more helpful to me

thanks

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Ok... I'll try a third and final time... I'm trying to help you do this using xp_CmdShell and Bulk Insert, as you requested. I asked you a couple of questions and asked you to provide me with a bit of data and a lost of the actual file names... I can't help you if you won't provide that information. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

susheereddy SSC-Addicted Points: 445 More actions · Answer 6

I have already told you that we have a format file so no need of bothering about format.. and the file name will be in this format [deposit]yearmonthtime.

thanks

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

susheereddy (5/6/2008)
and the file name will be in this format [deposit]yearmonthtime.

yearmonthtime... so no days in there. The year could be 2 or 4 digits. The month could be 1 or 2 digits, fixed or not, or it could be a 3 letter abbreviation or fully spelled out... I won't even go into all the different variations that time could be...

... not much help there. I needed that information to show you how to manage the files by date.

I recommend you Google xp_Dirtree with the 3 parameter format to help you get file names/extensions. The second and third parameter identify Level and whether to include file names or not. I recommend you set them both to "1" (1 Level and includes an indicator {IsFile} to differentiate between directory names and file names). You could also look into Books Online for how to setup a Text Linked Server to also provide a method of getting file names.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)