Adding in extra rows to an exported text file (1 Viewer)

peskywinnets

Registered User.
Local time
Today, 21:22
Joined
Feb 4, 2014
Messages
576
So I have a problem that I can't really figure out the best way to approach.

Background: Amazon require sellers to upload product flatfile files to modify listed products in their catalogue ...these flatfiles are quite unwieldly to use, so I want to basically I want to replicate their resulting tab delimited file with an automated export from Acceess.

This would have been easy, except Amazon have three rows at the top of their text output file, here they are in pale orange...



the top row (version), is going to be static & I can simply insert that row after exporting a text file from Accees, the bottom row will be the names of my query fields....but it's how to 'lookup' the middle row text...basically I don't want to have to export all data everytime so sometimes there'll only be a few columns & at other times more columns...so I need a way of inserting that middle row, but with some form of dynamic lookup.

So after Ive exported the file from access I need Access to open the top file, insert the row with the version number, but then insert tab delimted text that is directly related to (but not the same as), my query field names.

Any ideas?
 

jdraw

Super Moderator
Staff member
Local time
Today, 17:22
Joined
Jan 23, 2006
Messages
15,364
Pesky,
Perhaps an example with one of your products would help.

As I read your post, my first reaction would be to create a text file with the Amazon header.
Then create my product data and export to text.

Then, final text file would be composed of Amazon header text + My Data text.

But I'm not familiar with Amazon details, your product details.......

Please tell us more.
 

peskywinnets

Registered User.
Local time
Today, 21:22
Joined
Feb 4, 2014
Messages
576
I managed to crack it (quite convoluted ...but I learnt some new stuff along the way)

Basically, open the text file, read in the first row, use Split() To parse the header row text into an array.

Once the text is in an array, read it out again & use Dlookup to get the lookup value from a pre-populated linked table (creating a new row as we go). Write the extra row at the top of the text file ...job done.

At least going this way, it's dynamic & it doesn't matter whether I have a few columns in the text file or hundreds, it'll all work & marry up.

Thanks for you being willing to help (as ever) jdraw.
 

isladogs

MVP / VIP
Local time
Today, 21:22
Joined
Jan 14, 2017
Messages
18,186
I managed to crack it (quite convoluted ...but I learnt some new stuff along the way)

That's great
Suggest you post your code as others may also learn from it ...
 

peskywinnets

Registered User.
Local time
Today, 21:22
Joined
Feb 4, 2014
Messages
576
That's great
Suggest you post your code as others may also learn from it ...

I'm always reticent (embarrassed!) to do so becuase I'm not a programmer - I'm a kludger & otehr will likely wince!), but since you ask, here's what I did...

Code:
Dim objFSO As Scripting.FileSystemObject
Dim objText As Scripting.TextStream
Dim strFullPathNameAndExtension As String
Dim OriginalTopRow As String
Dim strNewText As String
Dim header As Variant
Dim LookupValue As String
Dim NewTopRow As String
Dim SecondRow As String
Dim I As Long

Set objFSO = CreateObject("Scripting.FileSystemObject")
strFullPathNameAndExtension = "C:\AccessData\Amazon\AmazonInventoryLoaderFile_full.txt"

'open and read the entire text file (we will need this later)...
Set objText = objFSO.OpenTextFile(strFullPathNameAndExtension, ForReading)
OriginalstrContents = objText.ReadAll
objText.Close

'open up the file again, but this time, only read in read in the top row...
    Set objText = objFSO.OpenTextFile(strFullPathNameAndExtension, ForReading)
    OriginalTopRow = objText.ReadLine
    objText.Close

' now split out the top row text into an array (the original row text is tab delimited)...
    header = Split(OriginalTopRow, vbTab) 

'now carry up a Dlookup on each element in the array text...
    For I = 0 To UBound(header)
    LookupValue = DLookup("[OUT]", "AmazonInventoryLoaderHeaderLookup", "[IN]= '" & header(I) & "'")

' this next bit commences recreating the the new row needed...
    If I = 0 Then
    SecondRow = LookupValue
    Else
    SecondRow = SecondRow & vbTab & LookupValue
    End If
    Next I
' This bit is static (it remains the same & will be the new top row)...
    NewTopRow = "TemplateType=homeimprovement" & vbTab & "Version=2017.0217" & vbTab & "The top 3 rows are for Amazon.com use only. Do not modify or delete the top 3 rows.

'now add in all the newly created rows to the very top of the original text file...
"
    strNewContents = NewTopRow & vbCrLf & SecondRow & vbCrLf & OriginalstrContents

Set objText = objFSO.OpenTextFile(strFullPathNameAndExtension, ForWriting)
objText.WriteLine strNewContents
objText.Close
Set objFSO = Nothing
    
    End Function

It's probably not that pretty but does exactly what I need it to do :)
 
Last edited:

Users who are viewing this thread

Top Bottom