Records split into two rows

  • Thread starter Thread starter Shairal
  • Start date Start date
S

Shairal

I receive a text file from a 3rd party vendor. The file splits each
customer's information across two rows. For example, my first row of data is
just header info, starting with the second row is info on customer ABC, the
third row contains the rest of the info for customer ABC. Then starting with
row 4 is customer DEF and the remaining information for that customer is on
row 5 ... etc.

Any advice on how can I put all of one customer's information into a single
record? I've been using Excel to create a key for the two rows that need to
matched up and then I link those two records in Access - giving me one
continuous record. It seems like Access should be able to do this without
all the extra steps.

Thanks in advance for the help!!
 
Below is a sample of the data - "H" is the header row, "L" is the start of a
new record, and "O" is the continuation of the row above it. There is
nothing in "L" or "O" rows, like a customer ID or a unique key, that would
allow me to link the two - that's why I dump it in Excel first. I recieve
about 5,000 records per text file. Thanks for your time!!

"H","BUSINESS","MyCOMPANY ","20090101",
"L","123456","RJK902227DRD","10000","20091222," 00140"," 14.00000","ABC
ELECT ","","US","",
"O","EC","BOB","NY","145130000","US","77849UU-J5-3302 ",
"L","123456 ","KMLOOO0S ","35000â€,"20091214", ,".00140"," 49.00000","ABC
ELECT ","","US","",
"O","EC","JONES","NY","145130000","US","J-726-aBCr-4703",
 
Without a customer ID or a unique key you are subject to errors but this will
get you there I believe.
Add an Autonumber field named MyAuto and other fields like this --
MyAuto, [Field1], [Field2], [Field3], [Field4], [Field5], [Field6], ...

I think this query will work for you --
SELECT [XX].[Field2], [XX].[Field3], [XX].[Field4], [XX].[Field5],
[XX].[Field6], [XX].[Field7], [XX].[Field8], [XX].[Field9], [XX].[Field10],
[XX].[Field11], [YY].[Field2] AS [Field12], [YY].[Field3] AS [Field13],
[YY].[Field4] AS [Field14], [YY].[Field5] AS [Field15], [YY].[Field6] AS
[Field16], [YY].[Field7] AS [Field17]
FROM YourImport AS [XX], YourImport AS [YY]
WHERE [XX].[Field1] = "L" AND [YY].[Field1] = "O" AND [YY].[MyAuto] =
[YY].[MyAuto]+1
ORDER BY [XX].[MyAuto], [YY].[MyAuto];
 
EXCELLENT!!! Worked like a charm! Thanks so much for your time and effort!

KARL DEWEY said:
Without a customer ID or a unique key you are subject to errors but this will
get you there I believe.
Add an Autonumber field named MyAuto and other fields like this --
MyAuto, [Field1], [Field2], [Field3], [Field4], [Field5], [Field6], ...

I think this query will work for you --
SELECT [XX].[Field2], [XX].[Field3], [XX].[Field4], [XX].[Field5],
[XX].[Field6], [XX].[Field7], [XX].[Field8], [XX].[Field9], [XX].[Field10],
[XX].[Field11], [YY].[Field2] AS [Field12], [YY].[Field3] AS [Field13],
[YY].[Field4] AS [Field14], [YY].[Field5] AS [Field15], [YY].[Field6] AS
[Field16], [YY].[Field7] AS [Field17]
FROM YourImport AS [XX], YourImport AS [YY]
WHERE [XX].[Field1] = "L" AND [YY].[Field1] = "O" AND [YY].[MyAuto] =
[YY].[MyAuto]+1
ORDER BY [XX].[MyAuto], [YY].[MyAuto];


--
Build a little, test a little.


Shairal said:
Below is a sample of the data - "H" is the header row, "L" is the start of a
new record, and "O" is the continuation of the row above it. There is
nothing in "L" or "O" rows, like a customer ID or a unique key, that would
allow me to link the two - that's why I dump it in Excel first. I recieve
about 5,000 records per text file. Thanks for your time!!

"H","BUSINESS","MyCOMPANY ","20090101",
"L","123456","RJK902227DRD","10000","20091222," 00140"," 14.00000","ABC
ELECT ","","US","",
"O","EC","BOB","NY","145130000","US","77849UU-J5-3302 ",
"L","123456 ","KMLOOO0S ","35000â€,"20091214", ,".00140"," 49.00000","ABC
ELECT ","","US","",
"O","EC","JONES","NY","145130000","US","J-726-aBCr-4703",
Click to expand...
Click to expand...
 
Shairal said:
Below is a sample of the data - "H" is the header row, "L" is the start
of a
new record, and "O" is the continuation of the row above it. There is
nothing in "L" or "O" rows, like a customer ID or a unique key, that would
allow me to link the two - that's why I dump it in Excel first. I recieve
about 5,000 records per text file. Thanks for your time!!

"H","BUSINESS","MyCOMPANY ","20090101",
"L","123456","RJK902227DRD","10000","20091222," 00140"," 14.00000","ABC
ELECT ","","US","",
"O","EC","BOB","NY","145130000","US","77849UU-J5-3302 ",
"L","123456 ","KMLOOO0S ","35000â€,"20091214", ,".00140"," 49.00000","ABC
ELECT ","","US","",
"O","EC","JONES","NY","145130000","US","J-726-aBCr-4703",
Click to expand...
 
Back
Top