MS Access: 255 field limit in a table

Factual Questions
1

I need to import a data file from an OLD telco application. Unfortunately the file has about 350 fields. I attempted to split the file in Access by telling it to import the first 100 fields and then re-import the data into a new table skipping the first 100 fields. Unfortunately this didn’t work. Access just imported fields 101-255.

Is there a way to make this work?
Will it require code?

Thanks in advance!

2

Mods could you please change the title to:

"MS Access: 255 field limit in a table"

Thanks!

3

Well… This answer may be too simplistic… but if the data is not interlinked among the fields, couldn’t you COPY your existing database into a second database, DELETE the first 175 rows from the second database, then IMPORT the first 175 fields from Database1, and then IMPORT the remaining fields from the second database, then REJOIN both tables into one Access table?

Hope this helps.

4

Unfortunately I don’t have an existing database. All I have is a tab delimited flat file. :frowning:

5

Wellllllll…

I believe you can import such a file into Excel - although its been some time since I’ve done so, I don’t think its too tricky to import a tabbed file. I’m using Excel 2000, however.

Access can then import from Excel with no problem.

6

I’d write a script in either perl or tcl and split the file into 2 or 3 parts, and import them into separate tables.

7

It is not tricky to import a tabbed file, but it seems that excel has a similar limit to the columns. It looks like there are 256 columns and you can’t insert a column. A scritp is likely your best bet.

How many records are we talking about?

8

30,000 records.

I thought about perl because a friend used to rave about how great it was (but unfortunately I din’t know perl).

Is perl hard to pick up?

9

The following Perl script will process a file, sample.dat, and split out the tab-delimited fields into two files, 256 at a time. (So the second file will have the remainder after the first 256).



#!/usr/bin/perl

use strict;
use warnings;

open my $input, "sample.dat" or die "Could not open input file: $!";

my @ofiles;
for(0..1) {
        open $ofiles[$_], ">output$_.dat" or die "Could not open output file $_ for writing: $!";
}

while( <$input> ) {
        chomp;
        my @row = split /	/;

        for(0..1) {
                my @out = splice @row, 0, 255;
                print {$ofiles[$_]} join( "	", @out ), "
";
        }
}
10

Awww… Geeez…

I apologize for forgetting about the 256 column limit. :smack:

11

Wow friedo!

I’m gonna give that a shot, thanks!

12

You might also want to consider putting the primary key for the record in both tables, so that you can link the first half of the record with the second half.

13

Let us know how it turns out, zoid.

14

That’s a dang good point! How do I say I want the second file to be field 3 AND fields 255 through the end of the record?

15

friedo what would be the best way to figure out what’s going on in your code? I’ve looked for perl tutorials but I can find anything online I can easily follow. Would I be best buying some books? Any suggestions for reading material?

16

The best book for learning Perl is Learning Perl.

I’m happy to explain the code, but it’s past my bedtime. I’ll check the thread tomorrow morning.

17

Learning Perl is the best programming language tutorial I’ve ever read. It just edges out K&R*, even, because it doesn’t attempt to be a reference. (Perl is so big you could use its reference manual to stop small-caliber bullets.)

*(The C Programming Language by Brian Kernighan and Dennis Ritchie.)

And no, Perl isn’t hard to pick up, even if you’ve never programmed before. Some experience in any modern language will make it easier, however.

18

If you haven’t mastered the Perl yet you could just make a couple copies of your tab delimited file, open them in Word then record a keyboard macro to delete fields.