Text file import

Question

Post reply

Text file import

Wecks

Right there with Babe

Points: 778
More actions
April 26, 2019 at 10:31 am

#3539990

Is there a script that scan take text that is not column delimited and transfer each line into a separate record and each line where it is separated by a number of spaces inserts into a separate column.
For example the format of the txt file is as below but there are hundreds of lines.
TS123 TS123456ght TB12 TBH 0000
AS234 AF23424322 RI SSDFF EDE 7898979879
Each row maybe slightly different in the amount of data in it but i just want a way to import it.
It maybe that i need to state the first lot of data on a line goes into the first column, the second into a second column etc.

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

I'd be tempted to import it into a single-column staging table and do the transformations from there.

John

Wecks Right there with Babe Points: 778 More actions · Answer 2

Thanks for your reply, TBH I am no SQL developer and the issue is pretty much how i would extract the data either from a Text file or after it has been imported into the SQL and give some structure.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

I'm not sure that a relational database is the place to store unstructured data like this. The reason I say that is that you don't know what data is going to be in what column, and so you can't create your table with anything but the loosest of data types (varchar(1000) for everything, maybe).

If you absolutely have to do it like that, and you're not experienced in T-SQL, then it's going to be a steep learning curve for you. It's too much to cover in a forum topic, really. You can use this technique to reduce the gap between each pair of elements to a single space. After that, you'd probably need dynamic SQL to do the actual inserts into your destination table.

John

Dinosaur SSC Enthusiast Points: 123 More actions · Answer 4

Unless the data is delimited with the same number of spaces with the same type of data in the same columns, its not the simplest of tasks. I agree with John Mitchell-245523, it’s probably too much to cover in a forum topic.

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

Wecks wrote:

Is there a script that scan take text that is not column delimited and transfer each line into a separate record and each line where it is separated by a number of spaces inserts into a separate column. For example the format of the txt file is as below but there are hundreds of lines. TS123 TS123456ght TB12 TBH 0000 AS234 AF23424322 RI SSDFF EDE 7898979879 Each row maybe slightly different in the amount of data in it but i just want a way to import it. It maybe that i need to state the first lot of data on a line goes into the first column, the second into a second column etc.

This may be a whole lot easier than most would think. Judging from the 2 examples that you have, this might be what is known as a "fixed field" file where each column (data missing or not) starts at a given character position on each and every line in the file and always has the same number of characters whether those characters be spaces or something else.

Is that what you have?

--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)

pietlinden SSC Guru Points: 63503 More actions · Answer 6

If these are EDI files, then you can't just import them. You have to parse those, because the different lines have different meanings/information. Do they have a consistent structure?