Skip to content
+1-888-319-3663

COMMUNITY FORUM

Grouping data with no key field

Adrian Cooke asked 6 years ago
Hi, I have a file to import to create an Inventory Transaction. There is no obvious key field. The file has a header line (H) followed by transaction lines (T). How can I group the data into one transaction ?
“H”,”ADJUSTEXP”,”POS”,”6469″,”24/06/2014″,”ORDER”,”STK”,”UK”,”POSADJ”
“T”,”M1013″,””,”WMS”,””,”19″,””,””
Answers
Patrick Roth Staff answered 6 years ago
Adrian,
You are going to need something so that SC knows what header/detail lines connect to each other and then of course what makes a document. 
But yeah, looking at your data you don’t have that at all.
Only solutions I can think of:
1. Add a Key field of some sort in the source data.  Ideally from wherever the file comes from.  In your case since it is one document, you’d just repeat that value “A”, “1”, etc.  whatever you want.
2. But assuming you can’t adjust the source – then you should just connect to the file using ODBC.
Now you write a query:
select * from Myfile.txt
But, you’d add the Key column
Select 1 as KeyField, * from myfile.txt
now you have created a new column for your key and it is in all of the records read that you can join on.
 
Adrian Cooke replied 6 years ago

Thanks Patrick, I will try that


If you would like to submit an answer or comment, please sign in to the eOne portal.