Hi everyone,
Got a bit of a tricky one here. Or at it's stumped me anyway!
I need to create some code to import a certain file type exported from another package into Excel.
The file is called a sequential file. It is basically a text file, and the data in it looks like this:
1/4
+1
Field1
Field2
Field3
Field4
+2
Field1
Field2
Field3
Field4
etc.
I need to import files of this type and put the record id number (the bit following each +) into column A and then in the columns that follow, the fields in order, so each record gets one row each. I can ignore the very first line as this just descibes the number of fields.
Of course, everything it variable. The first line will say how many fields are in the file, but instead of saying 1/4 (meaning fields are 1, 2, 3 and 4) it could say 5/8, or even 1,3,7,9 etc. Also, the files created could have anything up to 9999 fields (in which case you can't do it anyway coz Excel only has 256 columns but I'd like to be able to do it for up to this level)
The way I have been doing this in the past is to use another program (e.g. Word) and replacing a carriage return with a tab and then tab+ with carriage return+ which creates a tab delimited file which obviously Excel likes! I'd like a more refined way of doing things though.
I could of course just open the file in Excel and then run a macro to move the records into rows. However, the macros I've written in the past to do this take bloody ages to run if there are a lot of records.
Also, with the file format I'm dealing with, if I had a sequential file with 30,000 records with 100 fields in each, this is in theory something that would fit in Excel once the records are one per row, but it means my sequential file actually has 3 million lines in it which I can't open in Excel in the first place!
This is why I'd like some way of loading the file as it is opened.
Am I making sense here?
If I'm not being clear anywhere, please say so.
Many many thanks in advance.
Would be an enormously useful tool for me to have.
Rgds
AJ
Got a bit of a tricky one here. Or at it's stumped me anyway!
I need to create some code to import a certain file type exported from another package into Excel.
The file is called a sequential file. It is basically a text file, and the data in it looks like this:
1/4
+1
Field1
Field2
Field3
Field4
+2
Field1
Field2
Field3
Field4
etc.
I need to import files of this type and put the record id number (the bit following each +) into column A and then in the columns that follow, the fields in order, so each record gets one row each. I can ignore the very first line as this just descibes the number of fields.
Of course, everything it variable. The first line will say how many fields are in the file, but instead of saying 1/4 (meaning fields are 1, 2, 3 and 4) it could say 5/8, or even 1,3,7,9 etc. Also, the files created could have anything up to 9999 fields (in which case you can't do it anyway coz Excel only has 256 columns but I'd like to be able to do it for up to this level)
The way I have been doing this in the past is to use another program (e.g. Word) and replacing a carriage return with a tab and then tab+ with carriage return+ which creates a tab delimited file which obviously Excel likes! I'd like a more refined way of doing things though.
I could of course just open the file in Excel and then run a macro to move the records into rows. However, the macros I've written in the past to do this take bloody ages to run if there are a lot of records.
Also, with the file format I'm dealing with, if I had a sequential file with 30,000 records with 100 fields in each, this is in theory something that would fit in Excel once the records are one per row, but it means my sequential file actually has 3 million lines in it which I can't open in Excel in the first place!
This is why I'd like some way of loading the file as it is opened.
Am I making sense here?
If I'm not being clear anywhere, please say so.
Many many thanks in advance.
Would be an enormously useful tool for me to have.
Rgds
AJ
