Importing TXT file to SQL.

Question

Post reply

Importing TXT file to SQL.

SQLRep

SSCrazy

Points: 2052
More actions
October 26, 2005 at 7:29 am

#110293

Hi,
I am trying to transfer data from text file to sql but not able to do it cuz it all spread around in text file. What can i do to transfer all data and then parse it!
Help!!
Thanks

Viewing 14 posts - 1 through 13 (of 13 total)

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

Santhosh Kumar Right there with Babe Points: 766 More actions · Answer 1

If that is not properly delimited by a delimiter its not possible to import data from a text file using sql server.

with smiles

santhosh

SQLRep SSCrazy Points: 2052 More actions · Answer 2

is there a way that i can import it in to one column and then parse it!!

RichB SSCrazy Eights Points: 9651 More actions · Answer 3

Unless it is fixed field format - or you insert each row into a (probably single column) staging table and subsequently parse out into your destination tables the values you need, if you feel more comfortable doing it this way around.

RichB SSCrazy Eights Points: 9651 More actions · Answer 4

yes, that is straightforward, as long as you have a row terminator (eg lfcr)

Kory Becker Hall of Fame Points: 3585 More actions · Answer 5

Do you have any control over the text file you are trying to load?

SQLRep SSCrazy Points: 2052 More actions · Answer 6

i have the text file but it too big to modify. I even try taking in to different table and then parse it.

thanks

SQLRep SSCrazy Points: 2052 More actions · Answer 7

SQLRep

SSCrazy

Points: 2052

October 26, 2005 at 7:43 am

#600147

what is IFCR how can i do that

thanks

SQLRep SSCrazy Points: 2052 More actions · Answer 8

i got that in to one column but how can i query. The info in the table is customer information and i need to query their address and ssn!!

Thanks

Kory Becker Hall of Fame Points: 3585 More actions · Answer 9

You will need to find constants that exist in every row. Can you post a few rows of data in greek format (no real ssn's or addresses)

SQLRep SSCrazy Points: 2052 More actions · Answer 10

here is the data from the table

000 000000000000000000 0000000 00 1004319 10/25/2005 02 1004319

102 100431900103200083 0000001 01 121 RUCKS DRIVE ANGLETON TX 77515 1004319 1000467

102 100431900103200083 0000002 02 C P LUCAS REYES III 450638841

102 100431900103200083 0000004 05 06 BRAZORIA 102 10043190010

102 100431900103270417 0000002 02 C P DAVID LEBAKKEN KEITH 392665458

RichB SSCrazy Eights Points: 9651 More actions · Answer 11

Interesting.

Your job sucks... :p

You need to examine the data more, if possible go back to source and get a data dictionary.

It looks like you have 5 or more tables rolled into one. I would guess if you extract the text and the 2nd column eg 100431900103200083 into a seperate table (based on the 01,02, 05 value) you may be able to get something.

But you really, really need to find the documentation for this - unless its a standard format someone knows about..?

swede SSCommitted Points: 1773 More actions · Answer 12

is this a fixed length file?

take a look at using dts to import your fixed length file.

homebrew01 SSC Guru Points: 55548 More actions · Answer 13

You could try importing the text file into MS Excel first. Then you can tell Excel where to create the columns to break up the data. Then, import the Excel file into SQL.