1500 Columns in a text file, how to split?

[Keesinho]

Hello,

my problem is the following. I have an ascii file containing 1000 records, all have 1500 (!) columns. Access can't handle this, so I'm looking for some way to split my file into 3 files containing 500 columns.

Is there anyone out there who can provide me with a tool to do just this?

Help is very much appreciated.

Keesinho

 

[jimmythegeek]

That is a good one. In fact, I will throw another wrinkle into it. The maximum number of fields (columns) Access will allow in a table is 255. So even if you get them down to 500 columns, they still won't import into a table. You will have to get them down to like 6 columns of 250 to be able to get them into Access tables.

What kind of file would have 1500 columns? I've never seen anything even remotely close to that, ever! Is there any way that the ascii file can be exported differently? Do you control that, or is it completely out of the question?

I suppose you could code a ton of arrays, and fill them up based on walking through the text file in code, but that would be EXTREMELY tedious, as well as JUST FLAT UGLY.

Sorry I don't have a good answer.

 

[MichaelRed]

More questions than answers ...

Is the data 'well organized' (space delimited, comma delinited ...)? If so, a parsing routine wiouldn't be soooooo terrible.

Do you have a 'plan' for which fields are really related - to fit into a table structure? If not - STOP HERE!!!!!!

Is a lot of the 'information' redundant, so that it lends itself to reducing the volume of data you need to collect in the various tables/fields?

What is the MAXIMUM length of the 'Record'? If it is to long, there will be some additional processing issues (e.g. keeping MS Access from truncating the line on input &/or assignment.

What have you tried so far? What were the problems encountered in that approach?

Can you provide at least a sample of the information (e.g. the text record(s).

Can you provide the desired schema (table structure) for the information, along with which text/field goes into which table/field?

The above would (probably) get much more positive response than this brief post.

 

[rafe]

Just a suggestion

By 1500 columns do you mean 1500 fields or 1500 chars per line? Good news is that it's ASCII you're importing. So use that to your advantage. Are the fields delimited (by commas or some thing) or are they always in a fixed position (field1 is always in columns 1 to 7, field 2 is always in column 8-10, etc.).

No way around this, you're going to have to massage your data & it's going to be a royal pain but more tedium than difficulty.

1) For fixed position fields you could:

You can import your data in one table as text fields of about 250 chars long so that's 6 (or so) text fields of about 250 chars each. I say about because you don't want to break your text input data in mid-field. Use the import wizard to group the data.

Now you've got a text mess you're going to have to strip off data with Mid() functions and conversion functions in a series of append or create table queries. Something like:

NewField1: Cdbl(Mid(

![Field1],1,5))
NewField2: CLng(Mid(

![Field1]1,120,7))
NewField3: CBool(Mid(

![Field2],200,1))
One query for each new table you’re going to create, up to 255 new fields per new table.

Depending on the ASCII input data. Each of these statements goes into the “Field:” line (1st line) when the query is in design view.

2) For delimited fields it’s going to take a bit more work because you’re going to have to format the data before it’s imported with some fancy search replace functions or search for the delimiters after the data’s imported.

Hope this helps, but if you need further help I’ll try if you can give me more info.

rafe