Hi All,
I am very new to Access and am looking to import a txt file into a table--
I have imported txt files into Access before, but I am having some problems:
1. Each row is a different type of record
2. Each record (row) needs to be spaced out differently (delimited fixed width)
3. Every 6 rows need to be pulled into one row when imported into the access table
I've attached an example that might help explain my problems with the file--the break between the 2 sets of records is only there is show how the data cycles every 6 lines as relates to the Sample named in the 2nd line. (The file itself it actually continuous)
I'd ultimtely like to go from this:
Line 1 (250 characters in length)
Line 2 (50-100 characters in length)
Line 3 (250 characters in length)
Line 4 (250 characters in length)
Line 5 (250 characters in length)
Line 6 (250 characters in length)
Line 7 (250 characters in length)
Line 8 (50-100 characters in length)
To this: (A tbl where each row at every 7th row in the txt file)
Line 1 | Line 2 | Line 3 | Line 4 | Line 5 | Line 6 |
Line 7 | Line 8 | Line 9 | Line 10 | Line 11| Line 12 |
Line 13|...etc.
But also be able to breakup the extremely long entries for each Line (so ultimately each row in the access table would have approximately 115 columns/fields having all 6 lines being split up).
Each row in the txt file has a number to determine the way the row itself should be split by width (it is easiest to look at the line that says SAMPLE and look at the number immediately preceeding the S--that character position denotes the type of format that the row should be split into, so every 6 records it repeats: 1,2,4,4,4,4,6 (So I have 1 record type 1, 1 record type 2, 4 record type 4s and 1 record type 6) I have the different character widths needed for each record type, but I haven't put it here--I'm hoping to be able to eventually put this in a VBA code of some sort--almost like an If Record Type A, Then delimit as such... If that's even possible...
I'm sorry this question is so long-winded--I hope it makes sense to some degree, at least. I have looked around on the other forums under Line Input and Importing Txt Files, but haven't found anything that deals with both importing different rows but also adjusting the width of the rows as well. (If this type of question has already been answered somewhere, please let me know!) Aside from this I must confess I know little to nothing about VBA and was told that the only way I might be able to do this would be to use it. Is there a way to do this? Even if it must be done in more than one step that would be great. Any help or suggestions would be greatly appreciated!
Thank you!
Vanessa
I am very new to Access and am looking to import a txt file into a table--
I have imported txt files into Access before, but I am having some problems:
1. Each row is a different type of record
2. Each record (row) needs to be spaced out differently (delimited fixed width)
3. Every 6 rows need to be pulled into one row when imported into the access table
I've attached an example that might help explain my problems with the file--the break between the 2 sets of records is only there is show how the data cycles every 6 lines as relates to the Sample named in the 2nd line. (The file itself it actually continuous)
I'd ultimtely like to go from this:
Line 1 (250 characters in length)
Line 2 (50-100 characters in length)
Line 3 (250 characters in length)
Line 4 (250 characters in length)
Line 5 (250 characters in length)
Line 6 (250 characters in length)
Line 7 (250 characters in length)
Line 8 (50-100 characters in length)
To this: (A tbl where each row at every 7th row in the txt file)
Line 1 | Line 2 | Line 3 | Line 4 | Line 5 | Line 6 |
Line 7 | Line 8 | Line 9 | Line 10 | Line 11| Line 12 |
Line 13|...etc.
But also be able to breakup the extremely long entries for each Line (so ultimately each row in the access table would have approximately 115 columns/fields having all 6 lines being split up).
Each row in the txt file has a number to determine the way the row itself should be split by width (it is easiest to look at the line that says SAMPLE and look at the number immediately preceeding the S--that character position denotes the type of format that the row should be split into, so every 6 records it repeats: 1,2,4,4,4,4,6 (So I have 1 record type 1, 1 record type 2, 4 record type 4s and 1 record type 6) I have the different character widths needed for each record type, but I haven't put it here--I'm hoping to be able to eventually put this in a VBA code of some sort--almost like an If Record Type A, Then delimit as such... If that's even possible...
I'm sorry this question is so long-winded--I hope it makes sense to some degree, at least. I have looked around on the other forums under Line Input and Importing Txt Files, but haven't found anything that deals with both importing different rows but also adjusting the width of the rows as well. (If this type of question has already been answered somewhere, please let me know!) Aside from this I must confess I know little to nothing about VBA and was told that the only way I might be able to do this would be to use it. Is there a way to do this? Even if it must be done in more than one step that would be great. Any help or suggestions would be greatly appreciated!
Thank you!
Vanessa
