How to Do...? 2

[SQLBaby]

i submit this question in VB(MS Databse) forum too.

I have data in txt file (al most 300 +/- rows)like this

Acct:001000,IMP,180 A: $23656.00 D: $56458.00
Acct:001100,EXP,403 B: $59555.00 E: $165879.00 G: $45565.00

I need to put these information in file like

001000,IMP,180,23656.00,,,56458,,,
001100,EXP,403,,59555.00,,,165879.00

Actualy every row have random information like first row only has 'A' and 'D' information while 2nd. roe has B,E and G. So if from A to E some thing missing it just put comma and space.

Also in every row there are random spaces between B: and $??? like B: 54300.00, i want to remove this space too. Could any body help me how to do that please ...?

Thanks in Advance.


Knowledge is a key of success.

 

[nigelrivett]

Being a sql server guy I would bcp it into a table then use a series of set based operations to update the data then bcp it out again.

Given this is a VB forum open the file - read through row by row, loop through the fields in the row and create a new row which you output to another file.

Which bit are you haveing problems with?

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[SQLBaby]

i nThanks for reply, At the end i need to save these informations in SQL table, but the problem is every row does not have information in sequence as i mentioned. if information are in sequence then i can import directly but i need to put ',' to seperate the coloum for import and also need to remove '$' sign and spaces.

Actualy i do not know how to do in VB row by row to remove unwanted characters and put , at the place where the filed is not exist. Hope you understand now.

Knowledge is a key of success.

 

[DrJavaJoe]

As strongm would probably say, it looks like a job for RegExp.

 

[nigelrivett]

see

http://www.nigelrivett.net/ImportTextFiles.html

I'm assuming you have a table like

create table a
(
x varchar(6) ,
y varchar(3) ,
z varchar(3) ,
A numeric(18,2) ,
B numeric(18,2) ,
C numeric(18,2) ,
D numeric(18,2) ,
E numeric(18,2) ,
F numeric(18,2) ,
G numeric(18,2)
)

You can do this and avoid any client side code.

create table ##Import (s varchar(1000))
bulk insert ##Import from 'c:\a.txt' with (FIELDTERMINATOR='|',ROWTERMINATOR = '\n'

insert a
(x,y,z,A,B,C,D,E,F,G)
select x = substring(s,6,6) ,
y = substring(s,13,3) ,
z = substring(s,17,3) ,
A = case when charindex('A:',s) = 0 then convert(numeric(18,2),0) else replace(replace(substring(s,charindex('A:',s)+2,patindex('%[^. 0-9$]%',right(s,len(s) - charindex('A:',s)-1)+'x')-1),'$',''),' ','') end ,
B = case when charindex('B:',s) = 0 then convert(numeric(18,2),0) else replace(replace(substring(s,charindex('B:',s)+2,patindex('%[^. 0-9$]%',right(s,len(s) - charindex('B:',s)-1)+'x')-1),'$',''),' ','') end ,
C = case when charindex('C:',s) = 0 then convert(numeric(18,2),0) else replace(replace(substring(s,charindex('C:',s)+2,patindex('%[^. 0-9$]%',right(s,len(s) - charindex('C:',s)-1)+'x')-1),'$',''),' ','') end ,
D = case when charindex('D:',s) = 0 then convert(numeric(18,2),0) else replace(replace(substring(s,charindex('D:',s)+2,patindex('%[^. 0-9$]%',right(s,len(s) - charindex('D:',s)-1)+'x')-1),'$',''),' ','') end ,
E = case when charindex('E:',s) = 0 then convert(numeric(18,2),0) else replace(replace(substring(s,charindex('E:',s)+2,patindex('%[^. 0-9$]%',right(s,len(s) - charindex('E:',s)-1)+'x')-1),'$',''),' ','') end ,
F = case when charindex('F:',s) = 0 then convert(numeric(18,2),0) else replace(replace(substring(s,charindex('F:',s)+2,patindex('%[^. 0-9$]%',right(s,len(s) - charindex('F:',s)-1)+'x')-1),'$',''),' ','') end ,
G = case when charindex('G:',s) = 0 then convert(numeric(18,2),0) else replace(replace(substring(s,charindex('G:',s)+2,patindex('%[^. 0-9$]%',right(s,len(s) - charindex('G:',s)-1)+'x')-1),'$',''),' ','') end
from ##Import

drop table ##Import

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[SQLBaby]

Thanks for nice reply, i am trying to run this code but i am gtting error which is

Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'insert'.


Knowledge is a key of success.

 

[nigelrivett]

missing close bracket

bulk insert ##Import from 'c:\a.txt' with (FIELDTERMINATOR='|',ROWTERMINATOR = '\n')

Sorry - didn't try it.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[SQLBaby]

Thanks for quick reply now the error is

(238 row(s) affected)

Server: Msg 8114, Level 16, State 5, Line 4
Error converting data type varchar to numeric.



Knowledge is a key of success.

 

[nigelrivett]

Check the data you are importing.
Run the remove the insert so that it is executing a select
and change
convert(numeric(18,2),0) else replace(replace(substring(s,charindex('A:',s)+2,patindex('%[^. 0-9$]%',right(s,len(s) - charindex('A:',s)-1)+'x')-1),'$',''),' ','') end

to

convert(varchar(20),'0') else replace(replace(substring(s,charindex('A:',s)+2,patindex('%[^. 0-9$]%',right(s,len(s) - charindex('A:',s)-1)+'x')-1),'$',''),' ','') end

and you can look at the data - there is probably something in the file which is of the wrong format (or at least not the same as the definition you have given)

Another option is to split the file into parts until you find the row that is causing the problem

Another option is to but an identity on ##Import so that you can loop through row by row until you find the problem.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

I've tried it wit hthis and it seems to work

Acct:001000,IMP,180 A: $23656.00 D: $56458.00
Acct:001100,EXP,403 B: $59555.00 E: $165879.00 G: $45565.00
Acct:001100,EXP,403 B:$59555.00 C: $165879.00 G: $45565.00
Acct:001100,EXP,403 A: $59555.00 B: $59555.00 C: $59555.00 D: $59555.00 E: $59555.00 F: $59555.00 G: $59555.00
Acct:001100,EXP,403


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[SQLBaby]

ok, still have same problem, the data is like, i changed A,B,C... as REG, OVERTIME...etc etc

Acct:001000,EXP,114 REG: $44000.00 OVERTIME: $16500.00
Acct:001000,EXP,131 REG: $81000.00 OVERTIME: $42403.12
Acct:001000,EXP,180 REG: $407625.00 OVERTIME: $245325.00 SALREG: $60000.00 HOLIDAY: $10400.00
Acct:001000,EXP,272 REG: $174800.00 OVERTIME: $40012.50 SALREG: $64000.00
Acct:001000,EXP,403 REG: $307581.25 OVERTIME: $80606.25 SALREG: $252300.00
Acct:001000,EXP,695 REG: $421637.50 OVERTIME:$171131.25 PERSONAL: $24800.00 VACATION: $52000.00 OTHER: $37200.00
Acct:001000,EXP,784 SALREG: $52800.00 HOLIDAY: $13200.00
Acct:001000,EXP,PRO REG: $177825.00 OVERTIME: $34312.50
Acct:001000,IMP,020 REG: $662900.00 OVERTIME: $310087.50 SALREG: $175040.00
Acct:001000,IMP,114 REG: $92000.00 OVERTIME: $14475.00
Acct:001000,IMP,131 REG: $29000.00
Acct:001000,IMP,180 REG: $227862.50 OVERTIME: $49228.12 SALREG: $101531.25
Acct:001000,IMP,272 REG: $219200.00 OVERTIME: $27000.00 SALREG: $135000.00
Acct:001000,IMP,403 REG: $465000.00 OVERTIME: $26775.00 SICK: $16000.00 VACATION: $55000.00 SALREG: $163500.00
Acct:001000,IMP,695 REG: $147425.00 OVERTIME: $19800.00
Acct:001000,IMP,784 REG: $119581.25 OVERTIME: $30300.00
Acct:001000,IMP,PRO REG: $646181.25 OVERTIME: $459468.73 SALREG: $367450.00
Acct:001000,MNT,MNT REG: $349750.00 OVERTIME: $60131.25
Acct:001000,RFS,317 REG: $122000.00 OVERTIME: $44043.75
Acct:001005,EXP,PRO SALREG: $249062.50

Acct:001020,RFS,317 REG: $57300.00 OVERTIME: $3900.00 SALREG: $84000.00


Original TXT file is look like following, i ran querry in VB which convert the shape of TXT file as above. Still your SQL query will qork, i need to run VB query always first to remove junk info from following original TXT file.


Wages Summary Report Page 0001
Previous pay period 09/22/2003 4:26p
All accounts, all pay rules, all Timeclock groups


Summary Totals:
Acct:001000,EXP,114 REG: $44000.00 OVERTIME: $16500.00

Acct:001000,EXP,131 REG: $81000.00 OVERTIME: $42403.12

Acct:001000,EXP,180 REG: $407625.00 OVERTIME: $245325.00 SALREG: $60000.00 HOLIDAY: $10400.00

Acct:001000,EXP,272 REG: $174800.00 OVERTIME: $40012.50 SALREG: $64000.00

Acct:001000,EXP,403 REG: $307581.25 OVERTIME: $80606.25 SALREG: $252300.00

Acct:001000,EXP,695 REG: $421637.50 OVERTIME:$171131.25 PERSONAL: $24800.00 VACATION: $52000.00 OTHER: $37200.00

Acct:001000,EXP,784 SALREG: $52800.00 HOLIDAY: $13200.00

Acct:001025,RFS,317 Wages Summary Report Page 0002
Previous pay period 09/22/2003 4:26p
All accounts, all pay rules, all Timeclock groups



Acct:001110,ATS,ATS SALREG: $230772.00

Acct:001110,CLT,CLT REG: $39000.00

Acct:001110,EXP,053 REG: $51375.00 PERSONAL: $12000.00

Acct:001110,EXP,080 SICK: $13200.00 SALREG: $39600.00 HOLIDAY: $13200.00



Acct:001115,EXP,272 SALREG: $70000.00


Acct:001115,EXP,PRO wages Summary Report Page 0003
Previous pay period 09/22/2003 4:26p
All accounts, all pay rules, all Timeclock groups

SALREG: $120000.00


Acct:001115,IMP,695 SALREG: $105024.00

Acct:001115,PRO,403 SALREG: $281280.00
Acct:008120,RFS,317 REG: $129562.50 OVERTIME: $15750.00


Grand Totals: REG: $14192921.25 OVERTIME:
$3740360.21 SICK: $89600.00 PERSONAL: $82400.00
VACATION: $295000.00 OTHER: $39400.00
SALREG: $5057653.75 HOLIDAY: $63600.00


-------------------------------------------------------------------------------

Number of Records : 669

-------------------------------------------------------------------------------


Knowledge is a key of success.

 

[nigelrivett]

/*
create table a
(
x varchar(6) ,
y varchar(3) ,
z varchar(3) ,
A numeric(18,2) ,
B numeric(18,2) ,
C numeric(18,2) ,
D numeric(18,2) ,
E numeric(18,2) ,
F numeric(18,2) ,
G numeric(18,2)
)
*/

declare @A varchar(20) ,
@B varchar(20) ,
@C varchar(20) ,
@D varchar(20) ,
@E varchar(20) ,
@F varchar(20) ,
@G varchar(20)

select @A = 'REG:' ,
@B = 'OVERTIME:' ,
@C = 'SALREG:' ,
@D = 'HOLIDAY:' ,
@E = 'PERSONAL:' ,
@F = 'VACATION:' ,
@G = 'OTHER:'


truncate table a

create table ##Import (s varchar(1000))
bulk insert ##Import from 'c:\a.txt' with (FIELDTERMINATOR='|',ROWTERMINATOR = '\n')


insert a
(x,y,z,A,B,C,D,E,F,G)
select x = substring(s,6,6) ,
y = substring(s,13,3) ,
z = substring(s,17,3) ,
A = case when charindex(@A,s) = 0 then convert(numeric(18,2),0) else replace(replace(substring(s,charindex(@A,s)+len(@A),patindex('%[^. 0-9$]%',right(s,len(s) - charindex(@A,s)-len(@A))+'x')-1),'$',''),' ','') end ,
B = case when charindex(@B,s) = 0 then convert(numeric(18,2),0) else replace(replace(substring(s,charindex(@B,s)+len(@B),patindex('%[^. 0-9$]%',right(s,len(s) - charindex(@B,s)-len(@B))+'x')-1),'$',''),' ','') end ,
C = case when charindex(@C,s) = 0 then convert(numeric(18,2),0) else replace(replace(substring(s,charindex(@C,s)+len(@C),patindex('%[^. 0-9$]%',right(s,len(s) - charindex(@C,s)-len(@C))+'x')-1),'$',''),' ','') end ,
D = case when charindex(@D,s) = 0 then convert(numeric(18,2),0) else replace(replace(substring(s,charindex(@D,s)+len(@D),patindex('%[^. 0-9$]%',right(s,len(s) - charindex(@D,s)-len(@D))+'x')-1),'$',''),' ','') end ,
E = case when charindex(@E,s) = 0 then convert(numeric(18,2),0) else replace(replace(substring(s,charindex(@E,s)+len(@E),patindex('%[^. 0-9$]%',right(s,len(s) - charindex(@E,s)-len(@E))+'x')-1),'$',''),' ','') end ,
F = case when charindex(@F,s) = 0 then convert(numeric(18,2),0) else replace(replace(substring(s,charindex(@F,s)+len(@F),patindex('%[^. 0-9$]%',right(s,len(s) - charindex(@F,s)-len(@F))+'x')-1),'$',''),' ','') end ,
G = case when charindex(@G,s) = 0 then convert(numeric(18,2),0) else replace(replace(substring(s,charindex(@G,s)+len(@G),patindex('%[^. 0-9$]%',right(s,len(s) - charindex(@G,s)-len(@G))+'x')-1),'$',''),' ','') end
from ##Import



select * from a
drop table ##Import


I'll do one to work on the original file too.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

include this after the bulk insert and you should be able to do without the VB manipulation

bulk insert ##Import from 'c:\a.txt' with (FIELDTERMINATOR='|',ROWTERMINATOR = '\n')

delete ##Import where ltrim(s) not like 'Acct:%' or s is null or s like '%Page%'
update ##Import set s = ltrim(s)


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[SQLBaby]

i realy appreciate for your kind help for all of this.

well, again one problem, in table 'a' it just have first 4 colums information like Acct:001000,EXP,180 nothing else, if i opened ##import table befor i drop it it shows all information but .00 instead of 58265.00 i mean all records showing only .00

Also i don't need the word Acct:

Knowledge is a key of success.

 

[nigelrivett]

Check the text file.
Acct:001000,EXP,114 REG: $44000.00 OVERTIME: $16500.00

Acct:001000,EXP,131 REG: $81000.00 OVERTIME: $42403.12

Acct:001000,EXP,180 REG: $407625.00 OVERTIME: $245325.00 SALREG: $60000.00 HOLIDAY: $10400.00

The code I have given you will get rid of leadijng spaces and remove unwanted lines leaving
Acct:001000,EXP,114 REG: $44000.00 OVERTIME: $16500.00
Acct:001000,EXP,131 REG: $81000.00 OVERTIME: $42403.12
Acct:001000,EXP,180 REG: $407625.00 OVERTIME: $245325.00 SALREG: $60000.00 HOLIDAY: $10400.00

This is what you should see in ##Import after the deletes and update.

substring(s,6,6) should miss out the Acct: - if you are getting that there must be a lot of extra chars at the beginning of the rows.

If you are getting 0 for all the other fields it's either because it's not finding the values probably because
select @A = 'REG:' ,
@B = 'OVERTIME:' ,
@C = 'SALREG:' ,
@D = 'HOLIDAY:' ,
@E = 'PERSONAL:' ,
@F = 'VACATION:' ,
@G = 'OTHER:'

has the wrong values
or because there is some other character other than $ and spaces before the value.

When I put the data you have given into a text file and run the procedure I get

x y z A B C D E F G
------ ---- ---- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
001000 EXP 114 44000.00 16500.00 .00 .00 .00 .00 .00
001000 EXP 131 81000.00 42403.10 .00 .00 .00 .00 .00
001000 EXP 180 407625.00 245325.00 60000.00 10400.00 .00 .00 .00
001000 EXP 272 174800.00 40012.50 64000.00 .00 .00 .00 .00
001000 EXP 403 307581.25 80606.25 252300.00 .00 .00 .00 .00
001000 EXP 695 421637.50 171131.25 .00 .00 24800.00 52000.00 37200.00
001000 EXP 784 52800.00 .00 52800.00 13200.00 .00 .00 .00
001110 ATS ATS 230772.00 .00 230772.00 .00 .00 .00 .00
001110 CLT CLT 39000.00 .00 .00 .00 .00 .00 .00
001110 EXP 053 51375.00 .00 .00 .00 12000.00 .00 .00
001110 EXP 080 39600.00 .00 39600.00 13200.00 .00 .00 .00
001115 EXP 272 70000.00 .00 70000.00 .00 .00 .00 .00
001115 IMP 695 105024.00 .00 105024.00 .00 .00 .00 .00
001115 PRO 403 281280.00 .00 281280.00 .00 .00 .00 .00
008120 RFS 317 129562.50 15750.00 .00 .00 .00 .00 .00






======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[SQLBaby]

you are right, its my fault i did not mentioned correct. there are spaces before Acct: and also i checked care fully the text file has info like

Wages Summary Report Page 0001
Previous pay period 09/22/2003 4:26p
All accounts, all pay rules, all Timeclock groups


Summary Totals:
Acct:001000,EXP,114
REG: $44000.00 OVERTIME: $16500.00
Acct:001000,EXP,131
REG: $81000.00 OVERTIME: $42403.12
Acct:001000,EXP,180
REG: $407625.00 OVERTIME:
$245325.00 SALREG: $60000.00 HOLIDAY: $10400.00
Acct:001000,EXP,272
REG: $174800.00 OVERTIME: $40012.50
SALREG: $64000.00
Acct:001000,EXP,403
REG: $307581.25 OVERTIME: $80606.25
SALREG: $252300.00
Acct:001000,EXP,695
REG: $421637.50 OVERTIME:
$171131.25 PERSONAL: $24800.00 VACATION: $52000.00
OTHER: $37200.00
Acct:001000,EXP,784
SALREG: $52800.00 HOLIDAY: $13200.00
Acct:001000,EXP,PRO
REG: $177825.00 OVERTIME: $34312.50
Acct:001000,IMP,020
REG: $662900.00 OVERTIME:
$310087.50 SALREG: $175040.00
Acct:001000,IMP,114
REG: $92000.00 OVERTIME: $14475.00
Acct:001000,IMP,131
REG: $29000.00
Acct:001000,IMP,180
REG: $227862.50 OVERTIME: $49228.12
SALREG: $101531.25
Acct:001000,IMP,272
REG: $219200.00 OVERTIME: $27000.00
SALREG: $135000.00
Acct:001000,IMP,403
REG: $465000.00 OVERTIME: $26775.00
SICK: $16000.00 VACATION: $55000.00 SALREG:
$163500.00
Acct:001000,IMP,695
REG: $147425.00 OVERTIME: $19800.00
Acct:001000,IMP,784
REG: $119581.25 OVERTIME: $30300.00
Acct:001000,IMP,PRO
REG: $646181.25 OVERTIME:
$459468.73 SALREG: $367450.00
Acct:001000,MNT,MNT
REG: $349750.00 OVERTIME: $60131.25
Acct:001000,RFS,317
REG: $122000.00 OVERTIME: $44043.75
Acct:001005,EXP,PRO
SALREG: $249062.50
Acct:001020,RFS,317
REG: $57300.00 OVERTIME: $3900.00
Acct:001025,RFS,317
Wages Summary Report Page 0002
Previous pay period 09/22/2003 4:26p
All accounts, all pay rules, all Timeclock groups
SALREG: $84000.00
Acct:001110,ATS,ATS
SALREG: $230772.00
Acct:001110,CLT,CLT
REG: $39000.00
Acct:001110,EXP,053
REG: $51375.00 PERSONAL: $12000.00
Acct:001110,EXP,080
SICK: $13200.00 SALREG: $39600.00 HOLIDAY: $13200.00
Acct:001110,EXP,086
REG: $48000.00 OVERTIME: $8100.00
Acct:001110,EXP,114
SALREG: $72000.00
Acct:001110,EXP,117
REG: $206125.00 OVERTIME: $31865.62
SICK: $10000.00 HOLIDAY: $8000.00
Acct:001110,EXP,180
REG: $247125.00 OVERTIME: $16875.00
SALREG: $134000.00
Acct:001110,EXP,235
REG: $43750.00 SICK: $11200.00
Acct:001110,EXP,272
REG: $195475.00 OVERTIME: $46162.50
Acct:001110,EXP,403
REG: $391300.00 OVERTIME: $87431.25
SICK: $13600.00 SALREG: $129400.00
Acct:001110,EXP,695
REG: $52800.00 OVERTIME: $11962.50 HOLIDAY: $8800.00
Acct:001110,EXP,PRO
REG: $53400.00
Acct:001110,IMP,114
REG: $112000.00 OVERTIME: $20250.00
Acct:001110,IMP,117
REG: $36000.00 OVERTIME: $7762.50
Acct:001110,IMP,180
REG: $414100.00 OVERTIME: $30375.00
PERSONAL: $8800.00 SALREG: $259528.00
Acct:001110,IMP,272
REG: $9250.00 SALREG: $132750.00
Acct:001110,IMP,403
REG: $441700.00 OVERTIME:
$163612.50 SALREG: $101528.00
Acct:001110,IMP,695
REG: $375012.50 OVERTIME: $70762.50
SICK: $14400.00 PERSONAL: $10800.00 VACATION: $56000.00
SALREG: $74400.00
Acct:001110,IMP,PRO
REG: $491825.00 OVERTIME: $97028.25
VACATION: $45200.00 SALREG: $173000.00
HOLIDAY: $10000.00
Acct:001110,PRO,403
REG: $98000.00 OVERTIME: $30056.25
Acct:001110,RFS,403
REG: $18000.00
Acct:001115,EXP,272
SALREG: $70000.00
Acct:001115,EXP,PRO
Wages Summary Report Page 0003
Previous pay period 09/22/2003 4:26p
All accounts, all pay rules, all Timeclock groups
SALREG: $120000.00
Acct:001115,IMP,695
SALREG: $105024.00
Acct:001115,PRO,403
SALREG: $281280.00
Acct:001120,IMP,317
REG: $32700.00
Acct:001120,RFS,317
REG: $210000.00 OVERTIME: $92193.75
Acct:001120,RFS,403
REG: $104400.00 OVERTIME: $86625.00
Acct:001125,RFS,317
VACATION: $80000.00
Acct:001130,IMP,180
SALREG: $100000.00
Acct:002000,EXP,272
REG: $102637.50 OVERTIME: $5400.00
Acct:002000,EXP,PRO
REG: $492275.00 OVERTIME:
$155156.25
Acct:002000,IMP,080
REG: $120000.00 OVERTIME: $7125.00
Acct:002000,IMP,180
REG: $52000.00 OVERTIME: $33637.50
Acct:002000,IMP,272
REG: $52312.50 OVERTIME: $18112.50
Acct:002000,IMP,PRO
REG: $368100.00 OVERTIME:
$127462.50
Acct:002020,RFS,317
REG: $224000.00 OVERTIME: $64462.50
Acct:002110,EXP,045
REG: $77750.00 OVERTIME: $1500.00
Acct:002110,EXP,080
REG: $9000.00
Acct:002110,EXP,272
REG: $55300.00 SICK: $11200.00
Acct:002110,EXP,PRO
REG: $120612.50 OVERTIME: $1875.00
PERSONAL: $7600.00
Acct:002110,IMP,014
REG: $46000.00 OVERTIME: $8625.00
Acct:002110,IMP,042
REG: $42000.00 OVERTIME: $13781.25
Acct:002110,IMP,045
REG: $80000.00 OVERTIME: $17100.00
Acct:002110,IMP,080
REG: $63375.00 OVERTIME: $15000.00
Acct:002110,IMP,272
REG: $130000.00 OVERTIME: $99918.75
Acct:002110,IMP,PRO
REG: $557925.00 OVERTIME:
$101081.25 OTHER: $2200.00
Acct:002115,IMP,PRO
SALREG: $19224.00
Acct:002120,RFS,317
REG: $191000.00 OVERTIME: $76800.00
Wages Summary Report Page 0004
Previous pay period 09/22/2003 4:26p
All accounts, all pay rules, all Timeclock groups
Acct:002140,HRD,HRD
REG: $44000.00 OVERTIME: $22275.00
Acct:003000,EXP,086
REG: $54000.00 OVERTIME: $10125.00
Acct:003000,EXP,180
REG: $334000.00 OVERTIME:
$138384.37
Acct:003000,EXP,695
REG: $197797.50 OVERTIME: $25512.00
Acct:003000,EXP,PRO
REG: $48000.00 OVERTIME: $2700.00
Acct:003000,IMP,180
REG: $183562.50 OVERTIME: $1800.00
PERSONAL: $10400.00 SALREG: $320924.00
Acct:003000,IMP,695
REG: $337000.00 OVERTIME: $40781.25
SALREG: $52520.00
Acct:003000,IMP,PRO
REG: $287131.25 OVERTIME: $70575.00
Acct:003005,EXP,PRO
SALREG: $64000.00
Acct:003005,IMP,PRO
SALREG: $63200.00
Acct:003020,RFS,317
REG: $88500.00
Acct:003110,EXP,086
REG: $46000.00 OVERTIME: $5175.00 SALREG: $74000.00
Acct:003110,EXP,180
REG: $47643.75
Acct:003110,EXP,695
REG: $46000.00 OVERTIME: $6037.50
Acct:003110,EXP,PRO
REG: $27500.00
Acct:003110,IMP,180
REG: $426000.00 OVERTIME:
$109087.50 PERSONAL: $8000.00 SALREG:
$139320.00
Acct:003110,IMP,695
REG: $229600.00 OVERTIME: $40976.25
SALREG: $79000.00
Acct:003110,IMP,PRO
REG: $44000.00 OVERTIME: $1237.50
Acct:003110,PRO,180
REG: $62000.00
Acct:003111,EXP,180
REG: $54000.00
Acct:003115,EXP,086
SALREG: $71000.00
Acct:003115,EXP,PRO
SALREG: $82500.00
Acct:003115,IMP,180
SALREG: $92520.00
Acct:003115,IMP,PRO
SALREG: $90000.00
Acct:003120,IMP,695
REG: $40000.00 OVERTIME: $3000.00
Acct:003120,RFS,317
Wages Summary Report Page 0005
Previous pay period 09/22/2003 4:26p
All accounts, all pay rules, all Timeclock groups
REG: $116120.00 OVERTIME: $17100.00
Acct:003150,IMP,180
SALREG: $85000.00
Acct:003150,MGR,MGR
SALREG: $96480.00
Acct:008000,IMP,180
REG: $64837.50 OVERTIME: $6243.75 VACATION: $6800.00
Acct:008110,IMP,201
REG: $37050.00
Acct:008120,CLT,CLT
REG: $40322.50
Acct:008120,RFS,317
REG: $129562.50 OVERTIME: $15750.00

Grand Totals: REG: $14192921.25 OVERTIME:
$3740360.21 SICK: $89600.00 PERSONAL: $82400.00
VACATION: $295000.00 OTHER: $39400.00
SALREG: $5057653.75 HOLIDAY: $63600.00


-------------------------------------------------------------------------------

Number of Records : 669

-------------------------------------------------------------------------------




if you see 2-3 lines contain one record like

Acct:001000,EXP,695
REG: $421637.50 OVERTIME: $171131.25 PERSONAL: $24800.00 VACATION: $52000.00

and in txt file when the page break it look like this

Acct:003120,RFS,317
Wages Summary Report Page 0005
Previous pay period 09/22/2003 4:26p
All accounts, all pay rules, all Timeclock groups
REG: $116120.00 OVERTIME: $17100.00

i can email you the real txt file just send me test message at dosteeus@yahoo.com.

You are realy vey helpfull, Thanks for that all help you are giving me.

Knowledge is a key of success.

 

[nigelrivett]

What a mess.
If you send it to sqlnr@hotmail.com I'll have a look at it.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

Here's a VB app that will transform the file into something that will import. You can use the sql posted previously to import it.
Careful about the columns that you need - there is a SICK: missing.
Maybe it would be better to have these fields in another table as

id, type value

1, 'REG', 5.00
1, 'OVERTIME', 20.00

then columns could be added without affecting the table structure.
Sub UpdFile()
Dim infile As Integer
Dim outfile As Integer
Dim sbuf As String
Dim soutbuf As String

infile = FreeFile
Open "c:\wagesummary.txt" For Input As infile
outfile = FreeFile
Open "c:\wagesummaryout.txt" For Output As outfile
Do While Not EOF(infile)
GetLine infile, sbuf
If InStr(sbuf, &quot;Acct:&quot <> 0 Or EOF(infile) Then
If soutbuf <> &quot;&quot; Then
Print #outfile, soutbuf
End If
soutbuf = LTrim(sbuf)
Else
soutbuf = soutbuf & sbuf
End If
Loop
Close infile
Close outfile
End
End Sub
Sub GetLine(infile As Integer, sbuf As String)
sbuf = &quot;&quot;
Do While Not EOF(infile) _
And (RTrim(sbuf) = &quot;&quot; _
Or (InStr(sbuf, &quot;Acct:&quot = 0 _
And InStr(sbuf, &quot;$&quot = 0 _
) _
)

Line Input #infile, sbuf

Loop
End Sub


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[SQLBaby]

Thanks my friend, Its working now. I will check each entery one by one tomorow, But now i need to run 2-codes, one in VB and one in SQL. A little gift 'star' for you. Once again thanks.

Knowledge is a key of success.

 

[SQLBaby]

Thanks my friend, Its working now. I will check each entery one by one tomorow, But now i need to run 2-codes, one in VB and one in SQL. A little gift 'star' for you. Once again thanks.

Knowledge is a key of success.