Rather large project I'm confused on 1

[mainmast]

Hi all,

First off, I would like to thank anyone who helps in advance. I really do appreciate it!

I have a text file with some information I need to populate into a Microsoft Access Database. It's rather large, but with somewhat consistent data.

Here's the file:

http://www.wochelp.com/tech_info.txt

Besides the stuff at the very top, I need each of those little blocks of information put into a record in a database. Each line into a field of that record.

Can anyone help me out? It would be much appreciated!

 

[FesterSXS]

Open your copy of access - goto File >> Get External Data >> Import. Now select your text file and follow the instructions

Tony

Spirax-Sarco - steam traps control valves heat exchangers
Sun Villa - Luxury Florida accommodation

 

[damber]

Tony - I don't think MS Access text file import features will be able to manage this one. In case they don't I've put another suggestion below..

Mainmast,

If you work for a reasonably sized organisation then you might want to find out what data transformation tools are available, such as Integration/ETL type tools.. e.g. IBM/Ascential Datastage TX, Gentran, SeeBeyond, WebMethods etc etc. These will eat this file's transformation requirements in minutes and even adapt the output to your database.

If these tools aren't available, then you can code it in any language you like that supports databases. I'm assuming that you want to do it in ASP only, as you have posted in an ASP forum ;o) Though why not do it straight from the DB ? create a form with code behind it that reads the file and imports to the database - cuts out the connectivity stuff, and the IIS/ASP involvement, and is VB so isn't that different from using an ASP script in VBScript.

The most important part to this is understanding the file structure. This is what I see:

- "Record Groups" Separated by double line returns (Name:CR LF / Hex:0D 0A / ASCI:13 10 / VB: vbcrlf )
- "Records" separated by single line returns (as above coding)
- "Header1" RecordGroup (1 instance)
- "Header2" RecordGroup (1 instance)
- "Detail" RecordGroup (mulitple instances)
- "Footer" RecordGroup / Tag (1 instance)

Header1:

 num_categories=11
index=0  & name='Personal Combat'
index=1  & name='Guerilla Warfare'
index=2  & name='Persuasion'
index=3  & name='Biological Warfare'
index=4  & name='Air Combat'
index=5  & name='Innovation'
index=6  & name='Motivation'
index=7  & name='Economic Efficiency'
index=8  & name='Energy Production'
index=9  & name='Psionics'
index=10 & name='Agriculture'

Header2:

initial_technology='Culture'

Detail (example of 1):

  name='Carved Bone'
description='The technique of carving bone fragments into useful tools.'
story='We've learned how to shape bone fragments into useful tools.'
icon=''
type='weapon'
duration='permanent'
price='$0.00'
num_prerequisite_sets=1
  pre_req[]='Culture'
prerequisite_level='1'
$num_offenses_trumped=0;
$num_bonuses=3; $bonus[0] = Array("type"=>"base", "val"=>10); $bonus[1] = Array("type"=>"base_category", "ctg"=>"Personal Combat", "val"=>5); $bonus[2] = Array("type"=>"base_category", "ctg"=>"Economic Efficiency", "val"=>5);
weapon_type='normal'
weapon_name='Bone Spear'
weapon_description='Spear tipped with sharpened bone fragment.'
$num_defenses_trumped=0;
$num_bonuses=1; $bonus[0] = Array("type"=>"base_category", "ctg"=>"Personal Combat", "val"=>5);
$num_extras = 0;

Footer:

end

So, you will need to learn how the Split function works, and also how to use Arrays.

What you would do is split all the RecordGRoups up into an array:

 sTextFromFile = ... get the text from the FSO..
 aRecordGRoups = split(sTextFromFile, vbCRLF & vbCRLF)

Then iterating through the array twice to deal with the first two recordgroups (count the array size before doing anything, it should be > 3 to have split correctly)

From there take the same kind of approach as with splitting the record groups, but this time with only 1 <cr><lf> to split the records. Each of these records will become a field in a query, so for every record group not = "end" or the first two, will be a record in the database.

To get the field name split each 'item' in the 'record' array by the '=' char this will give you an array of 2 or more items. The first item can be used as the name of the field in the db, or translated / looked up in your code to reference a different field.. up to you. The remaining elements need joining together again with the initial element value removing from the start of the string, leaving you with the value to enter into the database.

OR.. if each line is an absolute content of the field (including the key value before the '=') , then simply add the line as the value..

Make sure you use the appropriate replace commands to remove/replace unwanted ' and " chars.

This can either generate SQL statements in another array, that are then processed at the end, or you can execute them on each iteration. Being a one off event, performance probably isnt too much of a concern.

Hope that gives you the general idea of how to approach it.

I'm not totally sure what the content is intended for, so cannot determine whether other parts will need to be split as well, though the principle should be the same. If you are intending to 'paste' this as client side code generated automatically by ASP from the DB, then you may just want to store the whole recordgroup as a field in a record - no point creating separate fields unless you need the granularity - and if you do, then you should use the values of the statements as the field content, not the whole string. That way, changes to the resulting code will be far easier. using split/join/arrays and a bit of imagination you can apply whichever strategy suits you.

Anyway, good luck...

A smile is worth a thousand kind words. So smile, it's easy!

 

[mainmast]

I played with MSACCESS Import feature, but I couldn't get it to do what I wanted.

Damber: Much thanks! I don't work for an organization at all - I'm 16 years old

Thanks for all the information, it really helps me with what I'm trying to do.

Thanks again!

 

[damber]

:-D Ha ha - no problem, I was probably a little presumptuous thinking that you were. Best time to learn is when you're young and not constrained by business risk management and profitibility concerns.. ;-)

Hope it works out for you.

A smile is worth a thousand kind words. So smile, it's easy!

 

[FesterSXS]

I've just realised there was a link to the text file. Having looked at it I laughed as there is no chance that you can import that using Access import feature. Oops

Tony

Spirax-Sarco - steam traps control valves heat exchangers
Sun Villa - Luxury Florida accommodation

 

[MichaelRed]

hmmmmmmmmmmmmmmmmmmmmmmmmmmm ... mmmmmmmmmmmmmmmmmmmmmmm ... mmmmmmmmmmmmmmmmmmmmmm ... mmmmmmmmmmmmm

Import, hmmm er, mmm probably not directly anyway.

You could search these (Tek-Tips) fora for a routine "basGrabFile". It would (with some of the companion procedures found in a few of the samples/threads which use it) at least get the entire text into a useable situation within VB (as an array of strings).

Using standard parsing techniques, (some examples above) it would not be difficult to then generate the db/table records.




MichaelRed