Text file problem

logic1

Guys I have a bit of an issue.

I collect a series of text files on a daily basis. About 48 in total.

The naming format on these files is "<date>_<version_num>_at*_asc.blob"

The contents are in the two column format:

---

referencenumber | ATI value

---

I need to put these into an SQL table but I need to include the date and version number from the file name as extra fields.

Anyone have any ideas?

.logic.

Rev Hellfire

yes loads.

Trojan

Originally posted by Rev Hellfire
yes loads.

Don't be cheeky, that's what PI is for.

Al.

Evil Phil

We'll need more information logic.

What OS is this on?
What DBMS is the table on?
Are you importing directly into the db or writing an application to do this for you?
If you're writing an application what language are you using?

logic1

Soz some more details

It's on Win2k Pro with perl installed (my knowledge of perl is severly limited)

I basically have folders full of these files on a daily basis and need to put them into a format where I can identify them in a database which basically involves putting the date and version name from the filename into the columns inside the text files.

.logic.

clearz

I can help you if you know a bit about the following:

Input Streams in java
JDBC
String manipulation in Java

If you know about this post a few example names and a bit of a file.
Are you sure every name is in the same format and every file is in the same format.

bonkey

Originally posted by logic1
I need to put these into an SQL table

What DB server? Many of them have flexible import applications. If its MSSQL 7.0 or newer, you're sorted...have a look at the DTS (Data Transformation Services)

jc

logic1

Just a follow up to this. The following code was kindly donated to me but I need it formatted properly for perl as I'm a perl idiot.

for file in `ls *_asc.blob`
do
date=`echo $fle `awk -F '{ print $1 }'`
ver=`echo $fle `awk -F '{ print $2 }'`
for line in `cat $file`
do
echo "$date $ver $line"
done
done

Again a quick synopsis of my goal is:

I collect a series of text files on a daily basis. About 48 in total
per day.

The naming format on these files is "<date>_<version_num>_AT*<1 to
10>_asc.blob"

The contents are in the two column format:

---

referencenumber | value
referencenumber | value
referencenumber | value
referencenumber | value
referencenumber | value

---

I need to alter these text files to include two new columns from the
filename itself.

So the file looks like: (Both date and version_num are taken from the
filename and put into the text file)

---

date | version_num | referencenumber | Value
date | version_num | referencenumber | Value
date | version_num | referencenumber | Value
date | version_num | referencenumber | Value
date | version_num | referencenumber | Value

---

So I'm basically asking someone to do it for me but I'll gladly pay with beer and or sexual favours at a later date. (Towel whipping in the locker rooms is also an option)

.logic.

DeadBankClerk

[php]
/*
* First run the command ls to list the directory.
* Then use the results one by one calling
* each one 'file' as if is used.
*/
for file in `ls *_asc.blob`
do
date=`echo $fle `awk -F '{ print $1 }'`
ver=`echo $fle `awk -F '{ print $2 }'`

/*
* For each line of the file do the following:
*/
for line in `cat $file`
do
echo "$date $ver $line"
done
done
[/php]

clearz

I can make you a little java app that does this if java is ok. Just post an accual name from one of the files instead of "<date>_<version_num>_AT*<1 to 10>_asc.blob"

What platform are you using. If it is windows I can use Visual Basic

logic1

Originally posted by clearz
I can make you a little java app that does this if java is ok. Just post an accual name from one of the files instead of "<date>_<version_num>_AT*<1 to 10>_asc.blob"

What platform are you using. If it is windows I can use Visual Basic

That would be magnificent It is windows I'm using so a VB app would be perfect.

The actual name of one of the files is:

1112_4114_10502_GB_ATI3.blob

Bascially broken down as follows:

Date = 11/12
Version = 4114
Sub version = 10502
Country code = GB
ATI number = ATI3

And basically the contents currently look like:

Referencenumber ATIdata

I want it to look like

Date Version Subversion CountryCode ATInumber Referencenumber ATIdata

Thanking you kindly.

.logic.

clearz

Here ya go.
Instructions:
Just select the folder that contains your files and click Go. It will create a folder within that folder called data which will contain the new files. I was not sure if you wanted a multi file output or singlefile output so I created two programs. Hope they are what you wanted. If you find any bugs of have any questions PM me.

Happy Christmas
John.

logic1

Hi John,

Program looks great and the efforts much appreciated but the uto file seems to be totally blank.

Not altogether sure why.

Here's some sample data from one of the Blob files it may give you some idea:


02#184986424 0=10 1=7 2=0 3=0 4= 5= 10=1B 11=F 12=20 18=40244640 20=E 22=C80 24=725 21=E 23=1F40 25=0 26=5460 27=A175 30=0 31=2 32=2 33=0 34=5460 35=9C40 40=1 41=80 42=0 43=0 44=1 45=800 50=2 51=2 52=2F0A 53=7B76 54=0 55=0 56=2B5E 57=BB 58=9 59=0 60=3F F1=0
02#184986599 00=09 02=00 03=00 10=04 11=0A 12=087F 14=00 15=00 17=0C 20=0C 21=0C 22=0AF0 23=0AF0 26=7080 27=11 32=04 33=1B 34=79E0 35=5DC0 40=01 41=01 43=04 44=01 42=08 56=1BD8 57=1F58 58=04 59=04 60=3C
02#185020478 0=10 1=7 2=0 3=0 4= 5= 10=18 11=F 12=26 18=40900600 20=E 22=C80 24=725 21=E 23=1F40 25=0 26=5460 27=B115 30=0 31=5 32=1 33=0 34=5460 35=9C40 40=1 41=80 42=0 43=99 44=2 45=800 50=2 51=0 52=354DCA 53=7D27A1 54=0 55=0 56=8EB3 57=108B9 58=A73 59=99 60=3F

the 02#18* is the reference number and the rest is the ATI data.

thanks

.logic.

clearz

Hi

The reason it doesent work is it is looking for a '|' symbol between the diferent fields as decribed in one of your examples above here is a version that should work with the new example given. Let us Know.

Regards,
John.

logic1

nice1 Clearz works a treat. Much appreciated

.logic.

clearz

No Problem

Silent Bob

Originally posted by logic1
Just a follow up to this. The following code was kindly donated to me but I need it formatted properly for perl as I'm a perl idiot.

for file in `ls *_asc.blob`
do
date=`echo $fle `awk -F '{ print $1 }'`
ver=`echo $fle `awk -F '{ print $2 }'`
for line in `cat $file`
do
echo "$date $ver $line"
done
done

That isn't PERL, it's a shell script

logic1

Originally posted by Silent Bob
That isn't PERL, it's a shell script

Well done.

.logic.

Silent Bob

Originally posted by logic1
Well done.

.logic.

I say that because it obviously needs translation before it is PERL, formatting won't cut it.

Also it won't even work as a shell script since it is missing pipes and parameters.