Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Import data which is on seperate lines in txt file

Status
Not open for further replies.
easyit

easyit

Programmer
Aug 22, 2003
443
NL
Hi,

I've a text file containing data as follows:

FieldId(unique) FieldText FieldFileName
267261 text1 EAMABI_ARN.txt
267262 text2 EAMABI_ARN.txt
267263 text3 EAMABI_ARN.txt
267264 * EAMABI_ARN.txt
267265 text5 EAMABI_ASD.txt
267266 text6 EAMABI_ASD.txt
267267 * EAMABI_ASD.txt
Click to expand...

I need to get the fields of each line in a seperate field of a record. The records are separated by the '*'.
Key FieldText1 FieldText2 FieldText3 FieldFilename
23 text1 text2 text4 EAMABI_ARN.txt
24 text5 text6 EAMABI_ASD.txt
Click to expand...

Does anybody know if there is method to seperate the records and order the the way I want (beside a line by line import using VBA)? Currently I have the file imported in a table.

I was thinking of shifting the Id in separate queries which I can join again, but maybe there is a simpler solution?

EasyIT
 
Sort by date Sort by votes
Why do you want to de-normalize your data? Why not use VBA? That would be the way to go. I would leave the data as-is (except get rid of the asterix lines), and if you just need it displayed with one ID per row, write VBA to do that display.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at
 
Upvote 0 Downvote
...the data between asterixes is one record, and I need to do several reports on it (counting etc). Unfortunally I cannot change the output file (it is a SAP invoicing error log. I simplified the example, but here's an actual example (maybe copy-paste it into notepad...):

Code: 
--------------------------------------------------------------------------------------------------------------------
|Typ|Meldingstekst                                                                                       |LTxt|Det.|
--------------------------------------------------------------------------------------------------------------------
|   |Voor operand 06-WAD30 zijn geen operandwaarden gevonden                                             |    |    |
|   |Afrekening van installatie 6008089612 is afgebroken                                                 |    |    |
|   |*                                                                                                   |    |    |
|   |Contract 5006307035 is voor afrekening geblokkeerd, reden afrek.blokkering SW                       |    |    |
|   |Afrekening van installatie 6008090425 is afgebroken                                                 |    |    |
|   |*                                                                                                   |    |    |
|   |Contract 5006307056 is voor afrekening geblokkeerd, reden afrek.blokkering SW                       |    |    |
|   |Afrekening van installatie 6008090450 is afgebroken                                                 |    |    |
|   |*                                                                                                   |    |    |
|   |Contract 5006307070 is voor afrekening geblokkeerd, reden afrek.blokkering SW                       |    |    |
|   |Afrekening van installatie 6008090468 is afgebroken                                                 |    |    |
|   |*                                                                                                   |    |    |
|   |Aan installatie 6008090550 is op datum 12.01.2005 geen contract toegewezen                          |    |    |
|   |Afrekening van installatie 6008090550 is afgebroken                                                 |    |    |
|   |*                                                                                                   |    |    |
|   |Contract 5006333163 is voor afrekening geblokkeerd, reden afrek.blokkering SW                       |    |    |
|   |Afrekening van installatie 6008090872 is afgebroken                                                 |    |    |
|   |*                                                                                                   |    |    |
|   |Contract 5026270650 is voor afrekening geblokkeerd, reden afrek.blokkering SW                       |    |    |
|   |Afrekening van installatie 6008091611 is afgebroken                                                 |    |    |
|   |*                                                                                                   |    |    |
|   |Contract 5026270651 is voor afrekening geblokkeerd, reden afrek.blokkering SW                       |    |    |
|   |Afrekening van installatie 6008091620 is afgebroken                                                 |    |    |
|   |*                                                                                                   |    |    |
|   |Contract 5006323593 is voor afrekening geblokkeerd, reden afrek.blokkering 99                       |    |    |
|   |Afrekening van installatie 6008092743 is afgebroken                                                 |    |    |
|   |*                                                                                                   |    |    |
|   |Contract 5008282073 is voor afrekening geblokkeerd, reden afrek.blokkering SW                       |    |    |
|   |Afrekening van installatie 6008092836 is afgebroken                                                 |    |    |
|   |*                                                                                                   |    |    |
|   |Begin foutenlijst                                                                                   |    |    |
|   |Apparaat 22124000408159403 TW 001 datum 14.01.2005: opnameresultaat bij inhuizing ontbrekend/fout   |    |    |
|   |Einde foutenlijst                                                                                   |    |    |
|   |Afrekening van installatie 6008095727 is afgebroken                                                 |    |    |
|   |*                                                                                                   |    |    |
|   |Contract 5006282069 is voor afrekening geblokkeerd, reden afrek.blokkering SW                       |    |    |
|   |Afrekening van installatie 6008096957 is afgebroken                                                 |    |    |
|   |*                                                                                                   |    |    |
|   |Contract 5006282077 is voor afrekening geblokkeerd, reden afrek.blokkering SW                       |    |    |
|   |Afrekening van installatie 6008096966 is afgebroken                                                 |    |    |
|   |*                                                                                                   |    |    |
|   |Contract 5006337683 is voor afrekening geblokkeerd, reden afrek.blokkering SW                       |    |    |
|   |Afrekening van installatie 6008097039 is afgebroken                                                 |    |    |
|   |*                                                                                                   |    |    |
|   |Contract 5006337691 is voor afrekening geblokkeerd, reden afrek.blokkering SW                       |    |    |
|   |Afrekening van installatie 6008097047 is afgebroken                                                 |    |    |
|   |*                                                                                                   |    |    |
|   |Contract 5027323935 is voor afrekening geblokkeerd, reden afrek.blokkering SW                       |    |    |
|   |Afrekening van installatie 6008097144 is afgebroken                                                 |    |    |
|   |*                                                                                                   |    |    |
|   |Contract 5006340686 is voor afrekenin
[\code]


EasyIT
 
Upvote 0 Downvote
....oops not finished yet, but clicked the wrong button :)

After putting all the right records on a line, I still need to extract the contract numbers and installation numbers, after that I can do a count of occurrence and reason for being in the list (which is the first line of each record; the second is merely acknowledgment of the transaction termination).

EasyIT
 
Upvote 0 Downvote
can you send me the text file and I'll take a look? mitzimeat@hotmail.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

combo
  • Locked
  • Question
SQL data types in CREATE/ALTER TABLE queries 1
Replies
2
Views
2K
combo
combo
lameid
  • Locked
  • Question
SELECT FROM Table in Another Access File
Replies
2
Views
4K
lameid
lameid
breezett93
  • Locked
  • Question
Field is being rounded in the export process
Replies
17
Views
1K
strongm
strongm
C
  • Question
Run-time error '3075': Syntax error (missing operator) in query expression 'Birthday = and Birthmonth ='.
Replies
3
Views
248
OzFoxy
OzFoxy
BugZap13
  • Locked
  • Question
Need help creating a date from two fields to be used in where clause
Replies
5
Views
565
mikrom
mikrom

Part and Inventory Search

Sponsor

Back
Top