Extracting Data a Txt file

Question

Extracting Data a Txt file

AVB

SSChampion

Points: 11418
More actions
March 27, 2008 at 8:24 am

#71293

I'm looking to import a txt file and store each field as a column in a table. The file wiill have different columns for each row. The file would look something like this:
ISA*00*^
AK1*IM*275^
AK9*A*1*1*1^
SE*4*0001^
GE*1*272^
IEA*00001*000000272^
I was thinking of using the Bulk Insert with BCP FormatFile but I don't think that'll support rows with different amounts of columns. Is that correct?
I could write something using Charindex but it seems like that would be pretty lengthy.
Any suggestions?

Viewing 5 posts - 1 through 4 (of 4 total)

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

mrpolecat SSCertifiable Points: 7014 More actions · Answer 1

You can use sp_addlinkedserver with a schema.ini file. Check out BOL sp_addlinkedserver and example H. You may also have to research building a schema.ini file to deal with the "*" as a delimieter.

This is what it looks like for Tab delimeted

[FILENAME.TXT]

ColNameHeader = False

CharacterSet = ANSI

Format = TabDelimited

DateTimeFormat=m/d/yyyy hh:nn:ss

Col1=colname1 DateTime

Col2=colname2 Char Width 50

Col3=colname3 Char Width 10

mrpolecat SSCertifiable Points: 7014 More actions · Answer 2

take a look here for the schema.ini info

http://msdn2.microsoft.com/en-us/library/ms709353.aspx

You can use

Format=Delimited(*)

AVB SSChampion Points: 11418 More actions · Answer 3

Thanks for the links... I'll check em out.

The weird thing is the Rows are all terminated with the ^

Not sure how that'll afffect it. I'll read about it.

I never knew you could create format files for data import. Pretty cool.

mrpolecat SSCertifiable Points: 7014 More actions · Answer 4

You can create format files for bcp as well but I think it will still have issues with your varied number of fields.