VBS Script to Cross Reference Txt File and Output New Txt File

[jarett]

I have a txt file that is uploaded to a FTP site by one of our vendors with multiple columns, most are unusable, 2-4 will be exactly as the data is in our DB. One column in the txt file contains a Unique Key field, this is the field I need to find in our DB and return our Item Code value for each line. There is also another key field (PO_Line Key) that I need to pull over from the DB. Along with the Item Code and Line Key (from our DB), I need the Customer PO, QTY, Sub Total, Ship From, all from the original Txt file. I would like all these fields outputted into a new Txt file. So, I have an idea of how it should be done but not how to do it, the original txt file needs to query our DB to get the Item Code and Line Key, then get the other fields from the original txt file, the output file can be a txt or csv file, not sure if wither one is easier to output.

 

[rpaulson]

try this on a bank sheet.

Sub Import_Txt_File()
Dim fldarray() As String
Dim FileName As Variant
wr = 1
FileName = Application.GetOpenFilename(FileFilter:="Text File (*.csv),*.csv")
If FileName = False Then Exit Sub ' user cancelled, get out
Application.ScreenUpdating = False
Open FileName For Input Access Read As #1
While Not EOF(1)
    Line Input #1, wholeline
    fldarray() = Split(wholeline, ",") 'assign the delimiter
 For c = LBound(fldarray) To UBound(fldarray)
    Cells(wr, c + 1) = fldarray(c)
 Next c
 wr = wr + 1
Wend
Close #1
End Sub

the above will import a csv file.

then do your look ups, deletions and all your other stuff as needed.

below will export a sheet to a csv file.

Sub Create_Txt_File()
mypath = "C:\test\"
myfile = mypath & "my_file.csv"

Data = ""
lc = Cells(1, Columns.Count).End(xlToLeft).Column 'last column

For r = 1 To Range("A65536").End(xlUp).Row
    For c = 1 To lc
        Data = Data & "," & Cells(r, c)
    Next c
Next r
Open myfile For Append As #1
Print #1, Data
Close #1

MsgBox myfile & " Export Complete"

End Sub

 

[jarett]

try this on a bank sheet.

Sub Import_Txt_File()
Dim fldarray() As String
Dim FileName As Variant
wr = 1
FileName = Application.GetOpenFilename(FileFilter:="Text File (*.csv),*.csv")
If FileName = False Then Exit Sub ' user cancelled, get out
Application.ScreenUpdating = False
Open FileName For Input Access Read As #1
While Not EOF(1)
    Line Input #1, wholeline
    fldarray() = Split(wholeline, ",") 'assign the delimiter
 For c = LBound(fldarray) To UBound(fldarray)
    Cells(wr, c + 1) = fldarray(c)
 Next c
 wr = wr + 1
Wend
Close #1
End Sub

the above will import a csv file.

then do your look ups, deletions and all your other stuff as needed.

below will export a sheet to a csv file.

Sub Create_Txt_File()
mypath = "C:\test\"
myfile = mypath & "my_file.csv"

Data = ""
lc = Cells(1, Columns.Count).End(xlToLeft).Column 'last column

For r = 1 To Range("A65536").End(xlUp).Row
    For c = 1 To lc
        Data = Data & "," & Cells(r, c)
    Next c
Next r
Open myfile For Append As #1
Print #1, Data
Close #1

MsgBox myfile & " Export Complete"

End Sub

This might be a little naive, but what is the point of importing through VBS and just not doing a "Get Data". If using the VBS and the name of the txt file changes each day from the vendor, ex 08-22-23status.txt then 08-23-23status.txt, do I need to accommodate for this in the above script? For the export code you create another module ? I never had a sheet run 2 VB scripts before only experience is running a VB script when you open a file.

 

[rpaulson]

In my Opinion the VBA script will be much easier and faster. Run the import script that i created and see if you like it.

 

[jarett]

Ran the code and imported, looks like a mess, it put columns from the txt file into two columns in the excel file. I assume this is an issue with the original txt file? I went to confirm with the source txt file that it comes in a tab delimited data format.

ROG_Test.xlsm
	A	B	C
1	CUSTOMER PO SALESORDER# SHIP FROM SHIP DATE SHIP TO NAME SHIP TO ADDRESS1 SHIP TO ADDRESS2 SHIP TO CITY SHIP TO STATE SHIP TO ZIP SHIP TO COUNTRY SUB TOTAL FREIGHT HANDLING FEE INVOICE TOTAL TRACK NUM TOTAL CASES SHIP VIA BOX NUMBER STYLE DESCRIPTION COLOR SIZE QTY INVENTORY KEY SIZE ID INVOICE.ATTENTION LPN
2	0004708 0130226825 DALLAS	TX 08/21/23 ACTION SPECIALTIES LLC 7915 Highway 90 W New Iberia LA 70560-7651 US 20.99 0.00 599.72 1ZAF62500310787066 1 UPS-GND 1 NKDC1963 Navy XS 1 169274 1 lexi LP0093839405
3	0004708 0130226825 DALLAS	TX 08/21/23 ACTION SPECIALTIES LLC 7915 Highway 90 W New Iberia LA 70560-7651 US 20.99 0.00 599.72 1ZAF62500310787066 1 UPS-GND 1 NKDC1963 Navy S 1 169292 1 lexi LP0093839405
Sheet1

 

[rpaulson]

if its Tab then Change
fldarray() = Split(wholeline, ",") 'assign the delimiter
to
fldarray() = Split(wholeline, vbtab) 'assign the delimiter

 

[jarett]

if its Tab then Change
fldarray() = Split(wholeline, ",") 'assign the delimiter
to
fldarray() = Split(wholeline, vbtab) 'assign the delimiter

Import looks good now, everything in it's own column. So, my next question is in your original reply you state "do stuff" in between the import and export, I have a contractor stating he does his lookups through the VBS with ODBC, we are trying to stay away from ODBC and use straight SQL connections. I might be able to figure something out by cimporting the table I need into a new sheet within the workbook and then adding a column (to the source data) to do the lookup on. Is this the correct way of thinking and doing it in power query to record? I'm just trying to stay were I'm more comfortable in my abilities.

 

[rpaulson]

There could be many ways do achieving, what you want. the problem is you are "t trying to stay were I'm more comfortable in my abilities". I have no clue what your abilities are. I would not be importing a table or even a text file. With some VBA code you could send a query string right to the SQL data base and return just the records you want, directly to your excel workbook. In about an hour I learned how to get data from our SQL server by watching these videos (VBA - working with data videos | Excel VBA Part 56.1 - Connect to SQL Server using ADO).