How would I import something like this?

[Insyderznf]

I'm having issues with the names as highlighted below. Since some are single named and some are multiple named they all need to be in the same column.

01 000 01 000 001 5DN C Abuna's Chant# 0.019 0.038 0.008 0.019

I assume thats why the # sign is there but i can't figure out how to tell access to ignore the space from the single letter and start over at the #. Also that # needs to be stripped out eventually.

Any ideas? Below is some more lines to work with. It is in a standard .txt file.

01 000 01 000 001 5DN C Abuna's Chant# 0.019 0.038 0.008 0.019
01 000 01 000 002 5DN C Armed Response# 0.019 0.038 0.008 0.019
01 000 01 000 003 5DN R Auriok Champion# 2.640 4.656 1.980 3.492
01 000 01 000 004 5DN U Auriok Salvagers# 0.038 0.067 0.019 0.034
01 000 01 000 005 5DN R Auriok Windwalker# 0.115 0.672 0.066 0.437
01 000 01 000 006 5DN R Beacon of Immortality# 1.920 3.360 1.344 2.520
01 000 01 000 007 5DN R Bringer of the White Dawn# 0.864 1.536 0.562 1.075
01 000 01 000 008 5DN U Circle of Protection: Artifacts# 0.038 0.067 0.019 0.034
01 000 01 000 009 5DN C Leonin Squire# 0.115 0.480 0.066 0.274
01 000 01 000 010 5DN C Loxodon Anchorite# 0.019 0.038 0.008 0.019
01 000 01 000 011 5DN U Loxodon Stalwart# 0.038 0.067 0.019 0.034
01 000 01 000 012 5DN R Raksha Golden Cub# 0.768 1.440 0.499 1.008
01 000 01 000 013 5DN R Retaliate# 0.384 0.960 0.219 0.624
01 000 01 000 014 5DN R Roar of Reclamation# 0.115 0.672 0.066 0.437
01 000 01 000 015 5DN C Skyhunter Prowler# 0.019 0.038 0.008 0.019
01 000 01 000 016 5DN U Skyhunter Skirmisher# 0.048 0.067 0.024 0.034
01 000 01 000 017 5DN C Stand Firm# 0.019 0.038 0.008 0.019
01 000 01 000 018 5DN C Stasis Cocoon# 0.019 0.038 0.008 0.019
01 000 01 000 019 5DN U Steelshaper's Gift# 0.384 1.152 0.219 0.806
01 000 01 000 020 5DN U Vanquish# 0.038 0.067 0.019 0.034
01 000 01 000 021 5DN R Acquire# 0.192 0.672 0.109 0.437
01 000 01 000 022 5DN C Advanced Hoverguard# 0.019 0.038 0.008 0.019
01 000 01 000 023 5DN R Artificer's Intuition# 0.288 0.672 0.164 0.437
01 000 01 000 024 5DN R Beacon of Tomorrows# 0.960 1.680 0.624 1.176
01 000 01 000 025 5DN R Blinkmoth Infusion# 0.096 0.672 0.048 0.437
01 000 01 000 026 5DN R Bringer of the Blue Dawn# 2.640 13.440 1.980 11.424
01 000 01 000 027 5DN C Condescend# 0.240 0.960 0.137 0.624
01 000 01 000 028 5DN U Disruption Aura# 0.038 0.067 0.019 0.034
01 000 01 000 029 5DN C Early Frost# 0.077 0.336 0.039 0.192
01 000 01 000 030 5DN U Eyes of the Watcher# 0.038 0.067 0.019 0.034
01 000 01 000 031 5DN U Fold into Æther# 0.038 0.067 0.019 0.034
01 000 01 000 032 5DN R Hoverguard Sweepers# 0.192 0.672 0.109 0.437
01 000 01 000 033 5DN C Into Thin Air# 0.019 0.038 0.008 0.019
01 000 01 000 034 5DN U Plasma Elemental# 0.038 0.067 0.019 0.034
01 000 01 000 035 5DN U Qumulox# 0.038 0.067 0.019 0.034
01 000 01 000 036 5DN C Serum Visions# 0.115 1.152 0.066 0.806
01 000 01 000 037 5DN R Spectral Shift# 0.192 0.672 0.109 0.437
01 000 01 000 038 5DN C Thought Courier# 0.019 0.038 0.008 0.019
01 000 01 000 039 5DN C Trinket Mage# 0.480 4.800 0.274 3.600
01 000 01 000 040 5DN U Vedalken Mastermind# 0.038 0.067 0.019 0.034
01 000 01 000 041 5DN R Beacon of Unrest# 2.160 3.792 1.620 2.844
01 000 01 000 042 5DN C Blind Creeper# 0.019 0.038 0.008 0.019
01 000 01 000 043 5DN R Bringer of the Black Dawn# 1.440 2.544 1.008 1.908
01 000 01 000 044 5DN C Cackling Imp# 0.019 0.038 0.008 0.019
01 000 01 000 045 5DN R Desecration Elemental# 0.115 0.672 0.066 0.437
01 000 01 000 046 5DN U Devour in Shadow# 0.038 0.067 0.019 0.034
01 000 01 000 047 5DN C Dross Crocodile# 0.019 0.038 0.008 0.019
01 000 01 000 048 5DN U Ebon Drake# 0.038 0.067 0.019 0.034

[Insyderznf]

Humm... No responses, is this not possible or is it so simple that people just ignore it?

[RuralGuy]

You need to allow at least 24 hours for us volunteers to respond before getting impatient. Is this basically a text file where your field starts in the same column and ends when you get to the #?

[Insyderznf]

I'm sorry that I became impatient. Thank you for your help.

I think you have the gist of it. Each item is a space deliminated except for the name field. The name field needs to be one column but can have anywhere from one to six names. The # marks the end of the name field but there is no marker for the start of that field. After the name field comes the costs field.

If I try and importat it as is i get a very goofed up table with multiple columns for each name in the name field.

The field headings should like like this when finished.
(Skip) (Skip) (Skip) (Skip) (Skip) Version Rarity Name Cost1 Cost2 Cost3 Cost4

I'll try and create a better file later, the forum isn't going to let me add enough spaces to line it up

[RuralGuy]

Because of the mixture of fixed length and variable length fields, asking the import wizard for help is probably useless. It is not too difficult to import the file using the FileSystemObject though. Do you want to do it? I'll play with what you have and post back when I have some code for you.

[RuralGuy]

This code will probably do it for you. You should be able to import it into ac2007 without a problem or run it as ac2000.

[Insyderznf]

Thank you for your hard work! I will put it to good use tomorrow! I'm excited and it will make it much easier using the filesystemobject as I wanted to create a form to do it anyways

[RuralGuy]

Glad I could help. Let me know how it works on your data.

[Insyderznf]

It works ok, however the version can be either 2-3 characters long. I'm sorry I forgot to mention that. It looks like if I can figure out what you did for the cost portion I can use a similar function to return the amount of characters for the version? Thank you again!

Here is the actual file

http://www.mediafire.com/?sharekey=7...5965eaa7bc68bc

Also the one line that is two long in the .txt file and causing it to error, i dont really need that so I deleted it. If its possible to import that line:
Champions of Kamigawa Theme Deck: Way of the Warrio great. If not, no big deal.

[Insyderznf]

My access skills were a little rusty so the code might be a little ugly but it works. I just used InStr to get the string length, pass the paramenters to the function and it works great! now I just need to write something that if the file is to long for the field name it truncates it.

[RuralGuy]

I left the field size at the default of 50 characters. Just open the table in design mode and change the field size of the OurName field to 255. That should fix that problem.

[Insyderznf]

That was silly of me, i was overthinking it.

[RuralGuy]

The important thing is you now have it working. Great!

[RuralGuy]

Are you ready to apply the Solved thread tool yet?