Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Importing Poorly Formatted Text File Expand / Collapse
Author
Message
Posted Monday, July 14, 2014 9:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:02 PM
Points: 9, Visits: 207
I am trying to import a text file to a SQL server table using SISS. I use SSIS but this is my first time in import this type. Please HELP!
---------------text file--------------
REQ que stn all

STN
DN 326 0000
LOC GHUT ILCE 01 2 12 01
OPT CFW 1MR DGT RTP 0 ALTO NACT AWS NACT !NPA CFB CFD ENQC
CFAN

STN
DN 326 0001
LOC GHUT ILCE 01 2 12 02
OPT SUS CFW 1MR CWT DGT RTP 0 ALTO 3261281 ACT AWS NACT !NPA !ECO !RIT !124 CFB
CFD VM ENQC

STN
DN 326 0003
LOC GHUT ILCE 03 2 16 14
OPT 1MR DGT RTP 0 !TKD CFB CFD

STN
DN 326 0004
LOC GHUT ILCE 03 2 19 20
OPT 1MR DGT RTP 0 !LOC CFB CFD
-------------------------------------------------------------
1. The header "REQ que stn all" is to be disregarded
2. STN disregarded meaning not be included in the destination table
3. DN disregarded
4. 7 digits number after DN eg, 326 0000 is the phone number, first column
5. LOC disregarded
6. GHUT i.e. the next after LOC is the second column and that the Station Code
7. The set of codes after GHUT, eg 'ILCE 01 2 12 01' is one column and that is the Port.
8. OPT is disregarded.
9. the Codes after OPT eg, 'CFW 1MR DGT RTP 0 ALTO NACT AWS NACT !NPA CFB CFD ENQC
CFAN' to be treated as individual separate columns.

Thanks,
Post #1592399
Posted Tuesday, July 15, 2014 1:19 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:06 AM
Points: 1,778, Visits: 4,658
marg 14154 (7/14/2014)
I am trying to import a text file to a SQL server table using SISS. I use SSIS but this is my first time in import this type. Please HELP!
---------------text file--------------
REQ que stn all

STN
DN 326 0000
LOC GHUT ILCE 01 2 12 01
OPT CFW 1MR DGT RTP 0 ALTO NACT AWS NACT !NPA CFB CFD ENQC
CFAN

STN
DN 326 0001
LOC GHUT ILCE 01 2 12 02
OPT SUS CFW 1MR CWT DGT RTP 0 ALTO 3261281 ACT AWS NACT !NPA !ECO !RIT !124 CFB
CFD VM ENQC

STN
DN 326 0003
LOC GHUT ILCE 03 2 16 14
OPT 1MR DGT RTP 0 !TKD CFB CFD

STN
DN 326 0004
LOC GHUT ILCE 03 2 19 20
OPT 1MR DGT RTP 0 !LOC CFB CFD
-------------------------------------------------------------
1. The header "REQ que stn all" is to be disregarded
2. STN disregarded meaning not be included in the destination table
3. DN disregarded
4. 7 digits number after DN eg, 326 0000 is the phone number, first column
5. LOC disregarded
6. GHUT i.e. the next after LOC is the second column and that the Station Code
7. The set of codes after GHUT, eg 'ILCE 01 2 12 01' is one column and that is the Port.
8. OPT is disregarded.
9. the Codes after OPT eg, 'CFW 1MR DGT RTP 0 ALTO NACT AWS NACT !NPA CFB CFD ENQC
CFAN' to be treated as individual separate columns.

Thanks,


I don't think the file is poorly formatted, it looks consistent, free of funny characters and relatively human readable.

First thought is to import the file, line by line, into a staging table and then use DelimitedSplit8K to parse it, straight forward and quite fast.
Post #1592427
Posted Tuesday, July 15, 2014 1:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:57 AM
Points: 13,636, Visits: 10,519
Indeed, the file is formatted quite well in the sense that it is very predictable.
How I would do it:

* read the file in line by line as one giant column (choose a delimiter that will never be present in the file, for example |$&|)
* use a conditional split to get rid of the lines you don't need (empty lines and header)
* implement a script transformation to parse the remaining lines. You can use the first three characters of a line to decide what to do.
* output all the columns combined into one single row. You can use STN to find out when a new row starts.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1592430
Posted Tuesday, July 15, 2014 2:10 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 2:02 AM
Points: 947, Visits: 887
Koen Verbeeck (7/15/2014)
Indeed, the file is formatted quite well in the sense that it is very predictable.
How I would do it:

* read the file in line by line as one giant column (choose a delimiter that will never be present in the file, for example |$&|)
* use a conditional split to get rid of the lines you don't need (empty lines and header)
* implement a script transformation to parse the remaining lines. You can use the first three characters of a line to decide what to do.
* output all the columns combined into one single row. You can use STN to find out when a new row starts.


Hi Koen, how to read line by line ?
I did this outside SQL, I am sure it can be done on SQL too, I am just curious about a hint.

thanks
Post #1592442
Posted Tuesday, July 15, 2014 2:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:57 AM
Points: 13,636, Visits: 10,519
Iulian -207023 (7/15/2014)
Koen Verbeeck (7/15/2014)
Indeed, the file is formatted quite well in the sense that it is very predictable.
How I would do it:

* read the file in line by line as one giant column (choose a delimiter that will never be present in the file, for example |$&|)
* use a conditional split to get rid of the lines you don't need (empty lines and header)
* implement a script transformation to parse the remaining lines. You can use the first three characters of a line to decide what to do.
* output all the columns combined into one single row. You can use STN to find out when a new row starts.


Hi Koen, how to read line by line ?
I did this outside SQL, I am sure it can be done on SQL too, I am just curious about a hint.

thanks


You can find an example here:

Creating an Asynchronous Transformation with the Script Component




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1592449
Posted Tuesday, July 15, 2014 4:02 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 2:02 AM
Points: 947, Visits: 887
Koen Verbeeck (7/15/2014)
Iulian -207023 (7/15/2014)
Koen Verbeeck (7/15/2014)
Indeed, the file is formatted quite well in the sense that it is very predictable.
How I would do it:

* read the file in line by line as one giant column (choose a delimiter that will never be present in the file, for example |$&|)
* use a conditional split to get rid of the lines you don't need (empty lines and header)
* implement a script transformation to parse the remaining lines. You can use the first three characters of a line to decide what to do.
* output all the columns combined into one single row. You can use STN to find out when a new row starts.


Hi Koen, how to read line by line ?
I did this outside SQL, I am sure it can be done on SQL too, I am just curious about a hint.

thanks


You can find an example here:

Creating an Asynchronous Transformation with the Script Component


I got it, many thanks
Post #1592478
Posted Tuesday, July 15, 2014 6:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:02 PM
Points: 9, Visits: 207
I am afraid, I am a newbie in script component (with scripting in C# and VB). I read the example provided in the link but I am still finding difficulties in your point last 2 points:
* implement a script transformation to parse the remaining lines. You can use the first three characters of a line to decide what to do.
* output all the columns combined into one single row. You can use STN to find out when a new row starts

#FeelingRatherDumb
Post #1592869
Posted Wednesday, July 16, 2014 1:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:57 AM
Points: 13,636, Visits: 10,519
1. You read the line and store it inside a string.
2. You read the first 3 characters and depending on the result, you decide what to do.
- if it's STN, you know a new row has stared
- if it's DN, LOC or OPT, you need to parse the line.
You can use the .NET split function for that.
You need to use probably the space as a delimiter.
- The result of the split function is an array of strings. Loop over this array and write each occurence to a variable.
Throw away DN, LOC or OPT in the process.
- Read the next line. If it is STN, you know you have processed a full row and you can write it to the output.
3. Repeat step 2 until all lines have been processed.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1592935
Posted Wednesday, July 16, 2014 11:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:02 PM
Points: 9, Visits: 207
Koen Verbeeck (7/16/2014)
1. You read the line and store it inside a string.
2. You read the first 3 characters and depending on the result, you decide what to do.
- if it's STN, you know a new row has stared
- if it's DN, LOC or OPT, you need to parse the line.
You can use the .NET split function for that.
You need to use probably the space as a delimiter.
- The result of the split function is an array of strings. Loop over this array and write each occurence to a variable.
Throw away DN, LOC or OPT in the process.
- Read the next line. If it is STN, you know you have processed a full row and you can write it to the output.
3. Repeat step 2 until all lines have been processed.


Thank you so much. You have helped a lot!
Just one more thing, there is some cases in which there are some values that should be on the same line as OPT but are the next row like for instance 'CFAN !TKD' e.g. below. Would be able to assist or provide a hint on how I could get those values onto the same row as, merge with those values with OPT?

Thank you.

Eg,
STN
DN 326 0000
LOC GHUT ILCE 01 2 12 01
OPT CFW 1MR DGT RTP 0 ALTO NACT AWS NACT !NPA CFB CFD ENQC
CFAN !TKD

The Result would be:
STN
DN 326 0000
LOC GHUT ILCE 01 2 12 01
OPT CFW 1MR DGT RTP 0 ALTO NACT AWS NACT !NPA CFB CFD ENQC CFAN !TKD
Post #1593385
Posted Wednesday, July 16, 2014 11:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 36,995, Visits: 31,517
@marg 14154,

Are the 7 digit values for DN record type unique for each file? I ask because I have an idea for this but need to know that to firm up the approach.

Also, is there a maximum number of different values (ie: columns) that can show up for the OPT record type?


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1593389
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse