Re-Arrange Data

[AC5FF]

Probably something simple here but I can't seem to get it figured out.

I've imported a huge text file and have pulled out the relivant data I need to store using a query. The data comes out like:
(date) ... 9/29/2017
(B)...1
(T)...0
(F)...18
(I)...101
(Z)...82

But I need to put this into a table; stored like:
(date)........(B)...(T)...(F)...(I)...(Z)
9/29/2017...1.....0.....18....101...82


Just can't wrap my head around making this happen. Anyone have an idea?

 

[plog]

Your starting data doesn't really make sense.

The data comes out like:
(date) ... 9/29/2017
(B)...1
(T)...0
(F)...18
(I)...101
(Z)...82

You said you've imported it like that, which means its in a table. Which means you have 2 columns of data.

How do you know which (date) record goes with which (Z) record?

 

[pbaldy]

If it's predictable, you can open the text file and read it line by line. You can use the AddNew method of a recordset. When you hit the date field, start a new record. At each succeeding line, add that field to the data. After (Z), finish the record with an Update.

 

[AC5FF]

Sorry for the delay getting back here. Was a crazy weekend and I wasn't able to work on this at all.

Plog: The data I am showing is after stripping it from the text file. The date itself goes with each record; like my second example. The data is error codes from nightly processing. I need to start storing this information for historical purposes. So in the future I can run a report that shows a history of (B)attery failures or (T)amper issues, etc.

pbaldy: It is not really "predictable" in the sense that the (B) is always line 25 of the text file; it is however set to column spacing and that's how I am importing the data - by using an import specification to set it up to only bring in columns 16-18, 50-53, and 65-68; then using a query to only pull out the data like I originially showed.

I've never used the AddNew method and will have to read up on it. If I am understanding things correctly that may work.

 

[plog]

I still don't understand. Is this a data manipulation issue (query) or a data importation issue. You say you have imported it, which leads me to believe that its in a table and thus a query issue. Please post some sample data from that table (include table and field names).

Then using the data in that table you post, show us how you want the data.

 

[AC5FF]

The data comes out like:
(date) ... 9/29/2017
(B)...1
(T)...0
(F)...18
(I)...101
(Z)...82

Phog; The issue is a data manipulation issue. Above is my table. Column headings are Exception and Data. The table itself is meaningless; in the fact that it is a temporary table that is overwritten every day with a new text file. I don't need to store all 500+ lines of the table; I just need to store what I pull from it.

Okay; so now that the data is available in a table as above I want to manipulate it and store it into a permanent table. like this:

(date)......(B)...(T)...(F)...(I)...(Z)
9/29/2017...1.....0.....18....101...82

Where the first line is the column headings and the 2nd line is the data. Then today when I run the macro or whatever it turns out to be it'll strip out today's data from the text file; and I will have this in my permanent table:

(date)......(B)...(T)...(F)...(I)...(Z)
9/29/2017...1.....0.....18....101...82
10/1/2017...2....4......12....97....62

Hope that helps to clear it up?

 

[AC5FF]

FYI
Here is the actual table data after I import it:
I know it looks like garbage; but row 210-218 contain the data I need to keep.

Field2	Field4	Field6
T O		
		
DAY		
AYS		
AY		
		
pro		
		
		
___		
___		
		
 PR		
___		
___		
		
		
		
---	0	
		
		
___		
___		
		
		
___		
___		
		
		
		
---		
c W		
Ver		
ly		
est		
Mar		
		
		
___		
___		
		
		
___		
___		
		
pro		
		
		
___		
___		
		
		
___		
___		
		
pro		
		
		
		
---		
c W		
ke'		
vil		
. 2		
rni		
ood		
Ver		
ton		
art		
5 W		
ly		
 Wo		
Hil		
al		
Pla		
r A		
ran		
 So		
 Ap		
 Hi		
en		
m (		
est		
oor		
Cou		
m V		
ion		
oor		
int		
en		
9 G		
Mar		
ok		
 Ap		
ven		
r S		
Par		
Par		
ng		
ng		
 We		
ng		
on		
		
		
___		
___		
		
		
___		
___		
		
pro	48	
		
		
		
---		
n T		
par		
ore		
ms		
ood		
 Sq		
ard		
9 W		
nde		
2 C		
d P		
l A		
Par		
 Ap		
te		
par		
ew		
oin		
 09		
afa		
Ter		
y A		
 Pa		
y H		
mpt		
err		
ts		
ent		
l/D		
is		
 St		
in		
The		
son		
Dim		
ank		
ule		
 St		
na/		
oll		
ors		
Hou		
ber		
ck		
oor		
Par		
m P		
Riv		
		
		
___		
___		
		
		
___		
___		
		
DCC		
COD		
---		0
738		
326		
195		
738		
864		
244		
371	15	
825		
464		
573		
300		
537		
333		
142		
531		
472		
608		
		
to:		
: T		
: T		
  t		
  c		
		
		
___		
___		
		
		
___		
___		
		
(B)	5	
(E)	85	
(F)	0	
(I)	37	
(R)	0	
(T)	12	
(Z)	80	
		
(I)		12
		
		
___		
___		
		
TER		
___		
___		
		
		
___		
		
eet		
		
DCC		
COD		
---		0
922		
		
___		
		
men		
		
DCC		
COD		
---		0
173		
452		
		
___		
		
		
		
DCC		
COD		
---		0
439		
		
___		
		
niu		
		
DCC		
COD		
---		0
598		
		
___		
		
men		
		
DCC		
COD		
---		0
923		
		
___		
		
 Ap		
		
DCC		
COD		
---		0
657		
		
___		
		
		
		
DCC		
COD		
---		0
337		
618		
654		
942		
819		
441		
186		
472		
88		
245		
148		
		
___		
		
		
		
DCC		
COD		
---		0
065		
		
___		
		
		
		
DCC		
COD		
---		0
665		
264		
174		
900		
523		
763		
		
___		
		
tie		
		
DCC		
COD		
---		0
030		
952		
		
___		
		
		
		
DCC		
COD		
---		0
237		
113		
		
___		
		
omi		
		
DCC		
COD		
---		0
244		
		
___		
		
men		
		
DCC		
COD		
---		0
231		
863		
		
___		
		
men		
		
DCC		
COD		
---		0
087		
062		
877		
		
___		
		
rac		
		
DCC		
COD		
---		0
525		
		
___		
		
) -		
		
DCC		
COD		
---		0
488		
984		
		
___		
		
 Tr		
		
DCC		
COD		
---		0
595		
375		
142		
851		
403		
169		
059		
689		
649		
988		
467		
645		
650		
621		
770		
		
___		
		
tme		
		
DCC		
COD		
---		0
322		
052		
549		
		
___		
		
The		
		
DCC		
COD		
---		0
29	15	
		
		
___		
___		
		
		
___		
___		
		
		
___		
		
 I		
		
DCC		
COD		
---		0
192		
945		
929		
		
___		
		
 II		
		
DCC		
COD		
---		0
324		
062		
901		
166		
331		
171		
		
___		
		
nts		
		
DCC		
COD		
---		0
294		
580		
967		
952		
693		
379		
		
___		
		
ts		
		
DCC		
COD		
---		0
929		
		
___		
		
III		
		
DCC		
COD		
---		0
105		
366		
325		
686		
382		
403		
959		
		
___		
		
 -		
		
DCC		
COD		
---		0
443		
31		
		
___		
		
- W		
		
DCC		
COD		
---		0
284		
		
___		
		
		
		
DCC		
COD		
---		0
780		
709		
		
___		
		
s -		
		
DCC		
COD		
---		0
211		
347		
004		
195		
287		
4		
224		
472		
833		
		
___		
		
ter		
		
DCC		
COD		
---		0
823		
093		
		
___		
		
 -		
		
DCC		
COD		
---		0
217		
895		
142		
333		
710		
405		
		
___		
		
men		
		
DCC		
COD		
---		0
741		
554		
		
___		
		
pts		
		
DCC		
COD		
---		0
713		
883		
352		
088		
385		
018		
401		
515		
852		
253		
		
___		
		
s -		
		
DCC		
COD		
---		0
780		
379		
679		
063		
067		
564		
393		
796		
		
___		
		
s -		
		
DCC		
COD		
---		0
897		
514		
515		
580		
		
___		
		
ate		
		
DCC		
COD		
---		0
		
186		
		
___		
		
The		
		
DCC		
COD		
---		0
053	15	
501	15	
785	15	
660	15	
550	15	
365	15	
		
___		
		
- W		
		
DCC		
COD		
---		0
264		
		
___		
		
 Wa		
		
DCC		
COD		
---		0
062		
863		
		
		
___		
___		
C18		0

 

[pbaldy]

For starters you wouldn't normally store data horizontally like that, it isn't normalized. That said, the loop should work. You can loop looking for "(B)" and then grab the value, etc.

 

[plog]

You are doing a hell of a job confusing me. Post #6 says after importation you have a table with 2 fields (Exception and Data). Post #7 says after importation you have a table with 3 fields (Field2 ,Field4, Field6). I have no idea what you have, perhaps pbaldy's solution will work for you.

 

[AC5FF]

plog
ignore field6; while it's needed for another issue; it's not relevant to this.

Pbaldy
i'm confused then. You say storing data horizontally isn't normalized? I thought that was normal... storing my data like this isn't normalized?

(date)......(B)...(T)...(F)...(I)...(Z)
9/29/2017...1.....0.....18....101...82
10/1/2017...2....4......12....97....62

How should it be normalized then? I'm seriously lost ...

 

[pbaldy]

Having different types of failures in fields is no different than having Product1, Product2, etc. The normalized way would be

Date........Type...Quantity
9/29/17.....B...........1
9/29/17.....F...........18

That said, rules are made to be broken. If you think the fields are static and this design works for you, go for it.

 

[AC5FF]

Pbaldy; I understand what you are saying now.
That would make importing into a table MUCH easier........

 

[arnelgp]

Is it
Possible to post the zipped txt file.

 

[AC5FF]

Sorry - no; can't do that.
More for what's contained in the text file than anything else.

 

[AC5FF]

Marking this thread as solved.

While I still haven't tried to essentially 'rotate' the data like I wanted, Pbaldy's suggestion for normalizing the final table made importing things a breeze. Since I'll still be able to pull historical information in this format I am going to go this route with the problem.

Thanks All!

 

[pbaldy]

Glad you got it working.